This post is to share a script I’ve been working on periodically over the last couple of months to monitor and report on replication latency at the distributor.
I use this in monitoring transactional replication with a stand-alone distributor.
My life is a bit easier since I learned how to use SQL agent tokens. They are particularly nice for setting date and timestamps on backup files. Unfortunately, they only work in the context of executing agent jobs.
There’s some pretty cool things about SSMS 2008– I am really pleased that execution plans open graphically by default now, and it’s easy to view the XML on demand. I had gotten pretty fast at saving things as .sqlplan, but it was a bit of a drag.
Until recently I’d never used the deadlock graph event in Profiler. I’ve been pretty lucky and haven’t had to troubleshoot deadlocks much until now. The deadlock graph turns out to be quite nice!
This past week at work we found an instance where a replicated table (transactional push) was out of sync on the subscriber. In evaluating how to address the situation, we did some testing in pre-production and discovered the following (using profiler).
I often need to find a query plan in the cache for a process that has run long overnight. Typically I’ll be able to figure out from our logging some of the tables involved in the query. Sometimes I will have most of the executing text but won’t know exactly what dates or reference points were included.
Update from Kendra (Nov 2018): I’m keeping this post for posterity, but I REALLY don’t recommend the script. You’d be much better off using a production monitoring tool that did this job, or @AdamMachanic ‘s sp_WhoIsActive.
Maybe you’re a user in a reporting database running a long query in the read committed isolation level, merrily blocking a process which needs to load data.
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.