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:

Have more questions?

Contact us

Was this article helpful?
0 out of 0 found this helpful

Provide feedback for the Documentation team!

Browse this section