SQL 2008 DBA

SQL Replication Distribution Cleanup Job Blocking Problem

Your rating: None Average: 5 (1 vote)

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:

SQL Saturday Presentation on Replication

Your rating: None

I edited my presentation from SQL Saturday and uploaded: SQL Server Replication Beginner to Expert Part1: http://youtu.be/5ENzsGWz0Eg and Part2: http://youtu.be/jw7cpnjuYJ0.

I hope you enjoy!


Automated Deadlock Alerting

Your rating: None Average: 5 (1 vote)

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.

SQL Replication Documentation Scripts

Your rating: None Average: 5 (2 votes)

Often I have been wanting to document the publishers, publications, subscribers, and subscriptions of my replication topology. Well I finally spent a few minutes to finish what I started and here is the result. Hope you enjoy!

My dissatisfaction with SQL 2012 licensing

Your rating: None

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:

SQL Saturday #68 Transactional Replication Inside and Out

Your rating: None

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.


Automated Restore Database from Last Full Backup

Your rating: None Average: 5 (3 votes)

In my environment, I backup to many files (for faster backups), and need to restore to a custom location. This query gets latest full backup info and restores to the path you specify. Tested on SQL 2005/2008.


SQL Replication Undelivered Command Count

Your rating: None Average: 5 (1 vote)

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.

SQL Replication Error Summary

Your rating: None Average: 4.5 (2 votes)

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 SQL Agent Job 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.

The Ultimate MS SQL Backup Script

Your rating: None Average: 4.4 (9 votes)

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.