How to automate SQL Server Express database maintenance using Windows Task Scheduler and Ola Hallengren’s maintenance solution

Problem

SQL Server Express is targeted embedded and smaller-scale applications, and one of the technical restrictions in the product is that the SQL Server Agent is not included.
And without the SQL Server Agent there is no built-in ways to automate tasks like database maintenance.

Solution

The objective of this article is to show how to perform database maintenance on a SQL Server Express installation by using Windows Task Scheduler as automation engine and the world-famous database maintenance solution from Ola Hallengren.

The article uses SQL Server Express 2019 installed on Windows Server 2019, but the scripts and examples has also been tested using SQL Server Express 2008R2 installed on Windows Server 2008R2.

The version of Ola Hallengren’s maintenance solution used in this blog is the “2020-01-26 14:06:53” version.

The steps to perform are:

• Install Ola Hallengren’s maintenance solution
• Create Windows Task Scheduler tasks
• Monitoring

Install Ola Hallengren’s maintenance solution

The installation of Ola Hallengren’s (OLA) maintenance solution has been described in the OLA FAQ but I will highlight the following:

• Change the installation database from the master database to a “Tools” database.
  Even though that the OLA solution only consists of one table and four stored procedures, I like to keep the master database as clean as possible.
• Remember to schedule the cleanup, delete and purge jobs.
  Too often I have seen usage of OLA where the ordinary maintenance jobs have been scheduled, but the cleanup jobs have been forgotten to be scheduled.

When installing the full OLA maintenance solution on a non-Express edition of SQL Server and given that the default value for @CreateJobs has been kept

DECLARE @CreateJobs nvarchar(max) = 'Y'

the following jobs will be created, but not scheduled.

In this blog I will show creation of Windows Task Scheduler tasks for each of these jobs.

Create Windows Task Scheduler tasks

Creating tasks is easy and involves the following steps:

• Name task and assign account to be used when running the task
• Add the required schedule
• Specify the command to be executed

In the following I have described how to create a task using the Task Scheduler GUI but the creation could also have been done using the command line tool schtasks (https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/schtasks)

The detailed description here shows how to create the CommandLogCleanup task and afterwards there will be sections for each of the tasks to be created showing the script files to be used as well as explanation of the chosen parameters.

Start the Windows Task Scheduler and right-click on the canvas and select Create New Task

Set the following in the General section:

• Name of the task
• Assign the account – the built-in account “NT AUTHORITY\SYSTEM” is used here as the tasks created are targeted the local machine where the SQL server is installed

Set the schedule for when the task must execute in the Triggers section:

Specify the program/script to be executed in the Actions section:

The task has now been created

After the task has executed the log file has been created

Create housekeeping tasks

The first 4 tasks to be listed are the ones easy to be forgotten, the housekeeping tasks.

The OLA FAQ recommendation for schedule of the housekeeping tasks are once every week.

Command Log Cleanup

This task deletes records older than 30 days logged by OLA into the table dbo.CommandLog.

Program/script:

D:\OLA\Scripts\CommandLogCleanup.cmd

Content of the script file CommandLogCleanup.cmd

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "DELETE FROM dbo.CommandLog WHERE StartTime < DATEADD(dd, -30, GETDATE());" -b -o D:\OLA\Logs\CommandLogCleanup.txt

Output File Cleanup

This task deletes files named *_*_*_*.txt in the SQL Server log directory and which are older than 30 days.

The SQL Server agent job for Output File Cleanup created by OLA uses the tokens ESCAPE_SQUOTE and SQLLOGDIR (https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?view=sql-server-ver15) which are only available within SQL Server agent job steps.

cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "$(ESCAPE_SQUOTE(SQLLOGDIR))" /m *_*_*_*.txt /d -30 2^>^&1') do if EXIST "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v echo del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v& del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v"

The script to be used by the Windows Task Scheduler is here shown where SQL Server Express 2019 has been installed on the C drive and uses the default directory location.

Program/script:

D:\OLA\Scripts\OutputFileCleanup.cmd

Content of the script file OutputFileCleanup.cmd:

@echo off

set _SQLLOGDIR_=C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log
set _LOGFILE_=D:\OLA\Logs\OutputFileCleanup.txt

