apitech
asked on
Huge msdb database
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.
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.
ASKER
When I run select * from dbo.sysfiles, the size of the MSDBData file is 957248, while the size of the MSDBLog file is only 288. Running select * from sys.allocation_units brought up an error saying that that is an invalid table.
Anyway, what can I do to fix this? There is over 40GB of space on the C drive where this database sits and over 200GB of space on the backup drive. But, I want to reduce the database's size before this becomes an issue.
Anyway, what can I do to fix this? There is over 40GB of space on the C drive where this database sits and over 200GB of space on the backup drive. But, I want to reduce the database's size before this becomes an issue.
So, based on the above it appears that the size of actual db is pretty less. How about the size of virtual memory allocated? Do you have N number of backup images of the database? May be something to clean up?
More than likely the Job History table is the cause, unfortunately until you have identified the actual table(s) that are the problem there is not much we can go by.
ASKER
How do I identify the problem tables?
ASKER
How do I identify the problem tables?
Hi,
In Enterprise Manager, right-click on SQL Server Agent and select properties. On the job tab, check that Limit size of job history log is selected.
The entries for Maximum Job History size (rows) is quite small.
HTH
David
In Enterprise Manager, right-click on SQL Server Agent and select properties. On the job tab, check that Limit size of job history log is selected.
The entries for Maximum Job History size (rows) is quite small.
HTH
David
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, for this information on finding which tables are the large ones.
Please let me know if this is the correct syntax on getting rid of the tables:
drop table 'tables name'
I just want to make sure that I'm doing this correctly. Also, can this be done while people are in the system? Is this something that will take days to run? Will backup jobs still run, while this runs?
Please let me know if this is the correct syntax on getting rid of the tables:
drop table 'tables name'
I just want to make sure that I'm doing this correctly. Also, can this be done while people are in the system? Is this something that will take days to run? Will backup jobs still run, while this runs?
Hi,
dropping a table is very fast. I'd be surprised if it takes more than minutes to run.
I don't think you can run a backup during this. But since its only for minutes so that shouldn't be a problem.
HTH
David
dropping a table is very fast. I'd be surprised if it takes more than minutes to run.
I don't think you can run a backup during this. But since its only for minutes so that shouldn't be a problem.
HTH
David
Yes, you cannot run backup jobs while issuing DCL commands that modify/create the structures.
Did you check the size of LOGs? That could be one of the reasons for growing size.
Other reason could be inefficient space allocation to DB, Tables.
Also, check "select * from dbo.sysfiles", "select * from sys.allocation_units" to check the sizes used and growth set.
Thanks,
Sam