SQL SERVER – Delete Backup History – Cleanup Backup History

SQL Server stores history of all the taken backup forever. History of all the backup is stored in msdb database. Many times older history is no more required. Following Stored Procedure can be executed with parameter which takes days of history to keep. In following example 30 is passed to keep history of month.

USE msdb
GO
DECLARE @DaysToKeepHistory DATETIME
SET @DaysToKeepHistory = CONVERT(VARCHAR(10), DATEADD(dd, -30, GETDATE()), 101)
EXEC sp_delete_backuphistory @DaysToKeepHistory
GO

Reference: Pinal Dave (http://www.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – Delete Backup History – Cleanup Backup History

  1. Also, be aware that if you don’t purge history regularly, you can end up with a really large MSDB – even just several gigs can give you performance problems when deleting history. I’ve run into servers that had to take an outage just to delete the history because it’d grown so large.

  2. Hi.
    I only wanted to mention if you’re using a German MS-SQL Server you got to replace the parameter 101 with 104 to get the correct input-Format for the sp_delete_backuphistory procedure.
    i.e. SET @DaysToKeepHistory = CONVERT(VARCHAR(10), DATEADD(dd, -30, GETDATE()), 104)
    regards.

  3. Hi Pinal,

    I have a doubt regarding the comments whenever a question is raised by some one hw can d solution known to d people apart from the questioner.

    Thanks,
    Narenndra

  4. sir ,
    i have some problem , i have 10 system and one server
    and i want to delete all the search item and web history from server will be it’s possible…? if possible please send me reply
    it’s n urgent………..

    teenu ranga
    bikaner
    rajasthan

  5. One key problem with this procedure and other similar ones is that they don’t yield to other running processes. I had to use this procedure in emergency and it can cause locking and blocking unless you have exclusive access to the tables used in the stored procedure.

    This is because often many millions of rows being deleted if you are using log shipping of several databases for example that performs a frequent log backup for each one.

    Maybe MS will rewrite them to give way in the next release.

    Tony S.

  6. I tweaked the System SP to make it more efficient. I ran into too many blockings and lockings with table data types. I used good old temp tables and indexed them.

    Also I am deleting only the TLog backups here. You can change it to do both by removing the where clause for ‘L’

    I ran it giving it 15 days at a time.

    USE [msdb]
    GO
    /****** Object: StoredProcedure [dbo].[spDeleteBackuphistoryTLogs] Script Date: 08/26/2011 09:13:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ALTER PROCEDURE [dbo].[spDeleteBackuphistoryTLogs]
    @oldest_date datetime
    AS
    BEGIN
    SET NOCOUNT ON

    SELECT DISTINCT backup_set_id INTO #TempBackupSetID
    FROM msdb.dbo.backupset
    WHERE type = ‘L’ and
    backup_finish_date < @oldest_date

    CREATE CLUSTERED INDEX CID_TBSI ON #TempBackupSetID (backup_set_id)

    SELECT DISTINCT media_set_id INTO #TempMediaSetID
    FROM msdb.dbo.backupset
    WHERE type = 'L' and
    backup_finish_date 0)
    GOTO Quit

    DELETE FROM msdb.dbo.backupfilegroup
    WHERE backup_set_id IN (SELECT backup_set_id
    FROM #TempBackupSetID)
    IF (@@error > 0)
    GOTO Quit

    DELETE FROM msdb.dbo.restorefile
    WHERE restore_history_id IN (SELECT restore_history_id
    FROM #TempRestoreHistoryID)
    IF (@@error > 0)
    GOTO Quit

    DELETE FROM msdb.dbo.restorefilegroup
    WHERE restore_history_id IN (SELECT restore_history_id
    FROM #TempRestoreHistoryID)
    IF (@@error > 0)
    GOTO Quit

    DELETE FROM msdb.dbo.restorehistory
    WHERE restore_history_id IN (SELECT restore_history_id
    FROM #TempRestoreHistoryID)
    IF (@@error > 0)
    GOTO Quit

    DELETE FROM msdb.dbo.backupset
    WHERE backup_set_id IN (SELECT backup_set_id
    FROM #TempBackupSetID)
    IF (@@error > 0)
    GOTO Quit

    DELETE msdb.dbo.backupmediafamily
    FROM msdb.dbo.backupmediafamily bmf
    WHERE bmf.media_set_id IN (SELECT media_set_id
    FROM #TempMediaSetID)
    AND ((SELECT COUNT(*)
    FROM msdb.dbo.backupset
    WHERE media_set_id = bmf.media_set_id) = 0)
    IF (@@error > 0)
    GOTO Quit

    DELETE msdb.dbo.backupmediaset
    FROM msdb.dbo.backupmediaset bms
    WHERE bms.media_set_id IN (SELECT media_set_id
    FROM #TempMediaSetID)
    AND ((SELECT COUNT(*)
    FROM msdb.dbo.backupset
    WHERE media_set_id = bms.media_set_id) = 0)

    DROP TABLE #TempBackupSetID
    DROP TABLE #TempMediaSetID
    DROP TABLE #TempRestoreHistoryID

    IF (@@error > 0)
    GOTO Quit

    COMMIT TRANSACTION
    RETURN

    Quit:
    ROLLBACK TRANSACTION

    END

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #003 « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  9. I need to delete backup files when my backup disk usage reaches a certain size. Is this possible using sql statements ? I can get a list of backup files created, but deleting them appears rather more difficult. Deleting after a certain age is no good, disk usage is my criteria.

  10. USE msdb
    GO

    DECLARE @COUNTER INT
    DECLARE @DaysToKeepHistory DATETIME
    SET @COUNTER = 383
    WHILE @COUNTER > 365
    BEGIN
    SET @DaysToKeepHistory = CONVERT(VARCHAR(10), DATEADD(dd, -@COUNTER, GETDATE()), 101)
    print @DaysToKeepHistory
    select count (0) from dbo.backupset where backup_start_date < @DaysToKeepHistory
    EXEC sp_delete_backuphistory @DaysToKeepHistory
    WAITFOR DELAY '00:01:00'
    –select MIN (backup_start_date) from dbo.backupset
    SET @COUNTER = @COUNTER – 1

    END

  11. These indexes will speed this process up a great deal. You can also do them on the restore tales if you need. The system tables are not properly indexed.

    – Create on MSDB to speed up purging of backup history

    USE [msdb]
    GO
    CREATE NONCLUSTERED INDEX [IX_BackupSet_Media_set_id]
    ON [dbo].[backupset] ([media_set_id])
    With (online=on)

    CREATE NONCLUSTERED INDEX [IX_BackupSet_Backup_set_id_Media_set_id]
    ON [dbo].[backupset]
    (backup_set_id, media_set_id)
    With (online=on)
    GO

    Create index IX_Backupset_Backup_set_uuid
    on backupset(backup_set_uuid)
    With (online=on)
    go

    Create index IX_Bbackupset_Media_set_id
    on backupset(media_set_id)
    With (online=on)

    go

    Create index IX_Backupset_Backup_finish_date_INC_Media_set_id
    on backupset(backup_finish_date)
    INCLUDE (media_set_id)
    With (online=on)
    go

    Create index IX_backupset_backup_start_date_INC_Media_set_id
    on backupset(backup_start_date)
    INCLUDE (media_set_id)
    With (online=on)
    go

    Create index IX_Backupmediaset_Media_set_id
    on backupmediaset(media_set_id)
    With (online=on)
    go

    Create index IX_Backupfile_Backup_set_id
    on Backupfile(backup_set_id)
    With (online=on)

    go

    Create index IX_Backupmediafamily_Media_set_id
    on Backupmediafamily(media_set_id)
    With (online=on)
    go

  12. With those indexes created you can use this script to purge. This will help prevent you from deadlocking other backups. You can enable the Wait for Delay if needed.

    USE msdb
    GO
    DECLARE @deadlock_var NCHAR(3);
    SET @deadlock_var = N’LOW’;

    SET DEADLOCK_PRIORITY @deadlock_var;
    GO
    DECLARE @COUNTER INT
    DECLARE @DaysToKeepHistory DATETIME
    Select @COUNTER = datediff(dd,MIN (backup_start_date),getdate())from dbo.backupset
    SET @COUNTER = @COUNTER – 1
    PRINT @COUNTER
    WHILE @COUNTER > 180
    BEGIN
    SET @DaysToKeepHistory = CONVERT(VARCHAR(10), DATEADD(dd, -@COUNTER, GETDATE()), 101)
    print ‘Date used to purge’
    print @DaysToKeepHistory
    Print ‘Currently at ‘
    Print @Counter
    Print ‘Days’
    EXEC dba..sp_delete_backuphistory_Custom @DaysToKeepHistory
    –WAITFOR DELAY ’00:01:00′
    –select MIN (backup_start_date) from dbo.backupset
    SET @COUNTER = @COUNTER – 1

    END

    GO

  13. Hi,

    If backup history is deleted then how could you calcuate the capacity planning for a database?

    We have around 200 databases on a shared server and msdb is of 6 GB and we never deleted the history of msdb since it was built. We use msdb database for capacity planning and yearly backup history reports.

    I think it is a bad idea to delete the data from msdb unless it is really bothering you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s