February 16, 2006 at 9:09 am
One of the nightly job was running since last night
and lock the table.Here is the out put of the sp_lock
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
74 8 0 0 DB S GRANT
74 8 618198444 0 TAB IX GRANT
74 8 618198444 1 KEY (0600e3d7d9fc) X GRANT
74 8 618198444 1 PAG 8:1802 IX GRANT.
I kill the spid 74 but spid is still running and had one open transaction since 1 am.
here is the out put of command kill 74
SPID 74: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
.
Spid 74 is still active and locked the table.
here is the out put of sp_who2 74
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID
----- ------------------------------ -------------------------- -------- ----- --------- --------------- ------- ------ -------------- ------------------------------------------------------------------------- -----
74 RUNNABLE TNDB\arcsystn TNDB . ForteTest KILLED/ROLLBACK 15 0 02/16 01:00:08 SQLAgent - TSQL JobStep (Job 0x82A381F90660C848AD4221D26AC25159 : Step 2) 74
How can i get rid of spid 74.Help Please
February 16, 2006 at 9:14 am
Notice how the Command is stating Killed/RollBack.
Sql is rolling back all the transactions it had completed. Your just going to have to wait for it to finish.
As a rule of thumb If a process has been running an hour, and you kill it, it will probably take an hour or so to roll back.
February 16, 2006 at 11:15 am
You can also stop the job in enterprise manager and the rest will follow .
Mike
February 17, 2006 at 11:19 am
If the process includes a linked server, and/or some other ODBC connectivity, you may not be able to remove it without restarting sql. restarting MSDTC may resolve it but not always.
Terry
February 18, 2006 at 2:04 am
Hi,
Before u kill any process have a look what type of lock that particular table holds.
As ur query have 3 locking effects Intent Exclesive (modifying the data),Exclusive Lock(X) locks the table from being accesed by other resource,Shared Lock (Read Only).
Look in more deep may be any other query accessing the table while ur job is acessing the table.
Killing the spid is not the solution for any problem.Find out the real problem otherwise u will be in problem.
Looks as a dead lock.
from
Killer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply