1

I just ran across a query:

INSERT [TableName] SELECT * FROM [TableName]

Note that it is the same table. This query causes serious blocking on one of my test databases (I wanted to drop it but couldn't and this made me investigate the issue).

The lock type is LCK_M_X. I cannot kill the session - i get the following message:

PID 56: transaction rollback in progress. Estimated rollback completion: 27%. Estimated time remaining: 5715216 seconds.

I don't want to wait the estimated 2 months for the rollback to complete. The transaction isolation level of the database is READ COMMITED.

Is it possible to kill this session? How should you act if you find such a query in one of the mission critical databases?

  • Try to use processExplorer. In ADM MODE, try to look for something in the SQL tree. You can kill processes there. – Racer SQL Jun 25 '15 at 14:00
  • Hey, I'm having the exact same problem in my production system. But, what causes this statement to be triggered? It seems to coincide during our index maintenance window. – Queue Mann Jan 21 '16 at 15:30
  • @QueueMann hey did this transaction disappear from the list? As far as I remember my SPID disappeared from the transaction list but it took a long time (a few days). I didn't find any other solution than waiting. I was lucky that this problem occurred on dev server. – BuahahaXD Jan 25 '16 at 8:55
  • No, it caused so much blocking I had to failover my cluster. IDK, it seems like all signs point to an index rebuild. – Queue Mann Jan 25 '16 at 16:36
5

That SPID is already killed and you will have to wait for the rollback process to finish. The estimated time is usually wildly inaccurate: don't worry about it. It will probably take from 1 to 2 times the execution time of the INSERT until the stage when it was killed.

If this happened in production, the answer would not be any different: wait for the rollback process to end. The database would not be consistent if the rollback operation was forcibly closed.

  • I doubt it is gonna end soon. It's been running for a few hours now. Since it is a test database and i want to get rid of it I tried deleting the mdf/ldf manually but it can't be done. Can you drop this DB somehow without stopping the SQL Server engine? – BuahahaXD Jun 25 '15 at 10:23

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.