KILL not working - RESOLVED - SQL Server Forums

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"

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"

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.

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"

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.

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"

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)

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"

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.

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"

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?

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"

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"

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

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"

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources