A complete maintenance plan for SQL Server 2008

SQL Server Maintenance Plan that can backup and restore database files on multiple servers, re-organise or rebuild indexes dependent on time

Optimising Databases

Scrolling CSS Diagram
An optimised database is a happy database. I have extended server life times at companies by implementing the following principles;
  • Keeping database size down
  • Keeping Indexes in optimal shape
  • Separation of Log files and Database files where possible

Steps

In this example, we are going to set up the code to do the following, there is a rough diagram of how the servers are connected;
  • Backup database on live server to reporting server
  • Re-Index Database
  • Kill all connections and restore the database on reporting server from the live server
  • Linked Servers will be in a separate article soon
  • Log Shipping will be in a separate article soon
First lets set up a backup. You can see the code is quite simple, and you can normally script it from the database. 

When doing it cross server, you will need to allow the account that SQL Server is running from access to the other servers file system.

Restore Database

This code can also be scripted from SQL Server Management Studio. If you add this code to a stored procedure, you can call it from other processes and even other servers really easily.

The following script will restore the database into standby mode. To stop standby mode just remove the line.

SQL Code

USE master
GO
CREATE PROC RestoreDatabase_ AS
RESTORE DATABASE [dbnameFROM DISK=N'c:\backupfolder\.bak' WITH FILE= 1, 
MOVE N'' TO N'd:\database\.mdf', 
MOVE N'_log' TO N'e:\database\.ldf',
NOUNLOADREPLACE, STATS= 10,
STANDBY=N'e:\database\ROLLBACK_UNDO_.bak'
GO

Kill Connections

When performing a restore on the database, you can only have one connection to it (the process performing the restore), therefore we can create a stored procedure to close all connections apart from the current process.

The code below will accept a database name, so it only needs to be written once and stored on the master database. It can then be called from any other process, in our case the complete maintenance plan.

The code to kill connections is the EXEC statement at the bottom.

SQL Code

Use master
GO
CREATE PROC KillConnections(@database varchar(50)) AS BEGIN
SET NOCOUNT ON
DECLARE @SPID INT
DECLARE @killstatement nvarchar(100)
DECLARE k CURSOR FOR (SELECT request_session_id FROM sys.dm_tran_locks where resource_type='DATABASE' AND DB_NAME(resource_database_id)= @database)
OPEN k
FETCH NEXT FROM k INTO @SPID--Get first record
WHILE @@FETCH_STATUS= 0--While there are records execute the following code
BEGIN
  -- Don't kill the connection of the user executing this statement!
  IF @@SPID<>@spid
  BEGIN
  -- Construct dynamic sql to kill spid
  set @killstatement ='USE '+@database+';KILL '+cast(@SPID as varchar(5))
  EXEC sp_executesql @killstatement
  PRINT @SPID
  FETCH NEXT FROM k INTO @SPID
  END
--Close Cursor
END
close k;deallocate k
END
GO
EXEC master.dbo.KillConnections ''

Re-Index Database

Next we can add some code to re-index our database, this again is a stored procedure where you only need to tell the system the name.

SQL Code

USE master;
GO
CREATE PROC DatabaseReIndex(@Database VARCHAR(100)) AS BEGIN
DECLARE @DbID SMALLINT=DB_ID(@Database)--Get Database ID
IF EXISTS(SELECT * FROM tempdb.sys.objects WHERE name='Indexes') BEGIN--Delete Temp Table if exists, then create
DROP TABLE TempDb.dbo.Indexes
END
CREATE TABLE TempDb.dbo.Indexes(IndexTempID INT IDENTITY(1,1),SchemaName NVARCHAR(128),TableName NVARCHAR(128),IndexName NVARCHAR(128),IndexFrag FLOAT)
EXEC ('USE '+@Database+';
INSERT INTO TempDb.dbo.Indexes(TableName,SchemaName,IndexName,IndexFrag)
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,sch.name,ind.name IndexName,indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats('+@DbID+', NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
INNER JOIN sys.objects obj on obj.object_id=indexstats.object_id
INNER JOIN sys.schemas as sch ON sch.schema_id = obj.schema_id
WHERE indexstats.avg_fragmentation_in_percent > 10 AND indexstats.index_type_desc<>''HEAP''
ORDER BY indexstats.avg_fragmentation_in_percent DESC')--Get index data and fragmentation, set the percentage as high or low as you need
DECLARE @IndexTempID BIGINT=0,@SchemaName NVARCHAR(128),@TableName NVARCHAR(128),@IndexName NVARCHAR(128),@IndexFrag FLOAT
SELECT * FROM TempDb.dbo.Indexes--View your results, comment out if not needed...
-- Loop through the indexes
WHILE @IndexTempID IS NOT NULL BEGIN
  SELECT @SchemaName=SchemaName,@TableName=TableName,@IndexName=IndexName,@IndexFrag=IndexFrag FROM TempDb.dbo.Indexes WHERE IndexTempID=@IndexTempID
  IF @IndexName IS NOT NULL AND @SchemaName IS NOT NULL AND @TableName IS NOT NULL BEGIN
  IF @IndexFrag<30. BEGIN--Low fragmentation can use re-organise, set at 30 as per most articles
  PRINT 'USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' +@TableName + N' REORGANIZE'
  EXEC('USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName+ N' REORGANIZE')
  END
  ELSE BEGIN--High fragmentation needs re-build
  PRINT 'USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' +@TableName + N' REBUILD'
  EXEC('USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName+ N' REBUILD')
  END
  END
  SET @IndexTempID=(SELECT MIN(IndexTempID) FROM TempDb.dbo.Indexes WHERE IndexTempID>@IndexTempID)
END
END
DROP TABLE TempDb.dbo.Indexes
GO
EXEC master.dbo.DatabaseReIndex ''

Complete Maintenance Plan

Now we can manage all of these separate code segments with another procedure that will call them in the correct order, this will be put in the live server. The code runs as follows;

  • Work out the time           
  • If it is evening re-index the database           
  • Run a backup of the database (add where you need)           
  • If it is evening back up the other databases (add where you need)           
  • Kill connections and restore database (add where you need)       

This code has been tried and tested and ran for periods of months without any issues.

Please note that the shrinking of log files and database files should be kept to a minimum, by running a backup you are emptying the log (although it will retain the space used). If it is needed when it should be run outside of normal working hours.

SQL Code

Use master
GO
CREATE PROC MaintenancePlan AS BEGIN
DECLARE @BackupType VARCHAR(1)='E'
IF DATEPART(HOUR,GETDATE()) BETWEEN 5 AND 21 BEGIN
SET @BackupType='D'
END
--EXEC ('USE TempDb; DBCC SHRINKFILE(templog, 0)');--This is only needed when space is at a premium!
--Re-index Live
IF @BackupType='E' EXEC DatabaseReIndex ''
--Create Backup
BACKUP DATABASE TO DISK=N'\\reportserver\backupfolder\.Bak'
WITH NOFORMAT, INIT, NAME =N'_Backup_For_LogShip', SKIP, NOREWIND, NOUNLOAD, STATS= 10;
--EXEC ('USE ; DBCC SHRINKFILE(_log, 0)');--This is only needed when space is at a premium!
--Backup Other Files at Night
IF @BackupType='E' BEGIN
  EXEC DatabaseReIndex 'dbname'
  --Backup Others
  BACKUP DATABASE [databasename] TO  DISK=N'{backuplocation}'
  WITH FORMAT,INIT, NAME =N'{backupsetname}',SKIP, NOREWIND, NOUNLOAD,  STATS= 10
END
--Restore Backups on other server
EXEC RepServer.master.dbo.KillConnections '';
EXEC RepServer.master.dbo.RestoreDatabase_;
END
GO
Ousia Logo