if exist %_LOGFILE_% del %_LOGFILE_% > nul:

cmd /q /c "For /F "tokens=1 delims=" %%v In ('ForFiles /P "%_SQLLOGDIR_%" /m *_*_*_*.txt /d -30 2^>^&1') do if EXIST "%_SQLLOGDIR_%"\%%v echo del "%_SQLLOGDIR_%"\%%v& del "%_SQLLOGDIR_%"\%%v" >> %_LOGFILE_%

Delete Backup History

This task deletes backup history records in the msdb database which are older than 30 days.

This is important to remember as missing cleanup of old backup history records can severely affect the backup performance (https://www.brentozar.com/blitz/msdb-history-not-purged/)

Program/script:

D:\OLA\Scripts\DeleteBackupHistory.cmd

Content of the script file DeleteBackupHistory.cmd:

sqlcmd -E -S .\SQLEXPRESS -d msdb -Q "DECLARE @CleanupDate datetime; SET @CleanupDate = DATEADD(dd, -30, GETDATE()); EXECUTE dbo.sp_delete_backuphistory @oldest_date = @CleanupDate;" -b -o D:\OLA\Logs\DeleteBackupHistory.txt

Purge Job History

On a SQL Server Express this task is not relevant as Express does not have any agent and therefore there will be no records to be cleaned up.

However, I have created the scripts in case you will use it on non-Express versions of the SQL Server

This task deletes SQL Server agent job history records in the msdb database which are older than 30 days.

Program/script:

D:\OLA\Scripts\PurgeJobHistory.cmd

Content of the script file PurgeJobHistory.cmd:

sqlcmd -E -S .\SQLEXPRESS -d msdb -Q "DECLARE @CleanupDate datetime; SET @CleanupDate = DATEADD(dd, -30, GETDATE()); EXECUTE dbo.sp_purge_jobhistory @oldest_date = @CleanupDate;" -b -o D:\OLA\Logs\PurgeJobHistory.txt

Create maintenance tasks

The following tasks are the 4 backup tasks, the 2 integrity check tasks and the 2 index optimize tasks.

The question of when to perform backup of user databases is impossible to answer here except in very general terms and it all relates to your business requirements and the terms RTO and RPO (https://en.wikipedia.org/wiki/Disaster_recovery)

Backup of system databases should happen regularly and whenever changed to the SQL server instance have been made.

As this blog is targeted SQL Server Express and the database size is limited to 10 GB in Express, I would recommend running full backup of system and user databases at least once every day.

Also remember to schedule the log backup as it is the only way to maintain the transaction log of databases in Full recovery model. Neither a Full nor a Diff backup maintain the transaction log.

Database backup

The following 4 database backup scripts uses the OLA stored procedure DatabaseBackup and where the following parameters are used here. For a complete list of all parameters and their usage look here in OLA SQL Server Backup.

DatabaseBackup – SYSTEM_DATABASES – FULL

This task performs full backup of the 3 system databases: master, model and msdb.

Program/script:

D:\OLA\Scripts\DatabaseBackupSystemDatabasesFull.cmd

Content of the script file DatabaseBackupSystemDatabasesFull.cmd:

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.DatabaseBackup @Databases = 'SYSTEM_DATABASES', @Directory = NULL, @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y';" -b -o D:\OLA\Logs\DatabaseBackupSystemDatabasesFull.txt

DatabaseBackup – USER_DATABASES – FULL

This task performs full backup of all user databases.

Program/script:

D:\OLA\Scripts\DatabaseBackupUserDatabasesFull.cmd

Content of the script file DatabaseBackupUserDatabasesFull.cmd:

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = NULL, @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y';" -b -o D:\OLA\Logs\DatabaseBackupUserDatabasesFull.txt

DatabaseBackup – USER_DATABASES – DIFF

This task performs differential backup of all user databases.

Program/script:

D:\OLA\Scripts\DatabaseBackupUserDatabasesDiff.cmd

Content of the script file DatabaseBackupUserDatabasesDiff.cmd:

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = NULL, @BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y';" -b -o D:\OLA\Logs\DatabaseBackupUserDatabasesDiff.txt

DatabaseBackup – USER_DATABASES – LOG

This task performs transactional backup of all user databases using Full recovery model.

Program/script:

D:\OLA\Scripts\DatabaseBackupUserDatabasesLog.cmd

Content of the script file DatabaseBackupUserDatabasesLog.cmd:

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = NULL, @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y';" -b -o D:\OLA\Logs\DatabaseBackupUserDatabasesLog.txt

Database integrity check

The following 2 database integrity check scripts uses the OLA stored procedure DatabaseIntegrityCheck and where the following parameters are used here. For a complete list of all parameters and their usage look here in OLA SQL Server Integrity Check.

In the OLA FAQ the recommendation is to “perform the full backup after the integrity check. Then you know that the integrity of the backup is okay.“.

As the command @CheckCommands is not specified a “DBCC CHECKDB” is executed against the selected databases.

DatabaseIntegrityCheck – SYSTEM_DATABASES

This task performs integrity check of the 3 system databases: master, model and msdb.

Program/script:

D:\OLA\Scripts\DatabaseIntegrityCheckSystemDatabases.cmd

Content of the script file DatabaseIntegrityCheckSystemDatabases.cmd:

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'SYSTEM_DATABASES', @LogToTable = 'Y';" -b -o D:\OLA\Logs\DatabaseIntegrityCheckSystemDatabases.txt

DatabaseIntegrityCheck – USER_DATABASES

This task performs integrity check of all user databases.

Program/script:

D:\OLA\Scripts\DatabaseIntegrityCheckUserDatabases.cmd

Content of the script file DatabaseIntegrityCheckUserDatabases.cmd:

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'USER_DATABASES', @LogToTable = 'Y';" -b -o D:\OLA\Logs\DatabaseIntegrityCheckUserDatabases.txt

Index optimize

The following 2 index optimize scripts uses the OLA stored procedure IndexOptimize and where the following parameters are used here. For a complete list of all parameters and their usage look here in OLA SQL Server Index and Statistics Maintenance.

When should you run the index optimize scripts? Again often. I will suggest that you run them daily and after the database log backup. By doing it that way the transaction log for databases using full recovery model has been maintained right before the index optimization scripts executes. The OLA FAQ also “recommend that you run a full backup after the index maintenance. The following differential backups will then be small“.

IndexOptimize – SYSTEM_DATABASES

This task performs index optimization of the system databases master and msdb.

Agent job for index optimization of system databases are not created by default by OLA, but I have used the blog by Igor Micev (https://www.sqlservercentral.com/blogs/indexes-optimize-for-the-system-databases) as source.

Program/script:

D:\OLA\Scripts\IndexOptimizeSystemDatabases.cmd

Content of the script file IndexOptimizeSystemDatabases.cmd:

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.IndexOptimize @Databases = 'master,msdb', @MSShippedObjects = 'Y', @LogToTable = 'Y';" -b -o D:\OLA\Logs\IndexOptimizeSystemDatabases.txt

IndexOptimize – USER_DATABASES

This task performs index optimization of all user databases.

Program/script:

D:\OLA\Scripts\IndexOptimizeUserDatabases.cmd

Content of the script file IndexOptimizeUserDatabases.cmd:

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @LogToTable = 'Y';" -b -o D:\OLA\Logs\IndexOptimizeUserDatabases.txt

Monitoring

Automation is wonderful but must always be monitored.

Backup

Backups must be checked and as stated earlier the only way to ensure that backups are valid are to perform a real restore of the backup files.

Integrity check

Integrity check should be done often e.g. every day.

BUT integrity check is worthless if you don’t examine the output of the integrity check tasks.

By using the parameter @LogToTable = ‘Y’ when running the OLA commands, it is easy to see if any errors occurred by inspecting the CommandLog table.

Here is an example of integrity check where a database was corrupted (thanks to Paul Randal and Kimberly L. Tripp that created corrupted databases that you can download for training)

SELECT cl.ID, cl.DatabaseName, cl.CommandType, cl.StartTime, cl.EndTime, cl.ErrorNumber
FROM Tools.dbo.CommandLog AS cl
WHERE (cl.ErrorNumber <> 0)
ORDER BY cl.ID DESC;

Acknowledgement

Finally I will thanks my former colleague Sune T. Tougaard who have taken time to review and comment the draft versions of this blog.