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]
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()) 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.