June 11, 2013 at 6:10 am
Looking at the tables within the MSDB Database -I can see that the dbo.backup* tables have been populated since the server was first commissioned.
These tables contain backup set information and details of the backup including filesize ad location ( possibly useful to some )
currently my files sit at 144,462 records and 72,231.
Question :
Can these files OR should these files be cleared down at least purge some records ?
This is not due to lack of space on the server, just a housekeeping question.
Thanks
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
June 11, 2013 at 6:38 am
I guess it depends on whether you still maintain those backup sets etc. With log shipping in particular those tables can get bloated pretty quickly, so on my servers I clear them down periodically.
You can use sp_delete_backuphistory to clean up the history.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 11, 2013 at 6:45 am
don't completely empty them as they are used by the gui to create restore histories if you use them to do restores. they can be useful in a disaster situation to track down backups or see who restored a database and from where.
You shouldn't need this data going too far back though so it is worth purging them. There is a history cleanup task in the maintenance plans for this purpose.
---------------------------------------------------------------------
June 11, 2013 at 6:50 am
We have a maintenance plan that, among other things, runs a "History Cleanup Task". We have it set to clean up history of "Backup and Restores", "SQL Server Agent Jobs", and "Maintenance Plan History" older than 8 weeks. No sense in having any backup history of backups that do not exist anymore. I have seen performance issues caused by not cleaning up backup history.
June 11, 2013 at 7:21 am
Great - I was thinking along the same lines... although I might want to restore my Backup/T-Log from February 15th 2010 !
Thanks for the input
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
June 11, 2013 at 7:26 am
Agent Job with ...
USE msdb;
GO
EXEC sp_delete_backuphistory @oldest_date = (GETDATE() - 60);
to keep 60 days ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
June 11, 2013 at 7:31 am
Really? From 2010? Well, can set the the task to delete older than anything you want. Even years. 🙂
Keep in mind that you can restore a backup even if it is not in backup history. You would just do a restore "from device". So you dont necessarily need a backup from 2010 to be in the database history.
John
June 11, 2013 at 7:31 am
start further back and work your way forward in chunks, that SP is cursor based and can be slow and intrusive.
whether 60 days is enough only you can really tell but I presume you will never restore that far back.
the information in there is quite useful to track database growth,
---------------------------------------------------------------------
June 11, 2013 at 7:42 am
Thats a very good point about the database growth over time.
Currently sat at 6gb - started at 700mb.
When doing "selective Restores" - I do take a "Copy Only Backup" at month ends and save those.
I have needed to go back to a specific time but only to cover the previous month-end issues .... so I think 60 days is more than enough.
Thank you for your help
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
June 11, 2013 at 7:48 am
You do realize that you don't need the data in these tables to restore a database, right?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply