Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Analyze and Correct a large SQL Server MSDB Database


By:   |   Last Updated: 2008-03-24   |   Comments (5)   |   Related Tips: 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:

  • Depending on the application, purpose and object dependencies coordinate with the application development team to move the objects with DTS\SSIS\T-SQL scripts to the new database then either drop or rename the existing objects to make sure they are not used in the future.

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:
  • dbo.sysdtscategories
  • dbo.sysdtspackagelog
  • dbo.sysdtspackages
  • dbo.sysdtssteplog
  • dbo.sysdtstasklog
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:
  • dbo.sysdtscategories
  • dbo.sysdtslog90
  • dbo.sysdtspackagefolders90
  • dbo.sysdtspackagelog
  • dbo.sysdtspackages
  • dbo.sysdtspackages90
  • dbo.sysdtssteplog
  • dbo.sysdtstasklog
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 - In Management Studio navigate to Root | SQL Server instance |  SQL Server Agent | right click on SQL Server Agent | select Properties | History page
  • SQL Server 2000 - In Enterprise Manager navigate to Root | SQL Server instance |  Management folder | SQL Server Agent | right click on SQL Server Agent | select Properties | Job System tab

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.


Last Updated: 2008-03-24


next webcast button


next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, April 15, 2017 - 10:58:19 PM - Hien Back To Top

Hi all,

 

Please help to share expert solutions to resolve the issue below

I can see msdb data file and log file so big now. I have ran stored procedures to delete DBO.Sysmail_attachments, DBO.Sysmail_send_retries, Sysmail_delete_mailitems_sp, Sysmail_delete_log_sp, sp_purge_jobhistory. All scripts ran succesfully and deleted data. But I still see data and log msdb file so big (not descreased so much). What can I do now? Can I use truncate command for this database?

 

Thanks and regards,

Hien


Monday, January 13, 2014 - 12:32:17 PM - Jeremy Kadlec Back To Top

MSSQL DBA,

Looks like you have a third party product installed from - http://lakeside-sql.software.informer.com/.

I would look into the installation, retention policy and contact their support with any questions.

Thank you,
Jeremy Kadlec
MSSQLTips.com Community Co-Leader


Sunday, January 12, 2014 - 9:57:48 PM - MSSQL DBA Back To Top

HI,

 

lakeSideWaitslogger._lakerSide_DbTools_waitlogger_waitDBlog table is showing 10.5233.613 records.

what is used for it ?

 

Regards,

MSSQDBA


Tuesday, July 19, 2011 - 4:27:12 PM - Jeremy Kadlec Back To Top

BPODFW,

Thank you for sharing your experience.

Thank you,
Jeremy Kadlec


Tuesday, July 19, 2011 - 4:09:50 PM - BPODFW Back To Top

I had the issue wih old backups creating large MSDB table. Just deleting the backup history was not going well until I found an article that helped . It suggested that creating the following indexes on the backup set table. Speed was dramatic. Hope it helps

USE MSDB
GO
create index BACKUPSET_I01 ON BACKUPSET (MEDIA_SET_ID)
GO
create index BACKUPSET_I02 ON BACKUPSET (BACKUP_SET_ID,MEDIA_SET_ID)
GO

Finish with

USE MSDB

EXEC sp_delete_backuphistory 'MM/DD/YYYY';

where the MM/DD/YYYY is the date to delete up to.

 

 


Learn more about SQL Server tools