SQL Server Maintenance Solution

Introduction

Because we needed additional processing power some databases need to be migrated to a new server. On the current server we have a 3^rd party database maintenance tool that works well for keeping the database indexes and table healthy. I discovered that we do not have any more licenses for the new server. The SQL 2008 R2 maintenance plans are rather simple and do not provide the fine tuning tools essential to run the maintenance plans efficiently and with as little time necessary. Some of those databases are nearly 24 hours per day.

SQL Server maintenance solution

The need for an extra license has been reported to management. While they decide on buying an extra license or not, I searched the internet for available and workable tools for free. The SQL Server Maintenance Solution by Ola Hallengren seems to fit what is needed for the time being.

The SQL Server Maintenance Solution is essentially a SQL server script to run on your server.

Installation

• Download the script from Downloads at Ola's site, it is called MaintenanceSolution.sql
• Start SSMS and connect to the database server. You need to have sysadmin rights.
• Open the script file MaintenanceSolution.sql. The script uses the master database.
• To create jobs, edit the lines32 and 33, leave line 31 with @CreateJob = “Y”. In my case we use the R drive on the server

SET @BackupDirectory     = N'R:\Backup' -- Specify the backup root directory.
SET @OutputFileDirectory = N'R:\Log'    -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.

• · Further down the script there is code to create 11 jobs. To distinguish these jobs from other jobs that are defined on the server, I inserted the prefix ‘MNTSOL’ in the names of the jobs. That way they are more easily recognizable and they are listed together.



• SET @JobName01 = 'MNTSOL DatabaseBackup - SYSTEM_DATABASES - FULL'
  SET @JobName02 = 'MNTSOL DatabaseBackup - USER_DATABASES – DIFF'
  Run the script



• The script creates a number of tables and stored procedures in the master database




• TABLE [dbo].[CommandLog]


• FUNCTION [dbo].[DatabaseSelect]


• PROCEDURE [dbo].[CommandExecute]


• PROCEDURE [dbo].[DatabaseBackup]


• PROCEDURE [dbo].[DatabaseIntegrityCheck]


• PROCEDURE [dbo].[IndexOptimize]

• Because @CreateJob = “Y” 11 jobs were created in the SQL Server Agent Jobs section.

• The jobs use the table and stored procedures. They are not scheduled yet. They are not setup to send alerts.

Index Optimize job

As the first test I ran the Index Optimize job on the test server. The user databases are not quite large. The job ran for about 5 minutes. After which I opened the table dbo.CommandLog to see what was registered in there. I saw 6 records, for 4 different databases. Each row explained in detail what kind of action was undertaken on a particular index.

Example of a row in the CommandLog:

DatabaseName	Sales
SchemaName	fact
ObjectName	SalesOrder
ObjectType	U
IndexName	IX_SalesOrder
IndexType	2
StatisticsName	NULL
PartitionNumber	NULL
ExtendedInfo	<ExtendedInfo> <PageCount>666349</PageCount> <Fragmentation>99.1803</Fragmentation></ExtendedInfo>
Command	ALTER INDEX [IX_SalesOrder] ON [Sales].[fact].[SalesOrder] REORGANIZE WITH (LOB_COMPACTION = ON)
CommandType	ALTER_INDEX
StartTime	2012-11-22 10:48:13.717
EndTime	2012-11-22 10:48:16.833
ErrorNumber	0
ErrorMessage	NULL

Conclusion

This looks like a workable solution. I will keep on testing this during the upcoming days .