Kill 142, I was trying to kill one of the session and found the following
error.

SPID 142: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.

when I use : dbcc opentran, it gave me following result

Oldest active transaction:
SPID (server process ID) : 142
UID (user ID) : 1
Name : user_transaction
LSN : (535:511:1)
Start time : May 3 2007 10:31:54:390AM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

when I used sp_who2, it gave me following entry

142 RUNNABLE WebSp WEBEDA2 . master KILLED/ROLLBACK 16 1 01/01
00:00:00 .Net SqlClient Data Provider 142

when I used sp_lock, it gave me following entries.

63 1 (master) 85575343 0 TAB IS GRANT
142 11 (myDB) 354100302 1 KEY (9500ee50c84e) X GRANT
142 11 (myDB) 354100302 1 PAG 1:2660 IX GRANT
142 11 (myDB) 354100302 0 TAB IX GRANT
142 11 (myDB) 0 0 DB S GRANT

I would appreciate if anyone help me out... its the production box and I
can't restart the SQL Server Services.

Thanks

Rogers,

If you repeatedly do "KILL 142 WITH STATUSONLY" it will probably continually
repeat the same status. If so, then this kind only gets eliminated by
restarting the SQL Server service.

As long as it is not locking anything anyone else needs, it can be ignored,
but it looks like that may not be the case with the MyDB locks.

Time to figure out the nearest window for a restart.

RLF

"Rogers" <naissani (AT) hotmail (DOT) com> wrote