Wednesday, 17 February 2016

Essential items for the DBA's toolkit.

DBA's have to write a lot of scripts to perform a wide range of functions, from maintenance to monitoring we have to dig deep into various areas of SQL to get the information that we need to do our job. But I don't like re-inventing the wheel and thankfully for us some of the best scripts are already there! Created by leading experts the following scripts are those that I (and many others) use all of the time and consider to be an essential part of our DBA toolkit.

Ola Hallengrens Maintenance Solution is a comprehensive set of scripts for running backups, consistency checks and index maintenance. The award winning solution is ridiculously easy to set up and contains a huge amount configurable options. Needless to say its used by many organisations around the world.

Next up we have sp_whoisactive written by Adam Machanic which is one of the most used scripts in the universe. The script gives you an incredible amount of information  about what is happening within your SQL instance. I have lost count the number of times the script has helped me diagnose problems and like everything one this page is used by database professionals the world over.

Brent Ozar's collection of scripts provide you with some serious troubleshooting capabilities and Sp_blitz is one of my personal favourites. Let this script run and it will return with a wealth of information about your SQL instance, why it is configured correctly and how to rectify it.

Analysing cumulative wait statistics is a fundamental part of troubleshooting and this script by Paul Randal is the one I (and many others) use the most. Just to mention the script originally came from Glenn Berry and was modified by Paul quite a bit.

For me all of these go beyond scripts, they're actually solutions in their own right. Maintenance tasks, monitoring, checking configurations and performance are all covered. Maybe you know of some more, created your own perhaps - let me know!


  1. OK so there are others - Adam Machanic is a nice guy and also this does what no other script does. It also does 80% of performance tuning but.....You have to understand the internals of SQL server.
    I like Glenn Barry's 2012 toolkit which is mighty useful especially with the disk and virtual IO side of things. I would have to say that Ola Hallegren's scripts are being used TOO MUCH. For enterprise uses PLEASE BUY and ENTERPRISE SOLUTION....
    Its maybe that putting something in that is unsuitable is better from the point of view of keeping yourself in work.
    Please take my advice fellow professionals try to understand the product rather than leverage scripts - this helps you "steal the very best"

  2. Yes a very good point, scripts without the knowledge of what they are doing (and how) can be a dangerous proposition - like anything in SQL Server I may add!

  3. Something to be said for "Rolling your own" as well. I keep a mix of mine and stuff from others handy :)