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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 KILL not working - RESOLVED
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/20/2008 :  05:06:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 05/21/2008 05:26:33

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 05/20/2008 :  05:19:08  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
What does Kill 75 WITH STATUSONLY show?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Edited by - harsh_athalye on 05/20/2008 05:19:19
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/20/2008 :  05:35:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/21/2008 :  03:34:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 05/21/2008 :  04:02:16  Show Profile  Reply with Quote
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

Sweden
30421 Posts

Posted - 05/21/2008 :  04:15:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
4464 records. Record size was 92 bytes.

Don't laugh!


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

Edited by - SwePeso on 05/21/2008 04:16:47
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 05/21/2008 :  04:19:41  Show Profile  Reply with Quote
What is it locked with? Have you checked sp_lock?

(he says trying not to laugh too hard)

Edited by - RickD on 05/21/2008 04:20:03
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/21/2008 :  04:50:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 05/21/2008 04:53:14
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 05/21/2008 :  04:55:10  Show Profile  Reply with Quote
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.


Edited by - RickD on 05/21/2008 05:01:48
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/21/2008 :  05:04:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 05/21/2008 05:10:48
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 05/21/2008 :  05:08:45  Show Profile  Reply with Quote
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

Sweden
30421 Posts

Posted - 05/21/2008 :  05:18:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30421 Posts

Posted - 05/21/2008 :  05:19:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
82 Posts

Posted - 06/26/2008 :  06:07:37  Show Profile  Send suresha_b a Yahoo! Message  Reply with Quote
>> 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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 06/26/2008 :  07:13:28  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

India
1 Posts

Posted - 12/23/2010 :  02:16:23  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000