Follow

How to cancel a long running SQL Job, such as a backup

Problem:

A database backup has started as part of configured nightly maintenance, but due to the large database size and the performance of the target disk, the job is still running now impacting performance of daily operation. Since the backup does not run via SQL Agent it cannot simply be stopped via SQL Management Studio.

Solution:

Determine the SPID of that command/job that performs the backup:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

 

Kill the command SPID determined above with the kill TRANSACT SQL. Syntax: 

KILL { session ID | UOW } [ WITH STATUSONLY ] 

More Info: https://msdn.microsoft.com/en-us/library/ms173730.aspx

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Article is closed for comments.