tag:blogger.com,1999:blog-2453693540149032389.post2528051326434992972..comments2023-05-18T14:11:09.911+01:00Comments on sql solace: Cleaning up MSDBr5d4http://www.blogger.com/profile/09999231147372917249noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-2453693540149032389.post-1452274112019140792012-05-17T20:14:38.802+01:002012-05-17T20:14:38.802+01:00Just wanted to drop a thank you note!!
Ran into an...Just wanted to drop a thank you note!!<br />Ran into an issue on a log shipping database where the MSDB database had grown to 16Gb in size. After using your "Clear Backup History " to do some pruning on the main culprit of sysjobhistory, I put your "history cleanup" job in place to keep the MSDB tidy.<br /><br />Cheers.AMBhattihttps://www.blogger.com/profile/16327500134757802079noreply@blogger.comtag:blogger.com,1999:blog-2453693540149032389.post-74915597172903625302011-08-29T22:50:06.154+01:002011-08-29T22:50:06.154+01:00Hi Matthew,
Totally valid points. When servers ar...Hi Matthew,<br /><br />Totally valid points. When servers are mine to monitor I do have a tools db which records db growth, table growth, fragmentation etc manually.<br /><br />The scenario I'm addressing in this post is the absolute extreme. Sites where no one has done ever looked at msdb growth and the server was built in 2004 ! An example ><br /><br />In 24 hours a single db with TL backups every 30 minutes generates 48 rows in the table. The daily full backup generates another.<br /><br />15 user dbs on the server are backed up in this way. 15 & 49 = 735 rows a day. Master, Msdb and Model also being backed up daily gives another 3 rows, total 738.<br /><br />7 (years) * 365 (days) * 738 (records) = 18876340 rows.<br /><br />Given that Msdb in SQL 2000 shipped with no indexes on the backup tracking tables, Enterprise Manager grinds to a halt if you attempt to view backup history.<br /><br />Add to this that msdb is growing in increments of 1MB and is highly physically fragmented over a drive.<br /><br />It's an extreme situation, but one I've come up against several times recently hence the effort and post about it.<br /><br />Richr5d4https://www.blogger.com/profile/09999231147372917249noreply@blogger.comtag:blogger.com,1999:blog-2453693540149032389.post-37047456992044709222011-08-29T15:27:07.196+01:002011-08-29T15:27:07.196+01:00While I agree that you don't need to keep baku...While I agree that you don't need to keep bakup history forever, it can be valuable data. For example, since backups are usually taken on a regular basis and the bakup history has the size of the backup file created over time, you can calculate growth estimates to see when more data file or disk space is going to be needed.<br /><br />Matthew TessierMatthew Tessierhttps://www.blogger.com/profile/14722163444228733793noreply@blogger.com