Thursday, February 12, 2009

Purge MSDB Mail History

use msdb
go


DECLARE @d datetime
select @d = min(last_mod_date) from sysmail_attachments with (nolock)
select @d
set @d = dateadd(dd,1,@d)
select @d

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @d
go

DBCC SHRINKFILE (N'MSDBLog' , 0)
GO
DBCC SHRINKDATABASE(N'msdb' )
GO

/*
select top 10 * from sysmail_attachments with (nolock)
select count(*) from sysmail_attachments with (nolock)
select min(last_mod_date) from sysmail_attachments with (nolock)
select max(last_mod_date) from sysmail_attachments with (nolock)
*/

No comments: