Today I was glancing at once of my servers and noticed the backup job was running later than normal. I haven’t been working with this server for long, so I glanced to check where the backup was writing to and checked the output directory. I found that a differential backup was being written, and that the differential backup from the day before was much larger than normal.
Update from Kendra (Nov 2018): I’m keeping this post for posterity, but instead of sp_who2, check out @AdamMachanic ’s sp_WhoIsActive, if you need a free tool to see what’s running in SQL Server.
I thought I would do a series of posts thinking through general troubleshooting steps I use for a wide variety of issues– everything from slow running performance, system crashes, or application failures where the source hasn’t been identified.
I needed to quickly report on what articles are being replicated from a database yesterday. It was helpful to include the size of the articles, number of rows, and the subscriber information.
The system tables make this pretty quick work.
Here’s a query I found useful today– this week we moved many of our production datamart servers to SQL 2K5 SP3 CU4, and today among the course of other issues I wanted to take a look at my job runtimes to see if they might be noticeably slower or faster than prior runs. I often am in a similar situation after deploying significant changes to our codebase.
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.
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.