Search Wiki:

How to Automate Maintenace Tasks with SQL Server Express


Jonathan Kehayias, May 5, 2008

One of the items missing from SQL Server Express is the ability to schedule jobs to run at set times since it lacks the SQL Server
Agent Service. This makes scheduling automated maintenance tasks that are easily available in the other editions of SQL Server
through Database Maintenance Plans, difficult at best, but not impossible. Since the Database Maintenance Plans cover a number
of tasks, they will be broken down individually to allow for implemenation of all or some of the tasks.

The commonly used tasks in the Database Maintenance Plan Wizard are:




Check Database Integrity

Second only to good database backups, validating the integrity of the SQL Databases should be a top priority for anyone running a
SQL Database. To accomplish this, a simple stored procedure will be created that allows for the database consistency checks to be
run against every database in an instance or a single database only.

CREATE PROCEDURE [dbo].[CheckDatabaseIntegrity]
(
	@DatabaseName sysname = null
)
AS
 
-- -- Begin Test Code
--DECLARE @DatabaseName sysname
--SET @DatabaseName = 'tempdb'
-- -- End Test Code
 
IF @DatabaseName IS NULL
BEGIN
	EXEC sp_msforeachdb 'DBCC CHECKDB(''?'')'
END
ELSE
	EXEC ('DBCC CHECKDB('''+@DatabaseName+''')'
END
 
 
/*
-- Execute Full Consistency Check of all Databases
sqlcmd -S .\EXPRESS -Q "EXEC CheckDatabaseIntegrity"
 
-- Execute Full Consistency Check of the WebContacts Database
sqlcmd -S .\EXPRESS -Q "EXEC CheckDatabaseIntegrity @DatabaseName='WebContacts'"
 
*/
 

Return to Top


Shrink Database

From time to time it is necessary to shrink the size of a database down, perhaps monthly after a data purge process runs. To allow this,
a shrinking stored procedure will be built.

CREATE PROCEDURE [dbo].[ShrinkDatabase]
(
	@DatabaseName sysname = null,
	@FreeSpace int = 0
)
AS
 
-- -- Begin Test Code
--DECLARE @DatabaseName sysname
--SET @DatabaseName = 'tempdb'
-- -- End Test Code
 
IF @DatabaseName IS NULL
BEGIN
	EXEC sp_msforeachdb 'DBCC SHRINKDATABASE(''?'', @FreeSpace)'
END
ELSE
	EXEC ('DBCC SHRINKDATABASE('''+@DatabaseName+''', @FreeSpace)'
END
 
 
/*
-- Execute Full Shrink Check of all Databases
sqlcmd -S .\EXPRESS -Q "EXEC ShrinkDatabase"
 
-- Execute Full Shrink of the WebContacts Database
sqlcmd -S .\EXPRESS -Q "EXEC ShrinkDatabase @DatabaseName='WebContacts'"
 
-- Execute Shrink of the WebContacts Database Leaving 10% FreeSpace
sqlcmd -S .\EXPRESS -Q "EXEC ShrinkDatabase @DatabaseName='WebContacts' @FreeSpace=10"
 
*/
 

Return to Top


Perform Index Maintenance

Index Maintenance is crucial to optimum performance of a SQL Server Database. Microsoft recommends that indexes
containing over 30% fragmentation be rebuilt, while indexes having less than 30% fragmentation be reorganized. In order
to implement this process in SQL Express, two stored procedures will need to be created. The first one is a modified version
of a procedure provided by Bill Baer for performing database maintenance on Sharepoint databases, a task that was
unsupported using Database Maintenance Plans with the RTM release of SQL Server. The reason that his procedure was
chosen for this task is two fold. First, it is provided in a Microsoft Whitepaper, and second, it updates statistics as a part of its
execution.

USE [master]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- This stored procedure checks all indexes in the current
-- database and performs either offline or online defragmentation
-- according to the specified thresholds.
-- The stored procedure also updates statistics for indexes in which the last update
-- time is older than the specified threshold.
-- Parameters:
--	@onlineDefragThreshold specifies minimum percentage of fragmentation 
--	to perform online defragmentation (default 10%).
--	@offlineDefragThreshold specifies minimum percentage of fragmentation 
--	to perform offline defragmentation (default 30%).
--	@updateStatsThreshold specifies the number of days since the last statistics update
--	which should trigger updating statistics (default 7 days).
-- =============================================
CREATE PROCEDURE [dbo].[sp_DefragIndexes] 
(
	@databaseName sysname = null,
	@onlineDefragThreshold float = 10.0,
	@offlineDefragThreshold float = 30.0,
	@updateStatsThreshold int = 7
)
	
AS
BEGIN
 
IF @databasename is null
BEGIN
	RETURN;
END
 
DECLARE @SQL nvarchar(max)
SET @SQL = 'USE '+ @databasename +'
 
	set nocount on
	DECLARE @objectid int
	DECLARE @indexid int
	DECLARE @frag float
	DECLARE @command varchar(8000)
	DECLARE @schemaname sysname
	DECLARE @objectname sysname
	DECLARE @indexname sysname
 
	declare @AllIndexes table (objectid int, indexid int, fragmentation float)
 
	declare @currentDdbId int
	select @currentDdbId = DB_ID()
	
	insert into @AllIndexes
	SELECT 
		object_id, index_id, avg_fragmentation_in_percent 
	FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL , NULL, ''LIMITED'')
	WHERE index_id > 0
 
	DECLARE indexesToDefrag CURSOR FOR SELECT * FROM @AllIndexes
 
	OPEN indexesToDefrag;
 
	-- Loop through the partitions.
	FETCH NEXT
	   FROM indexesToDefrag
	   INTO @objectid, @indexid, @frag;
 
	WHILE @@FETCH_STATUS = 0
		BEGIN
 
		SELECT @schemaname = s.name
		FROM sys.objects AS o
		JOIN sys.schemas as s ON s.schema_id = o.schema_id
		WHERE o.object_id = @objectid
 
		SELECT @indexname = name 
		FROM sys.indexes
		WHERE  object_id = @objectid AND index_id = @indexid
 
		IF @frag > @onlineDefragThreshold
		BEGIN 
			IF @frag < @offlineDefragThreshold
				BEGIN;
					SELECT @command = ''ALTER INDEX '' + @indexname + '' ON '' + 
							@schemaname + ''.'' + object_name(@objectid) + 
							'' REORGANIZE''
					EXEC (@command)
				END
 
			IF @frag >= @offlineDefragThreshold
				BEGIN;
					SELECT @command = ''ALTER INDEX '' + 
							@indexname +'' ON '' + @schemaname + ''.'' + 
							object_name(@objectid) + '' REBUILD''
					EXEC (@command)
				END;
			PRINT ''Executed '' + @command
		END
 
		IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, getdate())
		BEGIN
			SELECT @command = ''UPDATE STATISTICS '' + @schemaname + ''.'' + object_name(@objectid) + 
					'' '' + @indexname +'' WITH RESAMPLE''
			EXEC (@command)
 
			PRINT ''Executed '' + @command
		END
 
		FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag
 
	END
 
	CLOSE indexesToDefrag;
	DEALLOCATE indexesToDefrag;'
 
DECLARE @Params nvarchar(max)
SET @Params = N'
	@onlineDefragThreshold float,
	@offlineDefragThreshold float,
	@updateStatsThreshold int'
 
EXECUTE sp_executesql @SQL, 
		@Params,
		@onlineDefragThreshold=@onlineDefragThreshold,
		@offlineDefragThreshold=@offlineDefragThreshold,
		@updateStatsThreshold=@updateStatsThreshold;
END
 
 
 

The second procedure is the actual wrapper procedure that will be called from the command line to schedule the database
maintenance. Like all of the other procedures in this it accepts the database name as an input parameter to allow for
reindexing a single database, but it can also reindex all of the databases on the SQL Server by calling it without the parameter.


CREATE PROCEDURE [dbo].[PerformIndexMaintenance]
(
	@DatabaseName sysname = null
)
AS
 
-- -- Begin Test Code
--DECLARE @DatabaseName sysname
--SET @DatabaseName = 'tempdb'
-- -- End Test Code
 
IF @DatabaseName IS NULL
BEGIN
	EXEC sp_msforeachdb N'EXEC sp_DefragIndexes ''[?]'''
END
ELSE
	SET @DatabaseName = '['+REPLACE(REPLACE(@DatabaseName,'[', ''),']','')+']'
	EXEC sp_DefragIndexes @DatabaseName
END
 
 
/*
-- Execute Index Maintenance of all Databases
sqlcmd -S .\EXPRESS -Q "EXEC PerformIndexMaintenance"
 
-- Execute Index Maintenance on the WebContacts Database
sqlcmd -S .\EXPRESS -Q "EXEC PerformIndexMaintenance @DatabaseName='WebContacts'"
 
*/
 
GO
 

Return to Top


History Cleanup

SQL Server tracks the backup history of your server in the msdb database in a series of tables. The core of this
task in the Database Maintenance Plans is the spdeletebackuphistory procedure in msdb. A simple wrapper
procedure will be created to call this procedure providing the @oldestdate parameter automatically set to the
current date minus 30 days.

CREATE PROCEDURE [dbo].[CleanupHistory]
AS
 
DECLARE @OldestDate DATETIME
SET @OldestDate = CONVERT(varchar(10), DATEADD(dd, -30, GETDATE()), 101)
 
EXEC sp_delete_backuphistory @OldestDate
 
/*
-- Execute Cleanup Task
sqlcmd -S .\EXPRESS -Q "EXEC CleanupHistory"
 
*/
 

Return to Top



Maintenance Cleanup Task

This task is used to delete files created by the Maintenance Tasks executing. Generally it is used to delete backup files from the
backup path that are older than a set number of days. To accomplish this, a simple VBScript can be created based on the
following code:

 
'Start
 
Option Explicit
on error resume next
    Dim oFSO
    Dim sDirectoryPath
    Dim oFolder
    Dim oFileCollection
    Dim oFile
    Dim iDaysOld
    Dim CurDir
    Dim strExtension
 
'Definitions
    iDaysOld = 3
    strExtension = ".bak"
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    sDirectoryPath = CreateObject("WScript.Shell").CurrentDirectory
    sDirectoryPath = sDirectory & "\"
    set oFolder = oFSO.GetFolder(sDirectoryPath)
    set oFileCollection = oFolder.Files 
 
'Walk through each file in this folder collection. 
    For each oFile in oFileCollection
        If oFile.DateLastModified < (Date() - iDaysOld) Then
  If (strExtension="") Or (Right(UCase(oFile.Name), Len(strExtension))=UCase(strExtension)) Then
             oFile.Delete(True)
  End If
        End If
    Next
 
'Clean up
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oFileCollection = Nothing
    Set oFile = Nothing
'End
 

This code has two variables, extension and number of days which can be set to control what it will delete. To use it, you simply
create a DeleteFiles.vbs file with this code in it and your needed values in the folder to have the files deleted. Then schedule a
task to execute this on the necessary schedule using the CScript.exe executable like the following:

cscript "C:\BackUp\DeleteFiles.vbs"


Return to Top




Other Published Solutions

The above provided information is not the only method of automating these tasks. After this article was in progress, a post on
the forums made me aware of two articles by Jasper Smith that provide methods for Automating Maintenance in SQL Express.
They are available on the following links:

Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29


Ola Hallengren has also provided a really nice stored procedure set that were tested against SQL Server Express, and also
provided the following code examples for how to use them with SQL Express:

Backup of system and user databases
-- Objects needed: DatabaseBackup, CommandExecute, DatabaseSelect
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'SYSTEM_DATABASES', @Directory = 'C:\Backup', 
@BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24" -b
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'USER_DATABASES', @Directory = 'C:\Backup', 
@BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24" -b
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'USER_DATABASES', @Directory = 'C:\Backup', 
@BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 24" -b
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'USER_DATABASES', @Directory = 'C:\Backup', 
@BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 24" -b
 

Integrity check of system and user databases
--Objects needed: DatabaseIntegrityCheck, CommandExecute, DatabaseSelect
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] 
@Databases = 'SYSTEM_DATABASES'" -b
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] 
@Databases = 'USER_DATABASES'" -b

Index Optimization of user databases
--Objects needed: IndexOptimize, CommandExecute, DatabaseSelect
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES'" -b
 

All objects are available on his blog and are free to download.
http://ola.hallengren.com

There is also documentation available here.
http://ola.hallengren.com/Documentation.html



For additional information please see:


Books Online References

DBCC
http://msdn.microsoft.com/en-us/library/ms188796.aspx

DBCC CHECKDB
http://msdn.microsoft.com/en-us/library/ms176064.aspx

DBCC SHRINKDATABASE
http://msdn.microsoft.com/en-us/library/ms190488.aspx

ALTER INDEX
http://msdn.microsoft.com/en-us/library/ms188388.aspx

Related Whitepapers and Documents

Reorganizing and Rebuilding Indexes
http://msdn.microsoft.com/en-us/library/ms189858.aspx

SQL Database Maintenance for Sharepoint Whitepaper
http://go.microsoft.com/fwlink/?LinkId=111531&clcid=0x409



___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, May 21, 2008
Last edited Dec 11 2009 at 5:58 PM  by JonathanKehayias, version 8
Updating...
Page view tracker