Backup History Trouble

Question

Backup History Trouble

LerxtDBA

SSCrazy

Points: 2190
More actions
May 7, 2010 at 8:42 am

#156934

I've recently inherited a relatively complex set of SQL instances. New job. New and interesting challenges.
We do log shipping on a quite a few databases and do it fairly frequently. Unfortunately, whoever set up the log shipping didn't bother to create a task to clean up the backup history tables in msdb. Now msdb is bloated and I'm tight on space. I'm not allowed much in the way of down-time and trying to clear out >3M records using sp_delete_backuphistory is causing unacceptable blocking.
So I'm trying to come up with ways to fix this problem.
I was thinking of taking a backup of msdb, restoring it to another instance, cleaning up the backup history there, backing up the clean version, and finally restoring it onto production. I'll be honest, I've never had to restore msdb before so I don't know the implications of attempting this. I have a feeling there might be trouble with restoring msdb to a different instance but I'm not finding much online to read about this.
Any ideas out there that wouldn't much require downtime?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 1

It's not a bad idea, but the problem is, you lose all your history going forward from the point of backup.

A slightly better idea would be to export the TABLE to a new database / server, work on it there (figuring out how to identify the records to be deleted), then, keeping the to-be-deleted records, move the table back as a user table and do a JOINed Delete so that you only lose the records you want to lose.

Of course, this probably needs to be done during off hours or you really will kill your DB.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

I'm in a similar situation, but I don't want to lose my FULL backup history because I find it useful to look at growth over time. T-Log backups take up the most space, so I modified sp_delete_backuphistory to only delete old T-Log history. Perhaps you need to temporarily disable your backups while sp_delete_backuphistory runs to avoid blocking ? (a guess)

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 3

Actually, this other response brings up a good point. What exactly is the proc blocking? Or being blocked by?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

I assume blocked by t-log backups accessing the same tables .... Haven't verified it though.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

First create the indexes as advised (allow around 10 minutes to complete):

CREATE

INDEX [media_set_id] ON [dbo].[backupset] ([media_set_id])

CREATE

INDEX [restore_history_id] ON [dbo].[restorefile] ([restore_history_id])

CREATE

INDEX [restore_history_id] ON [dbo].[restorefilegroup] ([restore_history_id])

Then run the original command:

EXEC sp_delete_backuphistory 'your date'

Then while the delete is running, run these against MSDB at the same time to watch the progress:

select count (*) from restorefile

select count (*) from restorefilegroup

select count (*) from restorehistory

select count (*) from backupfile

select count (*) from backupset

select count (*) from backupmediafamily

select count (*) from backupmediaset

It worked perfectly for me when I had to delete records sitting since 2007. Got it from this forum only!!

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 6

If you haven't verified what, how do you know it's blocking at all? Are you getting a deadlock error? What messages are you seeing?

Sp_who2 shows blocking SPIDs and you can run the following code to verify what is doing the blocking.

DECLARE @Handle BINARY(20)

SELECT @Handle = sql_handle

FROM SysProcesses

WHERE SPID = 133 --Change this SPID to the blocking SPID

SELECT *

FROM ::fn_get_sql(@handle)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

DBA in Unit 7 SSCarpal Tunnel Points: 4059 More actions · Answer 7

I find the backup history tables are valuable too in terms of size trending, and data-retrieving for occasions that some ancient data need to be retrieved, which may only exist in the backups. The backup history table will provide clues to where/when to find them. Especically when the database has been dropped at some point of time.

Modify the sp_delete_backuphistory to keep the Full backup history and delete the t-log backup history (type='L') to certain period and then schedule it to run after each t-log backup.