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


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.


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


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

KILL { session ID | UOW } [ WITH STATUSONLY ] 

More Info:

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Contact us
Provide feedback for the Documentation team!