asked
sql server 2008 mssdb database 1 GB
hi team, i can see msdb database grown upto 1 GB, any reason, shrink not working , weekly job for re indexing
I would guess you have some object created in there by accident. Is it the default database for any of your users? It *may* be the details of job activity and backups but to make 1GB that's an awful lot of history information.
Congrats on 20K, that is a major accomplishment and kudos to @oleg for putting you over the mark.
@BradleySQL - Heh, thanks. It comes mostly from having been around here a long time ;)
MSDB can grow for a number of reasons. The number of jobs you have, the amount of backup history retained, etc. By any chances are you log shipping, mirroring, or taking tlog backups on a routine basis? Those will increase the amount of data in your backupset tables in MSDB. Have a look at the size of your tables in MSDB to determine where your growth is as well as the amount of free space in MSDB. It could be your MSDB was set to 100 MB and to auto grow by 1024 MB. Once it needed to grow it expanded the size of the file and is mostly free space.
You could run `sp_spaceused` to see if it's free space, or if you've got heavy index usage... (give it a table name to check a particular table). If you want to just see which tables have most rows of data, try: select object_name(id), rowcnt from sysindexes where indid in (0,1) and id in (select id from sysobjects where type = 'u') order by rowcnt desc (Not the best way of doing it, but it'll work as a quick & dirty query...)
I had an MSDB that grew like crazy because of order reciepts including a logotype as attachment. Database Mail logs copies of mails and attachments to MSDB. If that's your case - look at **sysmail_delete_mailitems_sp** to purge old mails and their attachments from MSDB.
Great suggestion Magnus..it will delete the data from table dbo.sysmail_mailitems so it will clear some space in database...is it need to be run in regular intrvals so that it will clear the table in periodical basis...or is there any thing which puges automaically from this table?
I've setup a job which runs nightly, doing: DECLARE @d datetime SET @d = DATEADD(Month, -1, CURRENT_TIMESTAMP) EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @d That will remove mailitems older than one month. Just change the DATEADD-call to fit whatever fits your needs.
Another option might be, and I only just found this out while doing some house-keeping on my dev server, that the Database Tuning Advisor has stored a lot of information in your MSDB. I have a dev server that has 17 user tables all with the prefix of "DTA_", in my case there only a few MB and its no concern to me. If you are affect here then you may want to investigate purging these tables with something mentioned here
http://support.microsoft.com/kb/899634 , if it describes your situation accurately and here
http://feodorgeorgiev.com/blog/2010/06/dta-what-is-making-my-msdb-grow/ also gives some options. If this is a production server and this is what has happened then you need to avoid it occurring in the future by using the DTA against only test or dev servers
