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
sysmail_mailitems - IN_ROW_DATA
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.
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
/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
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
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)
I know this is old post, but just experienced this problem today so I thought I would share.
In my case I found that the tempdb has grown to similar size too, so I restarted the affected database instance to force recreation of tempdb, and now I have space to execute the 2 SP to purge mails.
Just my 2 cents.