Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MSDB Huge, and Rapid Growth !! Expand / Collapse
Author
Message
Posted Tuesday, June 4, 2013 8:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:08 AM
Points: 2,827, Visits: 8,480
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




Post #1459791
Posted Tuesday, June 4, 2013 12:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:08 AM
Points: 2,827, Visits: 8,480
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.



Post #1459906
Posted Tuesday, June 4, 2013 4:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 35,261, Visits: 31,741
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1459988
Posted Wednesday, June 5, 2013 6:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:08 AM
Points: 2,827, Visits: 8,480
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



Post #1460202
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse