SQL 2005 DBA
SQL Replication Distribution Cleanup Job Blocking Problem
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:
Automated Mail Profile Creation and Send Mail Procedure
Once you have your server configured (see http://www.sqlwebpedia.com/content/automatic-method-configure-sql-server-mail-settings for a procedure to do this), you need to create mail profiles, but this can be a pain, so I wrapped the sp_send_dbmail in proc prc_InternalMail and it creates profiles on the fly as I like to see quickly where my emails are coming from in the sender's name.
Automatic method to Configure SQL Server Mail Settings
This procedure prc_DBA_ConfigureMail will help you configure your server so that you can email from SQL Server. Change all the placeholder values like email addresses and proper mail server addresses. Run the proc giving the two parameter values and if it returns a registry write statement, run it and restart SQL Agent to set it to allow failed jobs to email operators.
SQL Saturday Presentation on Replication
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!
-Chuck
Automated Deadlock Alerting
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.
Uninstalling SQL Instance from Cluster
You can't do it from Add/Remove programs, you have to do it from SQL installer, Maintenance Tab.
Passive node uninstalls take about 5-10 minutes. The active node takes a while longer.
In the picture below, I went to the Maintenance tab and selected Remove node from a SQL Server Failover Cluster and hit ok button to launch.
SQL Replication Documentation Scripts
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!
Msg 0, Level 11, State 0, Line 0 error adding data file
I got this error when trying to add a file to a full database:
Msg 0, Level 11, State 0, Line 0. A severe error occurred on the current command.
But, nothing was logged anywhere that gave me a hint as to what the problem was. I knew from past experience that sometimes if the first data file in a database is completely full, you will get an error like this. But, I had 33MB available. So, I gave it 50MB more and was able to add files to the database again. Hope this helps someone.
SQL Saturday #68 Transactional Replication Inside and Out
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.
http://sqlsaturday.com/viewsession.aspx?sat=68&sessionid=3979
