SQL 2008 DBA
I have been having a real issue with Replication's Distribution Cleanup job on our dedicated distributor server. It prevents the subscribers from updating their history, and making all my custom reports look like everything is latent, when in reality it may or may not be latent. I created a job to stop the job if it has been blocking for more than 5 minutes with this bit of code:
Ever use the BOL code to capture Deadlock events as WMI events, but were left with how to effectively use? Well, same here! What I created is a script that utilizes my DBOPS database that I have on every server to log the information, but I also email the captured data via my custom email proc (sp_db_sendmail works just fine if you have all the accounts created properly). You also don't need a trace flag to make this work. I hope you like this.
Hardware has been ever increasing in strength, but SQL licensing has not reflected this except for charging more money due to the per core cost. We are in the information and data collecting age and Microsoft’s scale limitations (http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx) for the versions of SQL might have been fine in the past, but they are not now.
Some examples of limitations that should change:
This past weekend, I gave a talk at the Olympia, WA SQL Saturday conference on Transaction Replication. I am posting up my slide deck and script files for monitoring replication performance and last hours replication errors. Both of these procs need to be run on your distributor server and the prc_internalsendmail can be replaced with your mail proc of choice, including sp_send_dbmail.
Ever wanted to get an email summary of subscriptions that are behind a certain amount? This script can do it! Just setup as a job on your distributor and it will send you a summary email with how many commands are yet to be delivered in a table format for easy reading. Referenced prc_internalsendmail proc can be found on this site at: http://www.sqlwebpedia.com/content/automated-mail-profile-creation-and-send-mail-procedure
This proc will monitor for errors so you don't have to monitor Replication Monitor (Replmon) constantly, nor are you forced to use the inflexible builtin alerting. Schedule it as a job on your distributor box and it will email you only when there are errors. It will provide a code segment for you to run to see all the errors if there were multiple lines of errors, which is common, but the comments column should be enough to figure it out or at least point out that there is a problem.
I am finally getting around to publishing my ultimate Microsoft SQL Server Backup scripts. All told, it is about 1600 lines of code and it can do just about anything you would need for Native or Quest Litespeed backups.
Here are some examples so you get idea of its power.