Delete backup history failed for server

SQL Server stores a finish history of all SQL backup and also reclaim operations, and also various other historic tasks such as tasks prefer Database Mail, Jobs, Log Shipping, Policies, Maintenance Plans, and so on. on a server circumstances in the msdb database.

You watching: Delete backup history failed for server

All of this data SQL Server inserts into the msdb database backup and reclaim background tables. Each of these tables contains a row of information for a different action:

backupfile – a row for eincredibly information or log file that is backed up

backupfilegroup – a row for every filegroup in a backup set

backupmediafamily – a row for every media family members. In situation that a media family is located in a mirrored media collection, the media family members has a sepaprice row for each mirrored set in a history table.

backupmediaset – a row for every backup media set

backupset – a row for every backup set

restorefile – a row for eexceptionally restored file consisting of documents brought back indirectly by a fileteam name

restorefilegroup – a row for every restored filegroup

restorehistory – a row for eextremely regain operation

Having a big number of databases and also perdeveloping constant SQL backup and restore procedure outcomes with a large msdb database and also it is preferable to percreate SQL backup and also regain background cleanup of the msdb database background tables.

For example, having a SQL Server circumstances that is restoring the transaction log backups for a huge variety of databases eextremely 15 minutes eexceptionally day, eexceptionally time that event occurs it percreates one insert right into an msdb mechanism table. As a result background information in a year obtain up to a few million rows.

This outcomes with an extremely huge msdb database and also this information will certainly reprimary in the msdb database and it hregarding be manually rerelocated.

In this write-up, we will certainly display a pair of options to purge the msdb database using native options.

See more: Problem Event Name: Bluescreen Os Version: 6

Removing the SQL backup and reclaim background from the msdb database from SQL Server Management Studio

One of the options to delete backup and gain back background is to use the Delete backup and gain back background indevelopment for the databases checkbox.

To use this option:

Right click a database in the Object Explorer pane and also choose the Delete option:

*

Rerelocating the SQL backup and also gain back background from the msdb database utilizing mechanism stored procedures

To delete backup and regain background for a details database use the following script:


The sp_delete_database_backuphistory stored procedure will delete information from the backup and restore history tables for a mentioned database. It hregarding be run from the msdb database.

This method requires many hand-operated job-related in instances wbelow a backup and also reclaim background has to be deleted for a big number of databases.

To check if tbelow is any kind of orphaned SQL backup and also restore history rows left usage the adhering to query:


SELECT database_name AS , COUNT(backup_set_id) AS OrphansFROM backupsetWHERE database_name NOT IN (SELECT name FROM master.dbo.sysdatabases)GROUP BY database_name
If executing this query this query returns any type of results it implies that there are databases that have actually SQL backup and gain back background without a physical referral on a backup history table.

To delete the backup history older than a specified date from the msdb database backup and also reclaim background tables usage the following script:


Performing a SQL backup and regain cleanup procedure making use of this approach have the right to be incredibly slow for a huge msdb database. Running this command also on a large collection of data without utilizing indexes might take hours or also days to complete. Another concern is that this will block backups as it will certainly lock the msdb mechanism tables.

See more: Taskbar Icon Stays Highlighted Windows 10, Taskbar Not Highlighting Open Apps

In regards to scalcapacity mechanism tables will percreate poorly if not appropriately indexed, however the msdb database backup and reclaim tracking tables perform not have indexes. Creating indexes on the msdb SQL backup and recovery history tables will certainly handle the performance worry. The complying with manuscript creates indexes on msdb history tables:


USE msdbGO-- create indexes on the backupcollection columnCREATE INDEX IX_backupset_backup_set_iid ON backupset(backup_set_id)GOCREATE INDEX IX_backupset_backup_set_uuiid ON backupset(backup_set_uuid)GOCREATE INDEX IX_backupset_media_set_iid ON backupset(media_set_id)GOCREATE INDEX IX_backupset_backup_finish_date_i ON backupset(backup_finish_date)GOCREATE INDEX IX_backupset_backup_start_date_i ON backupset(backup_start_date)GO-- create index on the backupmediacollection column CREATE INDEX IX_backupmediaset_media_set_iid ON backupmediaset(media_set_id)GO-- develop index on the backupfile column CREATE INDEX IX_backupfile_backup_set_iid ON backupfile(backup_set_id)GO-- produce index on the backupmediafamily column CREATE INDEX IX_backupmediafamily_media_set_iid ON backupmediafamily(media_set_id)GO-- develop indexes on the restorebackground columnCREATE INDEX IX_restorehistory_restore_history_iid ON restorehistory(restore_history_id)GOCREATE INDEX IX_restorehistory_backup_set_iid ON restorehistory(backup_set_id)GO-- create index on the restorefile columnCREATE INDEX IX_restorefile_restore_history_iid ON restorefile(restore_history_id)GO--create index on the restorefilegroup columnCREATE INDEX IX_restorefilegroup_restore_history_iid ON restorefilegroup(restore_history_id)GO

Removing the SQL backup and regain background from the msdb database by using the History Cleanup Task

Rerelocating historical backup and reclaim information from historic tables in the msdb database deserve to be achieved by utilizing the History Cleanup Task dialog. The History Cleanup Task dialog job supports deleting SQL backup and gain back history, SQL Server Agent Job background, and maintenance setup background.

To delete backup and restore history:

Right click the Maintenance Plans under the Management node in the Object Explorer pane and also choose the New Maintenance Plan option: