Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

2005 msdb..backupset table still large after data purge Expand / Collapse
Author
Message
Posted Tuesday, August 26, 2014 2:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 PM
Points: 319, Visits: 805
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
Post #1607369
Posted Thursday, September 4, 2014 1:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:40 AM
Points: 124, Visits: 187
what is recovery mode for this system DB
Post #1610389
Posted Thursday, September 4, 2014 1:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:40 AM
Points: 124, Visits: 187
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/
Post #1610390
Posted Thursday, September 4, 2014 2:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 PM
Points: 319, Visits: 805
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.

Post #1610399
Posted Thursday, September 4, 2014 2:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:40 AM
Points: 124, Visits: 187
if you have cleaned up and not released, have you truncated the log and shrink the database?
have you ran checkpoint manually??
Post #1610404
Posted Thursday, September 4, 2014 2:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:40 AM
Points: 124, Visits: 187
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
Post #1610406
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse