Blocked and blocking users

Question

Post reply

Blocked and blocking users

kamafasa

SSC Enthusiast

Points: 104
More actions
July 12, 2006 at 3:38 am

#61449

Does any one have a way (script stored procedure) to show who is holding and lock and who is being blocked and for how long.It would be great to be able to have a stored procedure return this information
TIA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 1

Here's a view I use. I suppose you could turn it into a stored procedure.

create view vw_BLOCKERS

AS

SELECT * FROM MASTER..SYSPROCESSES WHERE BLOCKED <> 0 OR

SPID IN( SELECT BLOCKED FROM MASTER..SYSPROCESSES WHERE BLOCKED<>0)

Tom

mngong SSCertifiable Points: 6167 More actions · Answer 2

Your view will select sessions that are blocked...

The question seemed to be asking for a listing of those who are blocked and those who are causing the blocking

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 3

My view selects both. It grabs the blocking spid with the or clause.

SELECT BLOCKED FROM MASTER..SYSPROCESSES WHERE BLOCKED<>0

The blocked column is the blocking spid. Test it, it works like a charm.

Thanks

Tom

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 4

I like to use sp_who2 - get most of the information I need from that.

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 5

theres a bunch of useful things in the scripts --> lock and connection management section. sp_what, sp_who3 are good places to start. they arent hard to mod to get you exactly what you need.