A while back I kicked off a survey asking what mechanism you use for running your regular SQL Server database maintenance.

Here are the results:

maintenance Easy automation of SQL Server database maintenance

The “Other” responses were:

  • 7 x “A combination of maintenance plans generated from SSMS wizard and a home-grown index maintenance script”
  • 5 x “Combo of own and modified scripts of Michelle Ufford”
  • 2 x “Home-grown scripts for main databases and SSMS wizard in some simple cases”
  • 1 x “A combination of enterprise backup agent and AdaptiveIndexDefrag
  • 1 x “A combination of home-grown scripts and Ola’s scripts (for Indexing and Statistics)”
  • 1 x “Combination of Ola’s scripts (indexes/stats) and homegrown (DBCC and backups)”

The main purpose of this survey is to show people that there are freely-available and comprehensive scripts that you can download to help run your regular database maintenance, and that many, many people use them in production.

I don’t like to recommend using the SSMS Maintenance Plan Wizard. It has quite limited options, has had a number of high profile bugs in the past, and to this day, even in SQL Server 2012, it still allows you to perform regular shrink operations without any warning as to the side-effects.

Home grown scripts  are OK, but time after time when I’m reviewing client maintenance scripts I see coding errors, lack of logging of what happened, and lack of useful error handling. An example of a common error is in code to figure out which indexes are fragmented, where the results from sys.dm_db_index_physical_stats are not filtered by alloc_unit_type_desc, so there are false positives from LOB_DATA and ROW_OVERFLOW_DATA allocation units. In one client, many of their large clustered indexes had no logical fragmentation but were being rebuilt every night needlessly because of benign LOB_DATA fragmentation, generating a ton of extra transaction log that had to be backed up.

More than 40% of the almost 500 respondents use some or all of Ola Hallengren’s Maintenance Solution, and I always recommend our clients download and play around with Ola’s scripts before asking us to write customized code for them, or at least let us use Ola’s code where possible to save them consulting time. Ola’s Maintenance Solution has won multiple awards and is very widely used in the SQL Server community. There are also some popular scripts for performing index maintenance from SQL Server MVPs Michelle Ufford and Tara Kizer, but Ola’s are the gold standard and allow you to do backups, index and statistics maintenance, and consistency checks.

One of the cool things about using Ola’s scripts is that they’re tested constantly by thousands of installations around the world and they’re very robust. If you’ve never seen them, or want to upgrade your database maintenance, check them out!