dbTalk Databases Forums  

big msdb

microsoft.public.sqlserver.setup microsoft.public.sqlserver.setup


Discuss big msdb in the microsoft.public.sqlserver.setup forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
childofthe1980s
 
Posts: n/a

Default big msdb - 12-17-2009 , 12:25 PM






I have a client that is on SQL Server 2000 Standard with service pack 4.
Although they have very few SQL jobs, their msdb database is huge. It is
over 7.50 GB!

First, why would this database get so big? Secondly what is the cleanest
way to shrink this database?

Normally, I would not be so concerned about this database? But, I stumbled
upon this after discovering that their nightly backup job was unable to run
due to low disk space. I was able to free up a ton of space. So, they are
"good" now. I think, though, that it might be a good idea to reduce the
size of this database and see what can be done to prevent it from getting
that big again.

Reply With Quote
  #2  
Old   
Aaron Bertrand
 
Posts: n/a

Default Re: big msdb - 12-17-2009 , 01:13 PM






Quote:
First, why would this database get so big?
Because they aren't cleaning up backup and job history, and SQL Server Agent
is set to allow history to accumulate forever? There is a setting on Agent
that dictates how much history is allowed to be retained. You can delete
based on age, number of rows overall, number of rows per job, or any
combination.

Quote:
Secondly what is the cleanest
way to shrink this database?
The best thing to do is clean up the existing data. But as several of us
have espoused here for a long time, "shrinking the database" is not really
ideal. Are you going to shrink the database, and then NEVER expect to be
able to re-use that space again? Since you know you are going to keep
collecting job history (just maybe not as much) then shrinking it to its
absolute minimum size is just an exercise in futility. Not only will you
eventually need the space again (so you can't really do anything with it in
the meantime) -- shrink, grow, shrink, grow; sense a needless pattern here?
And it can impact users as well, since they may all be backed up behind one
of these autogrow operations necessitated by the shrink in the first place.

Quote:
Normally, I would not be so concerned about this database? But, I stumbled
upon this after discovering that their nightly backup job was unable to run
due to low disk space. I was able to free up a ton of space. So, they are
"good" now. I think, though, that it might be a good idea to reduce the
size of this database and see what can be done to prevent it from getting
that big again.
If 7.5 GB is getting in the way of anything, perhaps you should move the
database to a different drive. And it is particularly alarming that it is
getting in the way of backups. If they are writing backups to the same disk
as SQL Server runs its data files, then why on earth are they bothering to
take backups at all? The point of a backup is to have a redundant copy
somewhere else in the event of failure. Right now you are "kind of" guarded
against database failure. But what if that hard disk fails? You've lost
your data AND the backup.

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.