On last SQLBits 9 – Query Across Mersey (September 2011, Liverpool, UK) I had great opportunity and pleasure to be a host as my SQLBits Helper role for not very famous, but full professional specialist from Denmark (work place)/Sweden (living place) – Ola Hallengreen at his session. Just back for the past for a moment – here is the abstract of his session: How do you do database maintenance in an enterprise environment? I have developed a stored procedure based solution that has become extremely popular in the SQL Server community. In this session I will go through how the solution works, how it can be used in different scenarios for backup, integrity check, index and statistics maintenance and talk some about the background for the design. You can also bring your questions about the solution or about database maintenance in general. And links for video recording is here: Slide Deck 75 KB + Demo Code 3 KB + MP4 Video Low 64 MB + MP4 Video Med 87 MB + MP4 Video High 205 MB +  MP4 Video HD 382 MB.

In this post I want to write some words about his great solution. Great because is powerful tool, and great because is free (like all tools in Sunday Market series). Based on Ola’s web site: The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. The Solution is based on stored procedures, functions, the sqlcmd utility, and SQL Server Agent jobs.

In one Maintenance Solution script package you can find a lot of scripts in three very important components, where all l are:
Supported versions: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012
Depends on: CommandExecute, DatabaseSelect and CommandLog

SQL Server Backup
Procedure name: DatabaseBackup
Purpose: Back up databases
Example below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[DatabaseBackup]

 

@Databases nvarchar(max),

@Directory nvarchar(max) = NULL,

@BackupType nvarchar(max),

@Verify nvarchar(max) = ‘N’,

@CleanupTime int = NULL,

@Compress nvarchar(max) = NULL,

@CopyOnly nvarchar(max) = ‘N’,

@ChangeBackupType nvarchar(max) = ‘N’,

@BackupSoftware nvarchar(max) = NULL,

@CheckSum nvarchar(max) = ‘N’,

@BlockSize int = NULL,

@BufferCount int = NULL,

@MaxTransferSize int = NULL,

@NumberOfFiles int = 1,

@CompressionLevel int = NULL,

@Description nvarchar(max) = NULL,

@Threads int = NULL,

@Throttle int = NULL,

@Encrypt nvarchar(max) = ‘N’,

@EncryptionType nvarchar(max) = NULL,

@EncryptionKey nvarchar(max) = NULL,

@LogToTable nvarchar(max) = ‘N’,

@Execute nvarchar(max) = ‘Y’

 

AS

#####  #####  #####

SQL Server Integrity Check
Procedure name: DatabaseIntegrityCheck
Purpose: Check the integrity of databases
Example below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck]

 

@Databases nvarchar(max),

@PhysicalOnly nvarchar(max) = ‘N’,

@NoIndex nvarchar(max) = ‘N’,

@ExtendedLogicalChecks nvarchar(max) = ‘N’,

@TabLock nvarchar(max) = ‘N’,

@LogToTable nvarchar(max) = ‘N’,

@Execute nvarchar(max) = ‘Y’

 

AS

##### ##### #####

SQL Server Index and Statistics Maintenance
Procedure name: IndexOptimize
Purpose: Rebuild and reorganize indexes and update statistics
Example below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[IndexOptimize]

 

@Databases nvarchar(max),

@FragmentationLow nvarchar(max) = NULL,

@FragmentationMedium nvarchar(max) = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationHigh nvarchar(max) = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationLevel1 int = 5,

@FragmentationLevel2 int = 30,

@PageCountLevel int = 1000,

@SortInTempdb nvarchar(max) = ‘N’,

@MaxDOP int = NULL,

@FillFactor int = NULL,

@PadIndex nvarchar(max) = NULL,

@LOBCompaction nvarchar(max) = ‘Y’,

@UpdateStatistics nvarchar(max) = NULL,

@OnlyModifiedStatistics nvarchar(max) = ‘N’,

@StatisticsSample int = NULL,

@StatisticsResample nvarchar(max) = ‘N’,

@PartitionLevel nvarchar(max) = ‘N’,

@TimeLimit int = NULL,

@Indexes nvarchar(max) = NULL,

@Delay int = NULL,

@LogToTable nvarchar(max) = ‘N’,

@Execute nvarchar(max) = ‘Y’

 

AS

##### ##### #####

 

From Ola’s WEBSITE You can download separated scripts like:

CommandLog.sql / DatabaseSelect.sql / CommandExecute.sql / IndexOptimize.sql / DatabaseIntegrityCheck.sql / DatabaseBackup.sql 

or get all in one file

 MaintenanceSolution.sql   

 

Enjoy!