Home Forums SQL Server 2005 Administering Clearing out msdb.dbo.sysmail_mailitems... RE: Clearing out msdb.dbo.sysmail_mailitems...

  • October 22, 2012 at 9:54 am

    #1551484

    jasona.work (10/22/2012)

    My thanks to Lowell!

    Using your Delete commands, plus adding one to delete from dbo.sysmail_mailitems, the DB is down to about 23GB from 133GB!

    Set up an Agent job to run once a week and keep the last 30 days of records in all three tables, so I shouldn't have to worry about that one again...

    Once more, thank you!

    Jason A.

    Glad to hear you've got it handled, Jason!

    Maybe post your final solution so others can benefit from your research?

    i think the other peice of the puzzle is deleting old job histories, right? i think the view is sysjobhistory

    how big is your table right now? is it most of that remaining gigs of space?

    exec sp_spaceused 'msdb.dbo.sysjobhistory'

    again, from my snippets for maint of msdb, i have this:

    DECLARE @oldestdate DATETIME

    SET @oldest = GETDATE() - 30

    EXEC SP_PURGE_JOBHISTORY @oldest_date = @oldestdate

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!