By: Jeremy Kadlec | Updated: 2008-03-24 | Comments (5) | Related: More > System Databases
Problem
It has come to my attention that some of our MSDB databases are getting what I would consider large for a system database. Some of our MSDB databases are over 2 GB which is a little perplexing because I know we do not create any user defined objects in that database. Can you give me some insight into the issue? I know we have this issue with both SQL Server 2000 and 2005 instances.
Solution
On a few different SQL Server instances, we have observed MSDB databases over 1 GB. In each of the circumstances, the reason for the size of the MSDB database was different, so each circumstance opened up some new insight into how the MSDB database is used. Let's take a look at how to diagnose the issue in an effort to correct the issue:
|
Backup the MSDB Database on each SQL Server instance |
| Although the MSDB database may be considered large, it may be the case for a legitimate technical or business reason. As such, before making any changes (i.e. move objects, drop objects, rename objects, etc.), it would be wise to backup the MSDB database first for rollback purposes. |
|
Backup Script |
| BACKUP DATABASE MSDB TO DISK = 'C:\MSDBPreliminaryBackup.bak' WITH INIT GO |
|
User Defined Objects |
| If a number of user defined objects (tables, stored procedures, functions, views, etc. not created in MSDB as a portion of the installation or a service pack) exist in the MSDB database, consider finding out why they exist in the database. Then see if the objects can be moved to an existing or new database. If your database administration objects are in the MSDB database consider moving them to a DBA database which can be dedicated to the SQL Server Administration needs.
Since numerous SQL Server system tables (Integration Services, Backup and Restore, Log Shipping, etc.) exist in the MSDB database the best reference for all of these tables is System Tables (Transact-SQL). This SQL Server 2005 Books Online article is a good point of reference to verify if the table is an MSDB system table or not. In addition, this article outlines the functionality and table definition for each table. From a resolution perspective, consider the following:
|
|
Large MSDB Tables |
| From a storage perspective, the next item to determine is which tables and indexes are large. Based on those metrics, then additional steps can be taken to determine if the tables can have none, some or all of the data deleted to reduce the size of the MSDB database. Consider executing the analysis script below to begin the process then based on the results determine the next steps. |
|
Analysis Script |
| SELECT object_name(i.object_id) as objectName, i.[name] as indexName, sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages, (sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB, (sum(a.data_pages) * 8) / 1024 as dataSpaceMB FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id GROUP BY i.object_id, i.index_id, i.[name] ORDER BY sum(a.total_pages) DESC, object_name(i.object_id) GO |
| From a resolution perspective, research each of the tables to determine the purpose behind the table. During the analysis it would make sense to review the table for min and max date related data. If for example you have backup records from five years ago that would never be needed, those records could be deleted. However, if you have recent data (i.e. 3 or 6 months of data) then be sure to maintain those records for historical purposes. In addition, if you know your business processes will continue to write to the tables but only recent data is needed (i.e. 3 or 6 months of data) consider building a SQL Server Agent Job to delete the unneeded records on a monthly basis. |
|
DTS Package Related Storage (SQL Server 2000) |
In SQL Server 2000, DTS Packages were primarily stored in the MSDB database as opposed to be stored as a structured storage file or Visual Basic file. As such, DTS Package storage was a typical culprit for a large MSDB database. If the following tables were large from the last query then DTS is one area that may need to be addressed:
|
| To address this issue, the first step would be to review the DTS Packages stored on your SQL Server instance. This can be achieved by using Enterprise Manager and navigating to Root | SQL Server instance | Data Transformation Services | Local Packages. Once you are in panel, review the packages to determine if they are in use or not based on your knowledge of the systems. Be sure to check with your entire team to determine if the packages are in use. If you have perhaps hundreds of packages that you do not think are in use, it is worth talking to your team and renaming the packages before deleting them. If you have monthly, quarterly or semi-annual processes, it would be wise to make sure all of those processes finish before any packages are dropped. |
|
SSIS and DTS Package Storage (SQL Server 2005) |
In SQL Server 2005, the DTS and SSIS Packages can still be stored in the MSDB database or in the file system. As such, it is important to once again review the packages and determine the location for the package. The corresponding tables in SQL Server 2005 are:
|
| Once again review the DTS and SSIS Packages in Management Studio to determine usage. Check with your team to validate the packages are needed and rename before deletion. Keep in mind with packages stored on the file system the excessive storage will probably be in the file system and not in the MSDB database, but your data should confirm that for you. |
|
DTS Package Revision History (SQL Server 2000) |
| Another item to keep in mind is that DTS Packages retain version history as they are saved. So it might be wise to review the revisions and see if packages have perhaps 10, 20 or more revisions. One way to review the DTS Package versions is to navigate to the following path in Enterprise Manager - Root | SQL Server instance | Data Transformation Services | Local Packages. In the Local Packages folder, right click on the package and select the 'Versions...' option then the interface below will appear. |
|
|
| From a resolution perspective, review this information, share it with your team and determine if earlier revisions are needed or not. If for example hundreds of versions of packages exist and only the most recent is needed, purging the older revisions may significantly help your MSDB database size. |
|
Backup System Tables |
| If you have backups issued on a daily basis or more, those records can really add up over months and years. If that was the case, the large tables analysis script from above should have brought that to your attention. If not, issue the analysis script below to see if some of the data can be deleted. |
|
Analysis Script (SQL Server 2000) |
| USE MSDB GO SET NOCOUNT ON GO SELECT COUNT(*) AS 'TotalRecords' FROM dbo.backupfile GO SELECT COUNT(*) AS 'TotalRecords' FROM dbo.backupmediafamily GO SELECT COUNT(*) AS 'TotalRecords' FROM dbo.backupmediaset GO SELECT COUNT(*) AS 'TotalRecords', MIN(backup_start_date) AS 'MinDate', MAX(backup_start_date) AS 'MaxDate' FROM dbo.backupset GO |
|
Analysis Script (SQL Server 2005) |
| USE MSDB GO SET NOCOUNT ON GO SELECT COUNT(*) AS 'TotalRecords' FROM dbo.backupfile GO SELECT COUNT(*) AS 'TotalRecords' FROM dbo.backupfilegroup GO SELECT COUNT(*) AS 'TotalRecords' FROM dbo.backupmediafamily GO SELECT COUNT(*) AS 'TotalRecords' FROM dbo.backupmediaset GO SELECT COUNT(*) AS 'TotalRecords', MIN(backup_start_date) AS 'MinDate', MAX(backup_start_date) AS 'MaxDate' FROM dbo.backupset GO |
| From a resolution perspective, review the records and determine what time frame of data is needed. Depending on business or technical needs, records could be deleted from these tables that are over 3 or 6 months old. |
|
Restore System Tables |
| Just like the backup tables, the restore related tables in MSDB may also have data from months or years that are no longer needed. As such, analyze those tables to see if data can be deleted based on the technical and business needs. |
|
Analysis Script (SQL Server 2000 and 2005) |
| USE MSDB GO SET NOCOUNT ON GO SELECT COUNT(*) AS 'TotalRecords' FROM dbo.restorefile GO SELECT COUNT(*) AS 'TotalRecords' FROM dbo.restorefilegroup GO SELECT COUNT(*) AS 'TotalRecords', MIN(restore_date) AS 'MinDate', MAX(restore_date) AS 'MaxDate' FROM dbo.restorehistory GO |
| From a resolution perspective, review the records and determine what time frame of data is needed. Depending on business or technical needs, records could be deleted from these tables that are over 3 or 6 months old. |
|
Log Shipping Related Tables |
| With the number of log shipping solutions that exist, review the corresponding tables for your solution to determine if any of the records can be deleted. |
|
SQL Server Agent Job History |
As a property of SQL Server Agent, job history is automatically purged so it is typically not the culprit for a large MSDB database unless the default configurations are modified. Here are the instructions to access these interfaces with the corresponding interface shown below:
|
|
SQL Server 2005 |
|
|
|
SQL Server 2000 |
|
|
Next Steps
- If you are concerned some of your MSDB databases on your SQL Server instances are a little large, consider conducting some of the analysis from this tip to determine the root cause, then figure out the best approach to resolve the issue.
- Be sure to backup the MSDB database prior to making any changes in order to rollback and/or recover an individual object.
- Keep in mind some of these processes may not be accessed on a daily, weekly or monthly basis, so be mindful of the time frame for the decision and consider renaming the objects then maintaining them for a specific period of time before final deletion.
- If none of these items have addressed your MSDB issue, please enter your problem and solution in the forums. We would be interested in other issues contributing to the size of a large MSDB database.
- Check out the SQL Server Agent category on MSSQLTips for additional information on how to leverage this SQL Server feature.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2008-03-24
