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...

A few ways to Monitor Transactional Replication queue

Monitoring the distribution agent queue is essential and this applies to any other step and component in the replication infrastructure.  There are a few ways we can monitor the distribution queue: 1. Monitor the time of the oldest pending command not yet delivered 2. Monitor the number of pending commands not yet delivered 3. Monitor the delivery latency   Monitoring the log reader agent queue is essential just as much as monitoring the distribution queue  even though I know this part of the replication infrastructure is hardly being monitored since people tend to think...

Multi Threading FTP client capable of resuming an interrupted download


I recently coded a .net console application that is aimed to automatically download backup files from one data center to another. I enjoyed this small project and since I did experience a few difficulties I thought pointing out some points would help others. I faced 2 major issues: 1. Even though my program was opening several threads I could only see 2 files being downloaded at a time It was important for me to have several files being simultaneous downloaded since we have a download limitation at the session level so in order...
tags: , ,
no comments

FOREIGN KEY CONSTRAINT defined with ON DELETE CASCADE introduce Key Range locks leading to deadlocks


I was troubleshooting a severe deadlock(s) case at the lab of a customer involving quite a few processes with lots of code… not one of the simple cases, and what caught my eye was the fact that the deadlock details printed at the ERRORLOG showed Range Key locks such as RangeS-U and RangeI-N while the isolation level in effect was read committed for all processes involved. Since the isolation level in effect was not Serializable (where you typically expect to see range locks) I immediately looked for triggers and indexed views that introduce range locks but none existed....