Keep Your MSDB Clean

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:

• Output .txt Files

• CommandLog

• Backup History

• Job History

• Maintenance Plan History

• SQL Server and SQL Server Agent Error Logs

• DB Mail History

• Full-Text Crawl Logs

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:

• backupfile

• backupfilegroup

• backupmediafamily

• backupmediaset

• backupset

• restorefile

• restorefilegroup

• restorehistory

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:

DECLARE @CleanupDate datetime
SET @CleanupDate = DATEADD(dd,-30,GETDATE())
EXECUTE dbo.sp_delete_backuphistory @oldest_date = @CleanupDate

Additionally, SQL Server's SSIS-based maintenance plans contain the "History Cleanup Task" which can also help:

When configuring the task, be sure to enable the "Backup and restore history" option:

Job History

Naturally, the SQL Agent Job history is stored in the MSDB database.

It can be cleaned up using the sp_purge_jobhistory stored procedure in MSDB.

Ola Hallengren's Maintenance Solution comes bundled with a job named "sp_purge_jobhistory" which does the same. Be sure to add a schedule for that job so that it would actually do something.

Its default T-SQL command looks like this:

DECLARE @CleanupDate datetime
SET @CleanupDate = DATEADD(dd,-30,GETDATE())
EXECUTE dbo.sp_purge_jobhistory @oldest_date = @CleanupDate

Additionally, SQL Server's SSIS-based maintenance plans contain the "History Cleanup Task" which can also help:

When configuring the task, be sure to enable the "SQL Server Agent job history" option:

You can also right-click on the SQL Server Agent node in SSMS and select "Properties":

Go to the "History" page and set both of the options that you see:

You should limit the size of the job history log based on size, as well as age. Adjust according to your specific use case and business needs.

I'm not entirely sure about how often these settings are actually enforced by SQL Server, so it wouldn't hurt to have an explicitly scheduled job to do this as well.

Maintenance Plan History

Similarly to job history, you should also be cleaning up your Maintenance Plan history, which inconveniences us by being stored in separate system tables:

• msdb.dbo.sysmaintplan_log

• msdb.dbo.sysmaintplan_logdetails

There's an undocumented stored procedure to clean those up, called sp_maintplan_delete_log. You can use something like the T-SQL command below and schedule a job to run it periodically:

DECLARE @beforeDate datetime  
SET @beforeDate = DATEADD(dd, -30, GETDATE());
EXEC msdb.dbo.sp_maintplan_delete_log @oldest_time = @beforeDate;

As you may have noticed previously, you can also clean up this data using the "History Cleanup Task" in SQL Server's SSIS-based maintenance plan:

When configuring the task, be sure to enable the "Maintenance plan history" option:

SQL Server and SQL Server Agent Error Logs

These log files are what you can access by going to the Maintenance > SQL Server Logs node, or the SQL Server Agent > Error Logs node in SSMS:

They're stored as ERRORLOG and AGENTLOG rolling log files in the "Log" subfolder in your SQL Server's installation path.

You can customize the SQL Server Error Log retention settings by right-clicking on the "SQL Server Logs" node and select "Configure":

Unfortunately, there's no similar configuration for the SQL Agent Error Log at the time of this writing.

You can manually cycle these logs using the stored procedures below:

• master.sys.sp_cycle_errorlog

• msdb.dbo.sp_cycle_agent_errorlog

But of course, it's best to automate this operation using scheduled jobs.

Please check out Guy Glantser's blog post about SQL Server Error Log Management for more details.

DB Mail History

This one is often overlooked by most people, but it's something that can also fill up your MSDB if you're using DB Mail frequently.

The system tables sysmail_mailitems, sysmail_attachments, and sysmail_log hold the full contents and details of each DB Mail sent, and they should be cleaned up just as well as the other SQL Agent historical tables.

To cleanup these tables, you should use the following stored procedures:

• msdb.dbo.sysmail_delete_mailitems_sp

• msdb.dbo.sysmail_delete_log_sp

You can create a scheduled job that would execute something like the following T-SQL commands:

DECLARE @beforeDate datetime  
SET @beforeDate = DATEADD(dd, -30, GETDATE());
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @beforeDate;  
GO
DECLARE @beforeDate datetime  
SET @beforeDate = DATEADD(dd, -90, GETDATE());
EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @beforeDate;  
GO

Full-Text Crawl Logs

This is another little-known possible pain point, and not directly related to MSDB... But it's important to know that SQL Server also stores "crawl logs" for its Full-Text Catalogs. These files are also stored in the "Log" folder of your SQL Server's installation path, and they have the following naming scheme:

SQLFT<DatabaseID><FullTextCatalogID>.LOG[<n>]

(more details in Microsoft Docs)

In order to not get too bloated, these too should be cycled similarly to SQL Server's Error Logs. This can be done using the undocumented stored procedure sp_fulltext_recycle_crawl_log.

This, however, must be done on a per-database basis, so it's a bit more complicated to implement.

No worries, though, you can use this T-SQL script from our very own Madeira Toolbox!

Check out Jonathan Kehayias's blog post for more details.

Conclusion

There's a bunch of different types of historical data collected by our SQL Server's ongoing maintenance operations. It's important to clean them up of old data, as to not let them bloat our database server. However, many of them are not being taken care of automatically or out-of-the-box, and would require us to take that extra step on our own.

In this blog post, I tried to cover all types of historical logs, files, and tables used by SQL Server. But turns out that there is even more stuff to cover!

Click here to head over to Keep Your MSDB Clean - Part 2