• Eitan Blumin

Keep Your MSDB Clean

Updated: Aug 19

As part of its regular, ongoing, day-to-day activities, your SQL Server instance would naturally collect historical data about its automated operations. If left unchecked, this historical data could pile up, leading to wasted storage space, performance hits, and even worse issues.

MSDB would obviously be collecting data about the SQL Agent job executions. But there are also a few other types of historical data that needs to be cleaned up once in a while. In this blog post, I hope to cover all bases and leave no historical data un-cleaned.


On this page:

EDIT: In case you missed it, there is even more stuff covered in part 2!


Output .txt Files

The LOG subfolder in your SQL Server installation folder would normally contain various .txt files used as output files for your maintenance jobs. These include output files for SQL Server's SSIS-based maintenance plans, as well as Ola Hallengren's Maintenance Solution jobs, and any other job steps for which the "Output file" setting was used:

Ola Hallengren's Maintenance Solution contains the "Output File Cleanup" job, which deletes old .txt files that were used as output files for your maintenance job steps. By default, deletes files older than 30 days, that match the structure *_*_*_*.txt. It executes the following command line:

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"

SQL Server's SSIS-based maintenance plans include the "Maintenance Cleanup Task" which looks like this:

When configuring it, be sure to select the "Maintenance Plan text reports" file type, set "txt" as the file extension, and enter your SQL Server's "Log" folder as the folder to be checked.

CommandLog

Speaking of Ola Hallengren's Maintenance Solution, it comes with the CommandLog table which holds the execution history of its maintenance jobs. Don't forget to schedule a cleanup job for that as well, to prevent it from bloating up your database.

Ola Hallengren's solution comes bundled with the "CommandLog Cleanup" job which deletes items from that table older than 30 days. Be sure to add a schedule for that job so that it would actually do something.

Its default T-SQL command looks like this:

DELETE FROM [dbo].[CommandLog]
WHERE StartTime < DATEADD(dd,-30,GETDATE())

Backup History

The MSDB database holds multiple tables dedicated to storing database backup history. These tables include:

In order to clean those up, you should use the sp_delete_backuphistory stored procedure in MSDB.

Ola Hallengren's Maintenance Solution comes bundled with a job called "sp_delete_backuphistory". Be sure to add a schedule for that job so that it would actually do something.

Its default T-SQL command looks like this: