Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

How to delete backup and restore history from msdb

Have you ever wondered why your msdb database is unusually large?  Did you know that SQL Server keeps historical information about every backup and restore operation in the msdb database?  It is recommended that you purge this backup and restore historical information on a scheduled basis.  Microsoft provides sp_delete_backuphistory to do the purge.  In SQL Server 2000, the stored procedure ran very slow as it cursored through the data.  As a result of the slowness, I wrote my own purge stored procedure.  I blogged about this a few years back. 

When we upgraded our systems to SQL Server 2005, I was curious if Microsoft had made sp_delete_backuphistory more efficient as I did not want to maintain my custom code.  I was ecstatic to find out that my wish had been granted.  I'll have to ask for money in my next wish before I run out of wishes!

For those of you who use my custom code (or similar code) to delete the backup/restore history in SQL Server 2005, start using sp_delete_backuphistory instead.

Print | posted on Wednesday, June 18, 2008 2:12 PM |

Feedback

Gravatar

# re: How to delete backup and restore history from msdb

I take the backup of the database with a specific name but i want to delete this after restore this.so please send me how to delete this database(which i take the name with).
8/1/2008 1:40 AM | Naresh Behera
Gravatar

# re: How to delete backup and restore history from msdb

Naresh, I don't fully understand your question. But perhaps all you need is DROP DATABASE dbName.
8/1/2008 9:16 AM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET