Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 KILL not working - RESOLVED

Author  Topic 

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 05:06:32
KILL 75 returns
SPID 75: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 554 seconds.
and has done for the last hour.


http://support.microsoft.com/kb/171224

DBCC PSS(1, 75) returns
pstat = 0x0

Are my only options to either restart the SQL Server service or wait for SPID 75 to die?

dbcc inputbuffer(75) returns
[dbo].[usp_MyStoredProcedureNameHere]


E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-20 : 05:19:08
What does Kill 75 WITH STATUSONLY show?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 05:35:39
Same as above
SPID 75: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 554 seconds.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-20 : 21:38:39
Depends on what the process did, sql may continue rollback after restarting.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 03:34:22
That's fine with me.
As of now, KILL 75 still reports SPID 75: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 554 seconds.
And the table involved is totally locked, dead-locked.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-21 : 04:02:16
How big was the transaction?

The problem if you restart could be that the database goes into recovery mode.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 04:15:12
4464 records. Record size was 92 bytes.

Don't laugh!


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-21 : 04:19:41
What is it locked with? Have you checked sp_lock?

(he says trying not to laugh too hard)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 04:50:02
Well, one strange thing appeared with sp_lock 75
spid	dbid	ObjId		IndId	Type	Resource	Mode	Status
75 30 0 0 DB S GRANT
75 30 1977058079 0 TAB IX GRANT
75 21 0 0 DB S GRANT
75 21 805994348 0 TAB IX GRANT
75 21 1989998566 0 TAB IX GRANT
1) ObjectID 805994348 in DBID 21 does not exist
2) ObjectID 1977058079 in DBID 30 is not locked by any means; I can select, update, delete and insert using that table.
3) ObjectID 1989998566 in DBID 21 is totally locked. This table can't be referenced in any way.

Other records returned with sp_lock 75 included
spid	dbid	ObjId		IndId	Type	Resource	Mode	Status
75 21 1989998566 0 RID 1:29638:0 X GRANT
75 21 0 0 EXT 1:103240 X GRANT
75 21 1989998566 0 PAG 1:99077 X GRANT
75 21 1989998566 0 TAB IX GRANT


The funny thing is that I didn't use any query hints when running the stored procedure.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-21 : 04:55:10
Do you have any other locks on 1989998566? You must have as the table lock is intent exclusive rather than exclusive and the only exclusive locks held by 75 are a page lock, a row lock and an extent.

805994348 May be a temporary object used by your query, although I would have thought this should be in the tempdb.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 05:04:48
There are a few EXTENT and PAG locks on objectid 1989998566, but all locks on this object in database is related to SPID 75.

These are all combinations of locks held on objectid 1989998566.
ObjId		Type	Mode	Status
1989998566 TAB IX GRANT
1989998566 RID X GRANT
1989998566 PAG IX GRANT
1989998566 PAG X GRANT


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-21 : 05:08:45
Thats strange as it doen't seem to have an exclusive lock of the whole table.

Will sys.dm_tran_locks give you any more information?

Do you have a decent backup just in case as you may have to try a restart?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 05:18:23
sys.dm_tran_locks is not available in SQL Server 2000.

I opened a new query window in a new spid and tried to put a tab lock on the offending table, run sp_lock and got following output
Spid	ObjId		Type	Mode	Status
75 1989998566 TAB IX GRANT
92 1989998566 TAB IS GRANT
Then I killed 92 and that worked. Then I killed 75, again, and now the transaction begun to rollback!

Problem seems to be resolved now. Transaction was rollbacked in 6 seconds and I can drop table now.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 05:19:53
Thank you for your patience.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2008-06-26 : 06:07:37
>> I opened a new query window in a new spid and tried to put a tab lock on the offending table

Peso,

How did you put the tab lock?

Suresh
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-26 : 07:13:28
One way would be to write an DML statement inside transaction block without ending transction.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

amit_patel
Starting Member

1 Post

Posted - 2010-12-23 : 02:16:23
quote:
Originally posted by harsh_athalye

One way would be to write an DML statement inside transaction block without ending transction.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"



Thanks,
Amit Patel
Go to Top of Page
   

- Advertisement -