The msdb database

The msdb database

msdb file size

The msdb database holds data about jobs, maintenance, alerts, history and backups. It needs to be backed up, and can be restored just like any user database. Its size can become very large if left unattended.

The following query shows the top 10 msdb table sizes:

USE msdb;
go
SELECT TOP 10 SERVERNAME=@@SERVERNAME,DB_NAME=DB_NAME(),TABLE_NAME=OBJECT_NAME(I.ID),INDEX_NAME=I.NAME,INDID,USED, ROWS, SIZE_N_MB = ROUND((USED*8.0/1024.0),2),
ROWMODCTR,STATISTICDT=STATS_DATE(I.ID,INDID)
FROM SYSINDEXES I, SYSOBJECTS O
WHERE I.ID = O.ID
AND INDID IN ( 0,1)
AND XTYPE = 'U'
ORDER BY USED DESC

For cleaning up your MSDB tables prefixed with DTA_ simply open up the Database Engine Tuning Advisor and delete the old sessions.

msdb backup/restore history tables

The increase in size is often due to the backup/restore history getting out of hand. Run the following to see when backup/restore history goes back to:

SELECT TOP 1 backup_start_date
FROM msdb.dbo.backupset WITH (NOLOCK)
ORDER BY backup_set_id ASC
GO

SELECT COUNT(*) AS 'TotalRecords',
MIN(restore_date) AS 'MinDate',
MAX(restore_date) AS 'MaxDate'
FROM msdb.dbo.restorehistory
GO

Before trying to clear anything, you should create some additional indexes to drastically speed up the time it takes the procedures that clear records to execute:

use msdb;
go

create nonclustered index IX_backupset_media_set_id on dbo.backupset (media_set_id);
create nonclustered index IX_restorefile_restore_history_id on dbo.restorefile (restore_history_id);
create nonclustered index IX_restorefile_restore_history_id on dbo.restorefilegroup (restore_history_id);

The following will let you see stats for the clean-up operation and delete entries older than 90 days:

SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO

USE MSDB
DECLARE @backup_date DATETIME
BEGIN
set @backup_date=(select dateadd (dd, -90, getDate()))
EXEC SP_DELETE_BACKUPHISTORY @backup_date
END

The log file for msdb may grow hugely after deleting unwanted data. The following will show the space available:

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

DBCC SQLPERF(LOGSPACE);
GO

And the following will try to reduce the log size to 80MB, if space allows;

DBCC SHRINKFILE (msdbLog, 80);
GO

More useful tips

A job can be added to a maintenance plan in order to clean up history prior to backup:

The physical backup files are preserved, even if all the history is deleted.