Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Size of msdb database

Author  Topic 

bpsintl
Posting Yak Master

132 Posts
Posted - 2010-02-18 : 10:18:25
What sort of size for the msdb database is "normal"? Mine is larger than any other user database.

I run the delete backup history function every day

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2010-02-18 : 11:38:24
you have a lot of SSIS packages stored in msdb?

No one can tell you "normal" size. It depends on what you're doing.

Might double-check that no one is creating user objects in msdb though
Go to Top of Page

bpsintl
Posting Yak Master

132 Posts
Posted - 2010-02-18 : 12:10:04
SSIS - not that I know of, how do I check?

I am the only person who has access
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-18 : 12:17:07
"I run the delete backup history function every day"

This should give you the number of Backups in the History that MSDB is holding - is it a "reasonable" number?

SELECT COUNT(*)
FROM	msdb.dbo.backupset

Lots of restores?

SELECT COUNT(*)
FROM	msdb.dbo.restorehistory

MSDB Dataabse once grew big (e.g. no housekeeping) and that cause has been fixed but the database not Shrunk since then?

USE msdb
GO
EXEC sp_spaceused

failing that run a script to see the size of the various objects to determine which is big / has many rows:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762 (Size script)
Go to Top of Page

bpsintl
Posting Yak Master

132 Posts
Posted - 2010-02-18 : 12:34:43
SELECT COUNT(*)
FROM msdb.dbo.backupset

the above returns 362

SELECT COUNT(*)
FROM msdb.dbo.restorehistory

the above returns 1

USE msdb
GO
EXEC sp_spaceused

returns 49.56Mb
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-18 : 12:38:56
EXEC sp_spaceused normally returns a bit more than that - the "reserved" size, data, index, and Unused.

If the reserved size is <= 100MB (i.e. <= 100,000 KB) then I would not worry about it
Go to Top of Page

bpsintl
Posting Yak Master

132 Posts
Posted - 2010-02-18 : 13:18:28
When I run that again I get

reserved = 33512kb
data = 28736kb
index size = 3328kb
unused = 1448kb
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2010-02-18 : 13:29:38
that's not all that big. your user DBs must be pretty small.

quick way to check how many SSIS packages: select * from sysssispackages
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-18 : 13:39:15
Yup ... 33MB. Its as optimised as it is likely to be possible to make it - don't worry about it until it gets to 100MB
Go to Top of Page

bpsintl
Posting Yak Master

132 Posts
Posted - 2010-02-18 : 15:21:09
LOL, yes my db's are tiny in comparison. I think my largest one is only 30Mb or so.

If it does get to 100Mb, what can you do then? Why would that be bad?
Go to Top of Page

bpsintl
Posting Yak Master

132 Posts
Posted - 2010-02-18 : 15:24:25
By the way, when I run select * from sysssispackages I get 8 rows back
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-19 : 01:40:53
"Why would that be bad?"

It would suggest that deletion of stale data had failed.

Or it might suggest that you have dramatically increased the frequency of backups, or something else like that, in which case you would forget about it again until it got to, say, 200 MB - but its important to have a baseline for comparison so that you know if there is a dramatic change
Go to Top of Page

bpsintl
Posting Yak Master

132 Posts
Posted - 2010-02-19 : 03:52:46
I do full backups twice a day. At the moment all db's are in simple mode
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-19 : 05:04:20
If you change to Full Recovery Model, in the future, and backup the Log every, say, 15 minutes that will obviously increase the number of backups significantly.

For example, we have 100 databases, or so, on our servers. With daily full backups and Log backups every 15 minutes that is nearly 10,000 backups a day, plus we retain the backup history for 90 days, so nearly 1,000,000 backup records recorded. That takes a bit more than 100MB unfortunately!
Go to Top of Page

bpsintl
Posting Yak Master

132 Posts
Posted - 2010-02-19 : 05:18:03
Phew! So how big is your msdb then?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-19 : 05:27:30
Generally around 300-500MB ...
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site