INSERT [TableName] SELECT * FROM [TableName] 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?
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.