msdb db is huge - help 1

[icemel]

Hi,

On one of our servers, I noticed that the msdb database has grown to 5 GB in size!

sp_spaceused yields:

NAME DB SIZE UNALLOCATED SPACE
msdb 5409.50 MB 1.12 MB

RESERVED DATA INDEX SIZE UNUSED
5533064 KB 5199712 KB 331024 KB 2328 KB

So it appears that the data is in fact, using up about 5 GB of space. I just don't know how this can be??

Can this be shrunk down??

The msdb database on our other servers is only about 350 MB, not 5 GB!

Help.

Thanks

 

[yelworcm]

How many DTS packages do you have on the server? You can probably start by removing old versions of these DTS packages. DTS packages are not overwritten when you save them. A brand new version is created, so if you are like me, and save often, you will likely have a lot of versions kicking around.

If this is not the case, run sp_spaceused backupset in msdb, and post the results.

 

[icemel]

Hi, further investigaion yielded that the "backupset" table in the msdb database has over 9.6 million rows!!

Obviously, there is a problem here.

Can one safely delete these rows prior to the most recent backupset?

I would assume so.

 

[yelworcm]

There are a couple of foreign key relationships to deal with (backupfile, and restorehistory), so you will probably have to deal with these tables as well, but other than Enterprise Manager not being able to suggest a backup from 1998 to restore from, I doubt you will have problems. A full script might be posted over on

http://www.sqlteam.com.

 

[mrdenny]

There are several things that are stored in the msdb that can grow.

The backup history.
DTS Packages
Job History (This will hold only a certain number of rows, but some people such as myself increase the number to get more history.)

If the system has been online for many years and you do frequent backups then the backup history can get very large and will need some pruning.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[icemel]

A histogram of the data in the backupset table revealed that the data has only been collecting in this table since January 2005, only 10 months.

However, there were several days over this 10-month period where over half a millions rows were added to this table in a single day.

I'm not sure how this is happening... or how to keep it from happening?

 

[icemel]

Hi yelworcm,

I think this script does what you're describing:

sp_delete_backuphistory

 

[yelworcm]

It may take a while for the Microsoft supplied version to run, as it is cursor based. Try isp_DeleteBackupHistory as seen in this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201