One case which affects the size of MSDB, is the backup history. Every time a database backup/restore operation takes place in SQL Server, the relevant information is kept into certain system tables in the MSDB database.
SQL Server stores database backup and restoration information into the following tables that exist in the MSDB database:
Let’s examine an example for better understanding the process.
In this example I am using a database in SQL Server 2008 called “DEMODB2008“. To this end, I performed some backup and restore operations for allowing us to check out the data stored in MSDB.
The following queries retrieve information regarding the backup and restore operations that took place on the specific database:
In the case where the MSDB database is huge you can try removing any unnecessary backup and restore history logs.
Instead of manually deleting these logs from the above tables, you can use a SQL Server system stored procedure instead.
This stored procedure is called “sp_delete_backuphistory” and exists in the MSDB system database.
The usage for the above stored procedure is:
sp_delete_backuphistory [ @oldest_date = ] ‘oldest_date’
The above command will clean up the backup and restore history logs up to 01/01/2009.
For more information you can visit SQL Server Books Online.
Recommended eBooks on SQL Server: