T-SQL Tuesday #050: Automation, how much of it is the same?

SQL-Tuesday.jpgNot a better way to start off the year with a good ole fashioned T-SQL Tuesday block party. Just what is this block party I am speaking of…well I’m glad you asked.

What is T-SQL Tuesday?
T-SQL Tuesday is a monthly blog party hosted by a different blogger each month. This blog party was started by Adam Machanic (blog|twitter). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Adam Machanic on his blog.

Who is hosting this week?

slqchow is hosting these weeks theme on automation to check out more about him you can visit his blog at (blog|twitter)

Lights, Cameras, AUTOMATE

As a data professional automation is key and often times can be taken for granted. Most professionals take automation to mean tasks such as the following:

  • Daily Backups
  • Index Maintenance
  • Job Failure Notifications
  • Job Success Notifications
  • Routine Maintenance
  • T-Log Shipping
  • Disk Alerts
  • Space Alerts
  • New Servers
  • Extended blocking
  • Deployment Automation – thanks RedGate (Twitter)

…and the list could go on for quite some time.

One specific item that I have found helpful to me is related to CMS/PBM. I like this tool because you can verify and evaluate all your servers from one central location. Some take-a-ways to think about CMS/PBM are evaluations such as:

  • Last Successful Backup Date
  • Database Page Verification
  • Database Auto Shrink
  • Database Auto Close
  • Data and Log File Location
  • Backup and Data File Location
  • Blocked Process Threshold
  • All SQL Agent jobs succeeded in the last 24 hrs (you and I both know that notifications don’t always get set up)

I am not going to go into specifics on creating an .rdl however once you have all the policies in place from there there an .rdl file can be created and a report emailed to you directly at the start of the business day, simple as that.

If you are interested in CMS\PBM check out John Sterrett’s (blog|twitter) information that he has published here

Also, another great article published is over at John Sansom’s (blog|twitter) on automation

One last piece of advice that I will give to myself along with this post is doing more extensive research on the use of PowerShell. It seems pretty powerful and easy to use up to this point, and it would appear that a lot of tasks can be automated through such.


In today’s Data Professional world if you are not automating tasks that will make you more efficient then why not start today? There are so many ways to automate tasks but with that said comes great responsibility. Before you just set out blindly get some thoughts and goals down on paper then start researching and exploring. Many Data Professionals before us have blazed these same trails, sure you might find something along the way that someone else has worked on and then you take it to another place but always remember to give credit where credit is due.

To Automate or Not to Automate that is the question?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s