SQL Server backup list timeout

While I'm normally an Oracle DBA, I also work with other databases, such as SQL Server.

Recently I was attempting to restore a database from backup for a client. I say "attempting" because the enterprise manager (yes, we still have SQL Server 2000 in places ) kept timing out. I'd go to the list of backups and it seemed like everything would just hang.

After some manic googling, I found this post on the SQLTeam blog.

In short, and against my instincts as an Oracle DBA, apparently you have to manually clean up the MSDB database to remove old backups. As this is equivalent to mucking about in the SYSTEM tablespace in Oracle ( a serious no-no ) I was hesitant, but after running though the procedure I got functionality restored.

The code I used was as follows:

use msdb
go

set nocount on
declare @oldest_date smalldatetime;
select @oldest_date = min(backup_finish_date) from backupset with (nolock)
set @oldest_date = dateadd(dd, +1 ,@oldest_date)
EXEC sp_delete_backuphistory @oldest_date;

-- If you'd like to keep the backup history for 30 days
use msdb;
go

declare @oldest_date smalldatetime;
set @oldest_date = dateadd(dd, -30,getdate())
EXEC sp_delete_backuphistory @oldest_date;

Ok, lets look at what this is doing. In the first statement, we're declaring a variable of type smalldatetime. Then we populate that value with the oldest finish date from backupset; in other words, we're finding the last record in the table by date.

After we find that oldest date, we add one day to it, then run a stored procedure ( sp_delete_backuphistory ) to delete records before the "oldest day + 1" which we pass to the stored procedure as an argument.

Another poster on the SQLTeam blog called it a "nibble script". An apt description, as it removes records from the table one by one.

I found it didn't time out if I increased it to remove the last 10 records or even the last 30; if you've more records in your table than I had you may find different results.

Still, it worked for me. Once old backup records were removed, the long running query which was causing the apparent timeout completed in a reasonable time.