Keeping MSDB Clean

SQL Server Best Practices
SQL Server Best Practices
I have a bunch of monitoring routines running against my SQL Servers. One of these checks available disk space on drives. Today I was surprised to see that one of my SQL system drives was looking pretty full. This is unusual to say the least because I dedicate a drive to the system (master/model/msdb) databases. Upon connecting to the server I discovered that MSDB had blown up to over 12GB in size.

Checking For Large Tables

I used the following query to look for large tables in MSDB (this query is completely based upon running profiler against SQL Server while running a Disk Usage By Table report.

SELECT TOP 10'.' AS [tablename],
    a1.rows as row_count,
    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved 
        SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
        SUM (ps.reserved_page_count) AS reserved
        FROM sys.dm_db_partition_stats ps
        GROUP BY ps.object_id) AS a1
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used
        FROM sys.dm_db_partition_stats ps
        INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
        WHERE it.internal_type IN (202,204)
        GROUP BY it.parent_id) AS a4 
        ON (a4.parent_id = a1.object_id)
    sys.all_objects a2  
        ON ( a1.object_id = a2.object_id ) 
    sys.schemas a3 
        ON (a2.schema_id = a3.schema_id)
    a2.type <> N'S' and a2.type <> N'IT'
    reserved desc,[tablename]

The results were a little shocking to say the least.

  • sysmail_mailitems contained 717,878 rows and was over 9GB in size
  • backupfile/backupmediafamily/backupmediaset/backupset combined had over 8.6million rows and totalled over 2.5GB in their space usage

Forgetting To Clean Up

MSDB is something frequently forgotten. Cleaning up job history is nice and easy, it’s just a setting in the SQL Agent. Cleaning up your sent emails and backup records is not as straightforward. Why Microsoft hasn’t included this I don’t know, especially given that they have provided stored procedures to perform the cleanup of this data.

My server has been in service since March 2009. That’s a lot of backups and a lot of emails sent. I needed to get a handle on cleaning this up and then implementing a process to ensure that these tables didn’t get out of control again.

Deleting Old Mail Items

Microsoft have provided the procedure sysmail_delete_mailitems_sp to perform cleanup of the sysmail_mailitems table. Two parameters can get passed in:

  • @sent_before – datetime – used to delete mail items sent prior to a certain date
  • @sent_status – varchar(8) – used to force deletion of only a certain sent status

In this instance I was not concerned with what items were going to be deleted, I just wanted to get rid of everything older than 14 days.

Here’s a bad way to do this:

DECLARE @DATE DATETIME = dateadd(d, -14, getdate())

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DATE

Actually let me clarify, this isn’t a bad way to do this if you don’t have 2 years and 9GB of data to delete. In fact this is the script I implemented in a job to continue to keep the table size down. Running this against such a large amount of data just blew up the transaction log, filled the drive and then forced a rollback.

Going a slightly smarter route I performed the deletion in small batches (5 days worth of email at a time) using the following:

DECLARE @maildate DATE = '2009-06-15'
WHILE @maildate < DATEADD(d, -14, GETDATE())
        EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @maildate
        SELECT @maildate = DATEADD(d, 5, @maildate)

This took a little while however the transaction log stayed small and it completed without incident.

Deleting Backup History

Again Microsoft do a kindness to help cleanup old data. This time it’s the procedure sp_delete_backuphistory which we pass

  • @oldest_date – datetime – the oldest backup history record we want to keep

Having learned my lesson from deleting the old mail items I again attempted to perform the delete in batches using:

Continue reading on

(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *