maintenance

SQL Server Maintenance Solution

Earlier this year, I posted about a tool from Brent Ozar called spBlitz and how it gives you amazing insight into configuration problems with your SQL Servers. Well today, I am here to tell you about another great SQL tool available for free online and that is the SQL Server Maintenance Solution by Ola Hallengren, a Swedish database administrator and was awarded a Microsoft MVP this year for the first time.

You can download his tool from https://ola.hallengren.com/ and on the site, there is full documentation for all of the features of the tool including the most common configuration examples and its use so you can get up and running really quickly with it.

The SQL Server Maintenance Solution is a .sql file that you download and allow it to install itself as a series of Stored Procedures in your master database. The tool works by invoking its Stored Procedures as SQL Agent Jobs and by default will create a number of these unless you opt not to during the install by changing one of the lines in the .sql file.

I opted to not install the default jobs but to create my own so I could configure how and what I wanted the scripts to do but it really is so simple that no administrator of SQL has any reason to not be performing good routine maintenance. I am using Ola’s scripts to both perform routine DBCC CHECKDB consistency and also to perform index defragmentation on databases which is it’s real power.

The reason Ola’s scripts beat a SQL Maintenance Plan for index defragmentation and the main reason I wanted to use them is that Ola gives us the flexibility to perform different actions according to the level of fragmentation so for example, I could do nothing if fragmentation in an index is below 10%, reorganise an existing index if fragmentation is between 10% and 30% and completely rebuild the index if it is over 30%. Compare this to a SQL Maintenance Plan where your option is reorganise or rebuild regardless of fragmentation level and you can see the advantage.

So now, that’s to the community and Brent and Ola, we can check the configuration of our SQL Servers to make sure they are happy and safe as well as easily configure our daily and weekly checks and maintenance on databases to keep our server and our databases happy and we all know that happy databases means happy software.

In another post coming up soon, I will show you how we can update the configuration of our SCOM Management Pack for SQL Server so that we can receive alerts for failed SQL Server Agent Jobs, allowing us to centralise our knowledge, reporting and alerting for SQL maintenance tasks.