SQLWebpedia

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:
 

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.

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.

Excel doesn't have options for exporting its content out to a CSV file that some programs other than excel like the format of. Recently we purchased VisionApp Remote desktop program and the import process wanted double quoted text and semi-colon delimitors. e.g. "Serverx";"RDP";"",""  But, excel would only quote values that have commas in them, and you can't customize the delimitor easily.

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

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.

One of my servers db's needs a real diet, so I updated a script I found on Brent Ozar’s site to look at clustered tables and tables where counts of use sum to 0. I had to break the coalesce into parts, otherwise you get an error with date and int values showing up in the coalesce.

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.

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!

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: