Earlier this week, I had explored why the sequence of index creation is important in a rather long post on performance tuning. You can read the post here: SQL Server – Performance – Best Practice – Create Index Before or After a data insert?
Today’s post, therefore, will be a nice, small one – something that you might want to convert into a scheduled “spring-cleaning” task.
Viewing Backup Information
Microsoft SQL Server uses MSDB to maintain a record for each completed backup. In production environments, it is generally required that all backup history is maintained. However, in Development and Quality Assurance environments, maintaining database backup history is generally not that important – after all, databases change in these environments often.
The backup history is maintained in the following tables within the MSDB:
- backupfile
- backupfilegroup
- backupmediafamily
- backupmediaset
- backupset
- restorefile
- restorefilegroup
- restorehistory
To learn more about viewing backup history information, refer the Books On Line page at: http://technet.microsoft.com/en-us/library/ms188653.aspx
Cleaning up the backup history
In order to cleanup the backup history, we can use one of the following two system stored procedures:
- sp_delete_backuphistory – To remove backup history for all databases within a SQL Server instance
- sp_delete_database_backuphistory – To remove backup history for a specific database
Some Key points to remember:
- Running these system stored procedures requires membership to the sysadmin fixed server role
- Running these procedures may be performance intensive
- Please do not use these without guidance from your DBA!
Until we meet next time,
Be courteous. Drive responsibly.
Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!
SQLTwins by Nakul Vachhrajani 
How often we need to clear this Backup history ????
what are the major advantages of clearing the Bacup History performance wise ??
LikeLike
Generally, when I no longer have a database on a particular instance, I clear out the backup history for that particular database.
As for the performance issues that may occur if the backup history is retained, I haven’t experienced any. However, personally, I like my servers to be clean and almost as good as new – which is why I typically perform a cleanup exercise once every quarter or so. Again, the key point to remember is that these cleanup routines would probably never be used in production, but in development and QA environments where databases are frequently changed or recycled, performing such cleanups is a good idea.
LikeLike
many thanks for the reply
LikeLike
Nice post, I have a question here. Do this backup history options work for all types of backups accordingly ?
LikeLike