MSDB Huge, and Rapid Growth !!

  • For some reason my MSDB database has grown huge in just a few days.

    June 1 backup is 3.5 gig

    June 2 backup is 6.5 gig

    June 3 backup is 13 gig

    June 4 backup is 25.5 gig !!!

    By running the query below, I see that no tables have a huge amount of records, but the table "sysmail_mailitems" has reserved_page_count = 3903216 and is now about 30 Gig !! But there are only 6,000 rows.

    What is going on here ? I am not familiar with this table and am off to read up on it now.

    SELECT o.name, ddps.row_count , reserved_page_count as 'Resrv Page', (used_page_count * 8)/1024 as Mbytes, *

    FROM sys.indexes AS i

    INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID

    INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID

    AND i.index_id = ddps.index_id

    ORDER BY ddps.row_count desc

  • I think I sort of figured it out.

    I have a job that captures blocking information and sends an alert and writes the blocking information to a table. Currently there's a job causing lots of blocking. I don't have email configured on this server, but I think the process is still running the sp_send_dbmail. I guess that dumps data into sysmail_mailitems, including all the code from the blocking process.

    I am deleting all the records in sysmail_mailitems with me as a recipient, and see the size going down.

  • Would it help if I showed you how to send emails without setting up email on a server?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (6/4/2013)


    Would it help if I showed you how to send emails without setting up email on a server?

    Yes, thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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