Ask the Community
Groups
How to cancel a long running SQL Job, such as a backup - Connect IT Community | Kaseya
<main> <article class="userContent"> <h3 data-id="problem">Problem:</h3> <p>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.</p> <h3 data-id="solution">Solution:</h3> <p>Determine the SPID of that command/job that performs the backup:</p> <blockquote class="blockquote"> <p>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 <br>FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a <br>WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')</p> </blockquote> <p> </p> <p>Kill the command SPID determined above with the kill TRANSACT SQL. Syntax: </p> <pre class="code codeBlock" spellcheck="false" tabindex="0">KILL { session ID | UOW } [ WITH STATUSONLY ] </pre> <p>More Info: <a href="/home/leaving?allowTrusted=1&target=https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms173730.aspx">https://msdn.microsoft.com/en-us/library/ms173730.aspx</a></p> </article> </main>