question

ruancra avatar image
ruancra asked

Is there a background process that automatically deletes backup history?

Hi I'm trying to write a query to retrieve the last FULL BACKUP date, this particular database servers has many databases with Full, Differential and Transaction log backups that run. On the large databases a full backup gets done once a month, the rest once a week. When querying msdb.dbo.backupset, the max full backup date is retrieved for all db's except the ones backup once a month. It seems to keep backup history for only certain period. MSDB has the following procs that delete backup history: -sp_delete_database_backuphistory -sp_delete_backuphistory but these procs have not been run. My question: Is there any process that automatically deletes the backup history tables?
sql-server-2008-r2tsqlbackup
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I had a similar experience today. As Kev says check if someone is purging the data or if there is a job scheduled to purge the data.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
No. Unless you clear out the backup history, it will stay for years.....(slowly grinding your msdb database to. a. halt.) Something must have purged the data.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ruancra avatar image
ruancra answered
I managed to run a trace and see that the job sp_delete_backuphistory is running every 10 minutes or so, just struggling to find out who or what is running it.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.