Sorry to hijack this thread but I am trying to clear out the sysmail_mailitems table myself on a SQL 2005 SP4 instance using the sysmail_delete_mailitems_sp procedure and leaving 30 days history. The command I am executing is as follows:

DECLARE @DelDate nvarchar(20) ;

SET @DelDate = DATEADD(d,-30, GETDATE())

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DelDate;

This runs successfully and has removed the rows from the table as expected however I can not shrink the database using the command below. It still thinks that there is over 6 GB of data in the sysmail_mailitems table.

DBCC ShrinkFile (MSDBData, 1536)

GO

DBCC ShrinkFile (MSDBLog, 10)

I have restored a copy of msdb before I ran the procedure and that did the same delete leaving 30 days history and then successfully shrunk.

Below are sizes of the sysmail_mailitems table in msdb after the following scenarios:

1) Before anything is run

2) After running sysmail_delete_mailitems_sp and shrink

3) Restoring a copy of msdb called msdb2 and running sysmail_delete_mailitems_sp and shrink

4) Taking a backup of the table by doing select * into bk_sysmail_mailitems from sysmail_mailitems on the msdb after scenario 2.

ObjectName TotalRows TotalPages UsedPages DataPages TotalSpaceMB UsedSpaceMB DataSpaceMB

sysmail_mailitems849 781644 781526 255 6106 6105 1

sysmail_mailitems42 804902 804811 15 6288 6287 0

sysmail_mailitems42 76362 76337 11 596 596 0

bk_sysmail_mailitems 42 99459 99454 13 777 776 0

It looks like for whatever reason the pages are not releasing but i'm not sure why, I have backed up the database with truncating the log, ran "sp_spaceused @updateusage = true" incase there is anything wrong with the stats, rebuilt the clustered index on the table and nothing has helped.

I have found a few posts similar to this issue but no one seems to have a resolution, do you guys have any ideas?

Thanks in advance.

Dave