Keeping MSDB Clean

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
    a3.name+'.'+a2.name AS [tablename],
    a1.rows as row_count,
    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved 
FROM
    (SELECT 
        ps.object_id,
        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
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id,
        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)
INNER JOIN 
    sys.all_objects a2  
        ON ( a1.object_id = a2.object_id ) 
INNER JOIN 
    sys.schemas a3 
        ON (a2.schema_id = a3.schema_id)
WHERE 
    a2.type <> N'S' and a2.type <> N'IT'
ORDER BY 
    reserved desc,[tablename]

SELECT TOP 10

a3.name+'.'+a2.name AS [tablename],

a1.rows as row_count,

(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved

FROM

(SELECT

ps.object_id,

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

LEFT OUTER JOIN

(SELECT

it.parent_id,

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)

INNER JOIN

sys.all_objects a2

ON ( a1.object_id = a2.object_id )

INNER JOIN

sys.schemas a3

ON (a2.schema_id = a3.schema_id)

WHERE

a2.type <> N'S' and a2.type <> N'IT'

ORDER BY

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

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())
    BEGIN
        EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @maildate
        SELECT @maildate = DATEADD(d, 5, @maildate)
    END

DECLARE @maildate DATE = '2009-06-15'

WHILE @maildate < DATEADD(d, -14, GETDATE())

BEGIN

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @maildate

SELECT @maildate = DATEADD(d, 5, @maildate)

END

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 SirSQL.net.