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
-- -- Begin Test Code
--DECLARE @DatabaseName sysname
--SET @DatabaseName = 'tempdb'
-- -- End Test Code
IF @DatabaseName IS NULL
	EXEC sp_msforeachdb 'DBCC CHECKDB(''?'')'
	EXEC ('DBCC CHECKDB('''+@DatabaseName+''')'
-- 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
-- -- Begin Test Code
--DECLARE @DatabaseName sysname
--SET @DatabaseName = 'tempdb'
-- -- End Test Code
IF @DatabaseName IS NULL
	EXEC sp_msforeachdb 'DBCC SHRINKDATABASE(''?'', @FreeSpace)'
	EXEC ('DBCC SHRINKDATABASE('''+@DatabaseName+''', @FreeSpace)'
-- 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

USE [master]
-- =============================================
-- 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
IF @databasename is null
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
		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.
	   FROM indexesToDefrag
	   INTO @objectid, @indexid, @frag;
		SELECT @schemaname =
		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
			IF @frag < @offlineDefragThreshold
					SELECT @command = ''ALTER INDEX '' + @indexname + '' ON '' + 
							@schemaname + ''.'' + object_name(@objectid) + 
							'' REORGANIZE''
					EXEC (@command)
			IF @frag >= @offlineDefragThreshold
					SELECT @command = ''ALTER INDEX '' + 
							@indexname +'' ON '' + @schemaname + ''.'' + 
							object_name(@objectid) + '' REBUILD''
					EXEC (@command)
			PRINT ''Executed '' + @command
		IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, getdate())
			SELECT @command = ''UPDATE STATISTICS '' + @schemaname + ''.'' + object_name(@objectid) + 
					'' '' + @indexname +'' WITH RESAMPLE''
			EXEC (@command)
			PRINT ''Executed '' + @command
		FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag
	CLOSE indexesToDefrag;
	DEALLOCATE indexesToDefrag;'
DECLARE @Params nvarchar(max)
SET @Params = N'
	@onlineDefragThreshold float,
	@offlineDefragThreshold float,
	@updateStatsThreshold int'
EXECUTE sp_executesql @SQL, 

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
-- -- Begin Test Code
--DECLARE @DatabaseName sysname
--SET @DatabaseName = 'tempdb'
-- -- End Test Code
IF @DatabaseName IS NULL
	EXEC sp_msforeachdb N'EXEC sp_DefragIndexes ''[?]'''
	SET @DatabaseName = '['+REPLACE(REPLACE(@DatabaseName,'[', ''),']','')+']'
	EXEC sp_DefragIndexes @DatabaseName
-- 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'"

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]
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:

Option Explicit
on error resume next
    Dim oFSO
    Dim sDirectoryPath
    Dim oFolder
    Dim oFileCollection
    Dim oFile
    Dim iDaysOld
    Dim CurDir
    Dim strExtension
    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
  End If
        End If
'Clean up
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oFileCollection = Nothing
    Set oFile = Nothing

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
Automating Database maintenance in SQL 2005 Express Edition Part II

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.

There is also documentation available here.

For additional information please see:

Books Online References





Related Whitepapers and Documents

Reorganizing and Rebuilding Indexes

SQL Database Maintenance for Sharepoint Whitepaper

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