1

I have a query that took about 6 hours to run, and I decided to cancel it.

Now, the query appears to still be running somewhere in the background because when I try to expand the database and access tables, its stuck on 'expanding.'

Can a query still be closing (i.e. rolling back) long after you exit out of SSMS? Its been almost 24 hours since I closed the query.

I also have temp tables still in the tempdb that I used in the original query, which are still there, even after I exit out of SSMS. Could this indicate the rollback is still in progress?

  • 1
    Almost anything is possible... When you closed SSMS you closed the interface but SQL Server is still doing its thing. Have you run sp_who2 or, better yet, sp_whoisactive? – SQL_Underworld Dec 30 '16 at 18:23
  • @guest22 - you should merge your two accounts into one, which will allow you to edit your own posts without having to get the edit approved – Max Vernon Dec 30 '16 at 20:18
4

Closing the connection to SQL Server, whether that is a connection utilizing SSMS or some other application causes the rollback of any currently active transaction(s) created by that connection. This includes any implicit transactions, and operations such as RESTORE DATABASE, etc. See this answer for more details about what can cause a transaction to be rolled back. Be aware that rollback may take an extended amount of time to complete, and will continue whether or not the application that caused the rollback is connected or not. Rollback will continue even if you restart SQL Server - in fact, restarting SQL Server will likely result in the database being marked as "in recovery" while rollback continues.

If SSMS is actually still running (perhaps the user interface is not visible, but the process is visible in Task Manager), the query might be still running, but I highly doubt that is the case.

Most likely you have some other connection holding a lock on some objects in the database in question, or a rollback is continuuing.

You can check the progress of transactions that are being rolled back by looking at system DMVs, such as:

SELECT des.session_id
    , des.host_name
    , der.command
    , der.percent_complete
FROM sys.dm_exec_requests der
    INNER JOIN sys.dm_exec_sessions des ON der.session_id = des.session_id
WHERE der.command = 'ROLLBACK';
  • 1
    Some people call DML statements "queries"; if the "query" in question was indeed a massive update, its rollback could take a while. – mustaccio Dec 30 '16 at 18:32
3

I've had it to where cancelling a query before it completes will leave the transaction incomplete - and the tables locked. You can use this query to find the process - then run KILL processNumber, obviously replacing processNumber with an ID from the query below:

SELECT
 blocking_session_id AS BlockingSessionID,
 session_id AS VictimSessionID,

 (SELECT [text] FROM sys.sysprocesses
  CROSS APPLY sys.dm_exec_sql_text([sql_handle])
  WHERE spid = blocking_session_id) AS BlockingQuery,

 [text] AS VictimQuery,
 wait_time/1000 AS WaitDurationSecond,
 wait_type AS WaitType,
 percent_complete AS BlockingQueryCompletePercent
 FROM sys.dm_exec_requests
 CROSS APPLY sys.dm_exec_sql_text([sql_handle])
 WHERE blocking_session_id > 0

You can also use the stored procedure sp_who2 to view processes. As far as I can tell, this is a more up-to-date way to view them; however, the above query still works as of SQL Server 2016, though Microsoft does say that they'll remove support for sys.sysprocesses in a subsequent release.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.