question

ramesh 1 avatar image
ramesh 1 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
sql-server-2008msdb
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Fatherjack - and very little to do with the contributions during that long time, right?! ;o)
1 Like 1 ·
Congrats on 20K, that is a major accomplishment and kudos to @oleg for putting you over the mark.
0 Likes 0 ·
@BradleySQL - Heh, thanks. It comes mostly from having been around here a long time ;)
0 Likes 0 ·
Tim avatar image
Tim answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

all good points, +1
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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...)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·
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.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.