I’ve submitted to GroupBy, free online training for Data Professionals. Sessions will be held in European hours on May 12 and for the Americas on May 13. Vote for sessions between April 13-17.

What Articles are being Replicated, and How Big are They?

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. Run the following against your publisher and you’ll be able to document this easily. select publicationName= sp.name , tableType=case p.index_id when 1 then 'Clustered Index' else 'Heap' end , p.index_id , articleName=ar.name , sub.srvname , ar.dest_table , totalSizeGB = cast(sum(au.total_pages)*8./1024./1024. as numeric(10,1)) , numRows = (select top 1 [rows] from sys.partitions p2 where p2.object_id=o.object_id and p2.index_id in (0,1)) , createDate= o.create_date , modifyDate= o.modify_date , droppedGB = cast(sum(case au.[type] when 0 then au.total_pages else 0 end)*8./1024./1024. as numeric(10,1)) , inRowGB = cast(sum(case au.[type] when 1 then au.total_pages else 0 end)*8./1024./1024. as numeric(10,1)) , LOBGB = cast(sum(case au.[type] when 2 then au.total_pages else 0 end)*8./1024./1024. as numeric (10,1)) , rowOverflowGB =…
Read More

Troubleshooting 1.00.001: sp_who2 and the SQL Server Log

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. Typically, the first thing I do in almost any situation I can think of is verify if I can connect to the SQL Server instance and verify if I can run sp_who2 and view the contents of the SQL Server Log. This is actually three separate tests, but you get quite a lot of information about the situation very quickly: Can I connect to the instance over TCP/IP?  And does it work with the default timeout, or do I have…
Read More

Average Daily Job Runtime

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. Why average runtime? Since most of my processing runs in SQL Server Agent jobs, looking at average runtime per day is a pretty convenient index of performance. However, the load in processing varies by day of week, so it’s frequently useful to check activity for only a certain day of the week. This script allows for both. I usually want to tweak the conditions, so I don’t set them in variables at the top, I edit them within the query itself each time:…
Read More

Replication Undelivered Commands: Monitoring and Reporting

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. The basic purpose for the script is: Check the number of undelivered commands for all active publications Log the data found to a table (which is collected on a centralized server so the history can be displayed graphically in Reporting Services 2008– that code not in this post) When the number of undelivered commands is  higher than allowed, fail the job and send an email with a table highlighting the publications that are behind Accept a parameter of dynamic length to allow specifying publications that should not be monitored as “production” (ie, do not alert in monitoring). Our repl servers are not SQL 2008 yet, so I use an XML wad…
Read More

Backups – Using SQL Agent Tokens to Set the Date, and Why to go MultiFile

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. We had some backups at work which were running quite slowly, and my colleague Gina reminded me of something I once learned, but had somehow forgotten: backups streamed to multiple files are much faster than those written to single files. This is true even when the files are sitting on the same drive. We’ve switched over the large backups from the existing process, where they wrote a single file to local disk and then robocopied out to a remote location, to the new process where the backup is streamed directly across the network to the backup location using multiple files. The total job runtime is always lower, and in most cases the…
Read More

Management Studio 2008- What I Like

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. But my very favorite thing is really simple. I’m not sure if it even made it into the ‘feature lists’ anywhere. It’s this: When you are running a query in a tab, the top of the tab has the note as ‘Executing’ until it completes. Whomever got in this feature has my sincere and heartfelt thanks. This is incredibly useful to me. I frequently need to run manual commands to verify data to troubleshoot things that shouldn’t be automated, and it’s really nice to be able to move to another tab and periodically glance at the tab header to see when it completes. No more…
Read More