KILL EXEC sp_delete_backuphistory '12/31/07'

[jpotucek]

--------------------------------------------------------------------------------
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 ???????

 

[JayKusch]

You could run sp_who from Query Analyzer and find the session that is executing the sp_delete_backuphistory.

You then take the SPID (session ID at the far left) and execute the command

KILL SPID (as in KILL 238 ... if session ID is 238)

BUT ... it may take a bit to finish since it will go into a ROLLBACK condition and will be restoring (more or less) the records it had deleted from the table up until the point when you fired off the KILL command.

Thanks

J. Kusch

 

[mrdenny]

If you have a lot of backup history this is to be expected. When deleting backup history you should do it a little bit at a time as if you are doing T/Log backups of all your databases there could be thousands of records per day to delete.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mutley1]

As Denny said, you want to do a bit at a time, so firstly find out when your OLDEST backup record is

USE MSDB
GO

select MIN(backup_finish_date) from backupset

Once you have that date you can add a few days / weeks / months onto it to find out how much it will try and delete if you pass a particular dat as per what you mentioned above. E.g. if the above says your earliest date is 2000-02-28, then add a month on to that and see how many records it brings back, as per below

select count (*)
from backupset
where backup_finish_date <= '2000-03-31'

You'll have to see how you go as it depends on what your backup regime is and when everything was created etc.

HTH,
M.