Puzzling Question 2

[Tomi]

I have come across this more than once:

When looking at sp_who2 'active' for blocking information there may be several spids that may be involved in blocking say for eg . spid 177 blocked by 210,210 blocked by 161, 161 blocked by 191. But in the spid list 191 is not there. Has anyone come across a similar scenario? Why does a spid not appear in the sp_who2 'active' output ?

Any ideas ?

Thanks
Tomi

 

[ptheriault]

I've never seen that. If you select from sysprocesses is the SPID there?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

Chance? Maybe sp_who2 ran just the microsecond after 191 disconnected and the blocking hadn't cleared up yet.

-SQLBill

Posting advice: FAQ481-4875

 

[Tomi]

Thanks SQlbill; ptheriault - sp_who2 basically selects from the sysprocesses table.

Tomi

 

[mrdenny]

Yes I have seen that.

If spid 191 did a begin tran, ran some code and never commited it won't show up as active but will still retain it's locks.

Little issues like this are why I've written an sp_who3 which I can post if you would like a copy of it. sp_who3 supports a third paramater blocked which shows you all the spids which are either blocked or blocking. It also gives much more information if you pass it a spid number including the command being runn from the input buffer, the full output buffer of the current active command (the entire thing, not just the first 255 characters), info on all the spids that it's blocking, and all it's locking info.

I've found it to be quite handy. I've got a SQL 7 version and a SQL 2000/2005 version.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

Denny,
Can you post your 2005 version for sp_who3? I wouldn't mind having it.

Thanks



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

The FAQ is all done. I've posted versions for SQL 7, SQL 2000 and SQL 2005. If anyone needs SQL 6.5 I'm sure I can get something working.

The FAQ is faq962-6561 <- here.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

awesome, thanks Denny.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Tomi]

I appreciate this.Thanks mrdenny

-Tomi