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?