Clearing out msdb.dbo.sysmail_mailitems...

  • jasona.work

    SSC-Forever

    Points: 49994

    OK, so we (I) finally noticed that the MSDB backup was rather large. In looking into the problem I found that the table dbo.sysmail_mailitems is taking about about 140,000,000KB (133GB if you prefer) It got this large for a couple reasons:

    1. When we set up Database Mail, we didn't realize it would store the messages here...

    2. Our previous backup method was more "automated" so we never noticed the backup getting as large as it did.

    3. OK, yes, I also never thought to test restore MSDB... :Whistling:

    So, I tried clearing it out using the stored procedure "sysmail_delete_mailitems" which did not help. Even setting it to delete a relatively small date range (@sent_before,) it runs so long we need to cancel it.

    I'm working on a copy of the DB now, and I thought I'd try just truncating the table (we don't really need to store all these old messages,) but that failed due to some foreign key constraints. So I'm coming to you folks for help. What's going to be the best way to clear this thing out?

    Can I do a "delete from..." to blow out the table? Or is this going to run into the problem of the FKs also? Will a straight "delete from..." take a long time as well (in which case I could put a "where" clause on the end, and just keep running it, blowing out a handful of records every time...)

    The DB *IS* in Simple Recovery, although based on what I've read I would expect the LDF to grow quite a bit during a delete operation. That I could then control by backing up the DB and checkpointing, correct? Or would I need to also do a "DBCC shrinkfile" on it?

    Thanks,

    Jason A.

  • Lowell

    SSC Guru

    Points: 323463

    jason if you sp_helptext sysmail_delete_mailitems_sp in the msdb database,

    you'll see it's using a catch-all query to delete fromt eht able, and only one table;

    after a bunch of error checking, this is the core of the proc:

    DELETE FROM msdb.dbo.sysmail_allitems

    WHERE

    ((@sent_before IS NULL) OR ( send_request_date < @sent_before))

    AND ((@sent_status IS NULL) OR (sent_status = @sent_status))

    i have this saved in my snippets for deleting old mail jobs, which deletes from two tables in msdb if you peek at the proc code.

    USE MSDB;

    GO

    DECLARE @DeleteBeforeDate DateTime

    SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())

    EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate

    EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate

    so you could do a simple DELETE:

    DELETE FROM msdb.dbo.sysmail_allitems

    DELETE FROM msdb.dbo.sysmail_log

    as a proof of concept, i just did exactly that on my dev machine; cleared out 38K rows of stuff. no foreign key errors, just clean deletes.

    hope that helps!

    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!

  • jasona.work

    SSC-Forever

    Points: 49994

    Lowell I'll give those a try once my re-restore of the DB on my test box completes...

    It only takes about an hour-and-a-half or so to restore...

    😉

    Thanks!

    Jason

  • durai nagarajan

    SSCoach

    Points: 15719

    Hello,

    Is there a way to delete it automatically?

    Regards
    Durai Nagarajan

  • Bhuvnesh

    SSC Guru

    Points: 59344

    durai nagarajan (10/18/2012)


    Is there a way to delete it automatically?

    Scehdule a JOB 😀

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • durai nagarajan

    SSCoach

    Points: 15719

    funny and thanks but is there no other option?

    Regards
    Durai Nagarajan

  • jasona.work

    SSC-Forever

    Points: 49994

    While it was a bit of snarky reply, it's also true. If you want to automate something like this, it's going to have to be scheduled somehow. Either using SQL Agent Jobs, Windows Task Scheduler and batch files, or a reminder on someone's calendar.

    Jason

  • Lowell

    SSC Guru

    Points: 323463

    the scriptlet i posted is what i've put in a scheduled job before;

    just letting it run once a day deletes anything older than 30 days;

    that's what would be my simple sugestion to keep the data under control, especially if you have a fairly busy usage of database mail.

    DECLARE @DeleteBeforeDate DateTime

    SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())

    EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate

    EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate

    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!

  • jasona.work

    SSC-Forever

    Points: 49994

    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.

  • Lowell

    SSC Guru

    Points: 323463

    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!

  • jasona.work

    SSC-Forever

    Points: 49994

    Lowell,

    I actually just stole your code, pretty much word for word. Below is the Agent Job step 1 I'm using:

    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

    Then step 2:

    use msdb

    go

    dbcc shrinkdatabase(N'MSDB')

    go

    USE [msdb]

    GO

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

    GO

    As for the rest of the space, it's still in sysmail_mailitems and I suspect the reason for it is the body column. The manager who uses DBMail to send out stuff, is sending some HTML e-mails, so I suspect those are taking up a good bit of space in the table. Thankfully, the server isn't tight on disk space, so I can live with a 23-24GB DB.

    Once more, thanks!

    Jason

  • Summer90

    SSC-Dedicated

    Points: 32844

    If you have a massive amount of rows you are deleting rebuild the indexes after that. That will free up your space.

  • dhechle

    SSC Eights!

    Points: 862

    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_mailitems 849 781644 781526 255 6106 6105 1

    sysmail_mailitems 42 804902 804811 15 6288 6287 0

    sysmail_mailitems 42 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

  • Summer90

    SSC-Dedicated

    Points: 32844

    There are several reasons for it. Typically the rows that are active are at the end of the file so it isn't shrinking. Rebuilding indexes on the tables will move the data to a different part of the file and then you should be able to shrink.

  • Lowell

    SSC Guru

    Points: 323463

    Markus (11/8/2012)


    There are several reasons for it. Typically the rows that are active are at the end of the file so it isn't shrinking. Rebuilding indexes on the tables will move the data to a different part of the file and then you should be able to shrink.

    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

    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!

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply