MSDB dbo.sysmail_mailitems is growing even though I run sysmail_delete_mailitems_sp

Question

I seem to have the same problem as described here http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/58e6d64e-5a68-4cc4-a8fa-835703fe9cb3 and here http://www.sql-server-performance.com/forum/threads/shrink-table-after-plenty-of-deletes.29254/

Currently this is the statistics:

sysmail_mailitems - LOB_DATA 
total_pages: 15659416
used_pages: 15657171
data_pages: 0

sysmail_mailitems - IN_ROW_DATA
total_pages: 306704
used_pages: 273039
data_pages: 271629

dm_db_index_physical_stats returns 270808 page_count for IN_ROW_DATA and 15657139 page_count for LOB_DATA.

I have about 220000 rows in the table. When I do a sum(datalength(body)) I get about 5 GB of data, but the data size of the table (index) is 125 GB.
Only the varchar columns recipients and body contains data. The max size of one body item is 185050. The avarage is 22846.

I have run UPDATEUSSAGE.

I run this script every night:

DECLARE @removedate DATETIME
SET @removedate = dateadd(day, -3, getdate())

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @removedate
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @removedate

So as you can see I only keep three days of history.

My version is:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2772.0 (X64)   Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

I have both database mirroring and service broker activated, but the problem seems to only be with the sysmail_mailitems table.

Answer 1

You should be able to rebuild the clustered index to reclaim the space, with...

USE [msdb]
GO
ALTER INDEX [sysmail_mailitems_id_MustBeUnique] 
ON [dbo].[sysmail_mailitems] 
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Thanks

---

/Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem

Answer 2

That didnt help. The dataSpacedMb became less but the totalSpaceMb and usedSpaceMb did not decrease.

Any other ideas?

Using the code below gives the following numbers

object name: sysmail_mailitems
index Name: sysmail_mailitems_id_MustBeUnique
totalPages: 16188925 
usedPages: 16186544 
dataPages: 7988 
totalSpaceMB: 126475 
usedSpaceMB: 126457 
dataSpaceMB: 62

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)