October 30, 2008 at 5:07 am
SQL Server 2000 with 32 Databases
I started
EXEC sp_delete_backuphistory '12/31/07'
about an hour ago and the Server is at a crawl.
How do I go about killing this ???????
October 30, 2008 at 7:56 am
Find SPID (sp_who2) that is running this.
kill (spid)
October 30, 2008 at 8:31 am
Why do you want to delete backup history ? It doesn't take up that much space.
It can be useful to:
- Identify when an old backup was taken in case I need to get it off archival tape
- Use the size information to see growth rates
October 30, 2008 at 12:34 pm
You should do the deletion in chunks rather than in one shot. Also, whats the size of msdb database?
MJ
October 30, 2008 at 12:44 pm
Guess I'm finding this out.. shoulda done it in chunks. my msdb database is 16mb
October 30, 2008 at 1:55 pm
Something like:
USE MSDB
DECLARE @backup_date char(10)
DECLARE @countback int
DECLARE @sql varchar(150)
set @countback = 200
while @countback >=0
BEGIN
print @countback
select @backup_date=convert(char(10),min(backup_start_date),101) from msdb..backupset
print @backup_date
BEGIN TRAN DELE
set @sql='SP_DELETE_BACKUPHISTORY '+''''+ @backup_date+''''
exec (@sql)
COMMIT TRAN DELE
select @backup_date
set @countback = @countback - 2
END
MJ
October 30, 2008 at 2:13 pm
Thank you
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply