blocked process - 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

General SQL Server Forums

New to SQL Server Administration

blocked process

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-10-19 : 12:40:52

i have a delete process that is being blocked by something i cant find.

blkby = -3

if i try and kill -3 i get an error that it should be an int value.

any ideas? i've restarted the server as well, it wont go away :(

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-19 : 13:15:04

I think you've got a distributed transaction spid. This should do it:


--returns a GUID
select req_transactionUOW
from master..syslockinfo
where req_spid = -3

kill <guid>

Be One with the Optimizer
TG

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-10-19 : 14:02:09

okay, i got 104 rows of:
00000000-0000-0000-0000-000000000000

should i still kill it? just making sure.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-19 : 14:16:19

No. You got 104 rows all for req_spid=-3 ???

Be One with the Optimizer
TG

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-10-19 : 14:33:49

yeah... for all of them, the rsc_objid is either 0 or 99, except one, it's 898102240. dont know if that means anything.

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-20 : 08:40:26

probably you wont be able to kill the ones with all zeroes. Look out for remaining IDs which have non-zero values for spid-3 and kill all of them.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-10-20 : 10:11:09

no dice, they are all 0's :(

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-10-20 : 10:20:30

i cant even explore the database tables w/o it locking... same blocker.

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-20 : 10:33:36

Here is a detailed explaination on deferred transactions (-3) by Paul Randal. You'll need to dig error logs to find out if there is any filegroup that's offline. -2 is related to MSDTC.
[url]http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-dealing-with-negative-SPIDS-(-2-and-3).aspx[/url]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-20 : 12:11:21

quote:
Originally posted by Cindyaz

Here is a detailed explaination on deferred transactions (-3) by Paul Randal. You'll need to dig error logs to find out if there is any filegroup that's offline. -2 is related to MSDTC.
[url]http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-dealing-with-negative-SPIDS-(-2-and-3).aspx[/url]

Thanks! Nice article. I needed to tweak the url though:
http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-dealing-with-negative-SPIDS-%28-2-and-3%29.aspx

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources