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

16/11/2014

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

09/11/2014

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

04/11/2014

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

03/11/2014

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

01/11/2014

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

30/10/2014

  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

13/07/2013

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

07/11/2012

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

Kill all open transactions using the DBCC OPENTRAN command

14/07/2012

  Recently (last weekend ) I run into a situation where the transaction log of database ReportServerTempDB grew far beyond it’s regular size. When looking at the log_reuse_wait_desc column of sys.databases it indicated ACTIVE_TRANSACTION so I run a DBCC OPENTRAN command just to find out that the oldest open transaction dates 2 days back. I killed the spid returned from DBCC OPENTRAN and still there was an active transaction from the same time period. So after having to issue the DBCC OPENTRAN command several times followed by a kill command I took a few moments to...
tags: , ,
no comments