MSDB Huge, and Rapid Growth !!

Question

MSDB Huge, and Rapid Growth !!

homebrew01

SSC Guru

Points: 55548
More actions
June 4, 2013 at 8:05 am

#274600

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

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

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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)

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

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