2005 msdb..backupset table still large after data purge

Question

Post reply

2005 msdb..backupset table still large after data purge

Staggerlee

SSCertifiable

Points: 5272
More actions
August 26, 2014 at 2:10 am

#295758

Hi all,
So I've found a instance with a nice 5gb msdb, most of it was from backupset. After running a sp_delete_backuphistory in small chunks to get it cleared to only the last 90 days, the table size is still very large, Ive heard this is a known problem in 2005, but i cant find any reference to it on the web. Any one know how to recover the space?
Regards
s

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

srikantmeister SSChasing Mays Points: 657 More actions · Answer 1

srikantmeister

SSChasing Mays

Points: 657

September 4, 2014 at 1:22 am

#1743103

what is recovery mode for this system DB

srikantmeister SSChasing Mays Points: 657 More actions · Answer 2

Also find a good link on the same, refer below

http://www.mssqltips.com/sqlservertip/1461/analyze-and-correct-a-large-sql-server-msdb-database/

and

http://www.sqlservergeeks.com/sql-server-controlling-the-growth-of-msdb-database-caused-by-database-mail/

Staggerlee SSCertifiable Points: 5272 More actions · Answer 3

Hi,

thanks for the reply,

msdb is in simple recovery mode,

thanks for the link, its one i used to help build out our msdb clean up jobs. The problem seems to be that space isnt being released when i delete rows from the backup tables. a team member here says its a known issue, that you resolve with truncating all the backup tables, but i cant find anything saying that in the interwebs.

srikantmeister SSChasing Mays Points: 657 More actions · Answer 4

if you have cleaned up and not released, have you truncated the log and shrink the database?

have you ran checkpoint manually??

srikantmeister SSChasing Mays Points: 657 More actions · Answer 5

Also can you segregate now where the space is lying currently?

I mean for example out of 5 GB, how much by tables, how much by others, how much unused/unallocated...n all