good point Markus!

here's what i think is everything we are talking about here...delete items, check the fragmentation, reindex, and then shrink...I added a script to show the fragmentation of the index on ;

then if you do the dbcc reindex, it'll probably go to zero (it did on mine)

then when you shrink, it would return that space back.

FYI, my mailitems index was only 14% fragmentation before the delete,56% after the delete, and zero after the reindex.

so the deleted rows are still taking up space in the indexes.

use msdb

go

declare @DeleteToDate datetime

set @DeleteToDate = DATEADD(d, -30, getdate())

DELETE FROM msdb.dbo.sysmail_allitems

where sent_date < @DeleteToDate

DELETE FROM msdb.dbo.sysmail_log

where log_date < @DeleteToDate

delete from msdb.dbo.sysmail_mailitems

where sent_date < @DeleteToDate

--just how fragmented is our index after deleting stuff?

--limited to the PK indexes on sysmail_mailitems and sysmail_log

SELECT

ps.database_id,

ps.OBJECT_ID,

ps.index_id,

b.name,

ps.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

INNER JOIN sys.indexes AS b

ON ps.OBJECT_ID = b.OBJECT_ID

AND ps.index_id = b.index_id

WHERE ps.database_id = DB_ID()

and b.name IN('sysmail_mailitems_id_MustBeUnique','sysmail_log_id_MustBeUnique')

--ORDER BY ps.OBJECT_ID

ORDER BY ps.avg_fragmentation_in_percent DESC

--reindex so the empty space is not in the middle of the pages of the index

DBCC DBREINDEX(sysmail_mailitems,sysmail_mailitems_id_MustBeUnique)

DBCC DBREINDEX(sysmail_log,sysmail_log_id_MustBeUnique)

dbcc shrinkdatabase(N'MSDB')

go

USE [msdb]

GO

DBCC SHRINKFILE (N'MSDBData' , 0, TRUNCATEONLY)

GO