Copy large files over WAN using aria2


As we move forward in time more data is being saved resulting in big size databases (VLDBs) which bring out new challenges to the DBA. In my personal experience in recent years I had come across several cases where I had to copy large files between 2 data centers located at the US and Europe. This led me to coding my own ftp client utility that met my specific requirements. I had plans to make it public available but that plan remained in the plans task list so far and in the mean time I had come across a...
no comments

SQL Server 2014 fails on INSERT using OUTPUT clause

In the process of upgrading a customer’s SQL Server 2012 to 2014 we have come across the following case which was not detected by the SQL Server 2014 Upgrade Advisor (UA) tool. When inserting values to an identity column you have to use the SET option SET IDENTITY_INSERT ON; or else face the bellow error: Msg 544, Level 16, State 1, Line 22Cannot insert explicit value for identity column in table '#t2' when IDENTITY_INSERT is set to OFF. While this is something we all know since the early days we started working with the product none of us noticed...
no comments

SQL Server Upgrade Advisor (UA) tool trace events


The SQL Server Upgrade Advisor tool (UA) is an essential step in preparing for your SQL Server upgrade. The tool can perform it’s analysis in 3 different ways: 1. Database – connect to a live database and analyze it’s code 2. Trace files – Analyze trace files that were captured a head 3. Batch files – Analyze sql scripts   I have come across many occasions where DBAs tend to use the first option only since it is the most simple and requires no preparation a head. However, I would like to point out that in...
no comments

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.


The exception: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. I have come across this exception more than once in the past and I am pretty much familiar with it but there is always room for learning and this time the circumstances has led me to take some time and deep dive into this issue inside out. I have created a simple .NET console application (download link at the very end of post) which I named...
no comments

Monitor Transactional Replication Distribution Agent queue – By observing the time of the oldest pending command


Monitoring the distribution queue in Transactional Replication is essential in systems where the data being replicated is important for various business processes relaying on that data. In this post I share a very simple way that I have used to monitor the oldest pending command. I have covered another way to monitor the distribution queue in this post. One way to monitor the distribution queue in Transactional Replication is to observe the time of the oldest undelivered command. For example, knowing that on a regular basis your oldest pending command is around 40 minutes gives you a base line...

Monitor Transactional Replication Log Reader latency


One way to know how is your log reader doing is simply to keep an eye on it’s latency and when crossing a predefined threshold get notified.I have used the Microsoft stored procedures sp_posttracertoken and sp_helptracertokenhistory which are designed to allow us to programmatically monitor the replication latency at 2 points along the delivery chain. From the published database’s transaction log to the distribution database which serves as the replication queue (MSrepl_transactions and MSrepl_commands tables), a task done by the log reader agent and from the distribution database to the subscription database at the subscriber, a task done by...

Configure and Monitor Replication agents schedule


By default when replication is installed (Merge and Transactional) the replication jobs get created with a schedule that starts the jobs one time only when SQL Server Agent service starts. I would like point out 2 important points regarding this schedule: 1. In case you stick with this default schedule then it is important to add an additional schedule that runs every 1 minute so that if for any reason the job fails it will get started by the 1 Minute schedule and not wait there for you to do so. 2. I find it better to use a 1 Minute...

Monitor Transactional Replication Distribution Agent queue – By observing the amount of pending commands


In this post I will be showing how I monitor the Distribution Agent queue by monitoring the number of pending commands not yet delivered. All the code shown in the snippets is available for download at the bottom of this post. There are 2 ways that I have used over time: 1. Using a query, call it the “distribution query”, that accesses the relevant tables at the distribution database and returns a result set with data at the article level showing the amount (count) of commands not yet delivered. 2. Using the Microsoft stored procedure that is in use...

A walk around to the limitation of INSERT..EXEC of a nested stored procedure


Recently, while coding a CLR stored procedure to overcome the known limitation that a stored procedure that in it’s body definition already uses an execution of a stored procedure in the form of INSER..EXEC cannot be executed in this way it self when called, I found out an interesting way to overcome this limitation. The problem: For example: if proc my_proc has in it’s code something like INSERT t1 EXEC p1; then my_proc cannot be called in the form of :INSERT my_table EXEC my_proc; Here is the error received when calling a nested stored procedure in the for of...
no comments

Monitor Transactional Replication Log Reader queue


  Here is a very simple way to monitor the number of commands not yet being read by the log reader and that are still awaiting to be delivered to the distribution database by using the Microsoft procedure sp_replcounters. I started monitoring this part of the Transactional Replication infrastructure after experiencing a case where the log reader was running (job was up in a running state and never failed, the logreader.exe process was up) and no error was logged at MSrepl_errors table but in reality no commands were being delivered (which of course on a busy server can lead...