Killed Session 1

[DrSql]

I have killed a session, still it shows up in sp_who2 showing as rolling back. If I try to kill that again I get the below output
"SPID 53: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds."

Any possible way to get rid of this, without restarting the agent.
Thanks


Dr. Sql
vpathisseril@yahoo.com
Good Luck.

 

[mrdenny]

Nope, getting rid of this will require that the SQL Service be restarted.

Now, here's why.
Spid 53 was under someone's control. That person got sick of waiting and disconnected from the database, or thier computer crashed, or the network died, they diconnected in some method other than the client crasefully telling the SQL Server that it was closing the connection. 99,999 times out of 100,000 times this will cause no problems. But every once in a while (it's probably actually much less than one in every 100,000 bad disconnects) the SQL Server doesn't realise that the connection was broken. The transaction is completly rolled back, but the last part of the rollback, is to inform the client that the query has been killed and the data changes have been rolled back. But because the client isn't there any more the server can't inform the user that the transaction has been roled back, so the rollback hangs.

Unfornitually there isn't any good way to deal with this other than a service restart.

Unfornitually even if the user reconnects it won't help, becuase they will be assigned to a different SPID.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[DrSql]

Its a backup job is locked with session. In that, if I re-start the SQL-Agent does it would release the spid?


Dr. Sql
vpathisseril@yahoo.com
Good Luck.

 

[mrdenny]

I'm not quite clear what your asking.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)