Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Find if a specific sp is blocking

Posted on 2009-05-04
9
Medium Priority
?
952 Views
Last Modified: 2012-05-06
I want to test if a specific sp (sp_bob) run by specific user (sa) is blocking.  If it is, return 'true'.  Else, return 'false'

This will be run by a scheduled task, and if this specific process is blocking at 4am, I _really_ don't care, it will fix itself up.
0
Comment
Question by:Hecatonchires
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 14

Expert Comment

by:Jan Franek
ID: 24302291
What exactly do you mean by 'blocking' ? Waiting for lock ? Locking some resource and causing other processes to wait for lock ? Or just running for some period of time ?
0
 
LVL 19

Expert Comment

by:grant300
ID: 24306121
You might want to tell us the Sybase product and version...

Regards,
Bill
0
 
LVL 7

Author Comment

by:Hecatonchires
ID: 24310578
Ah, mea culpa.

Sybase 11.9.2 :(

I've bodged up the following:
create procedure sp_find_blocking_sp
(
	@user varchar(10),
	@sp_name varchar(30)
)
as
begin
    /* This procedure returns how many times an sp is blocking.  
    Returns a 1 row recordset containing the field block_count 
    as an integer.  0 means its not blocking 
    
    YOUR_DB is hardcoded as it is a table reference :(
    */
	if exists( select 1
		from 	master..sysprocesses sysp1,
				master..syslogins sysl,
				YOUR_DB..sysobjects syso
		where 	sysp1.suid = sysl.suid
		and 	sysp1.id = syso.id
		and		sysl.name = @user
		and 	sysl.dbname = 'YOUR_DB'
		and 	syso.name = @sp_name)
	begin
		select  (select count(1)
		from master..sysprocesses sysp2
		where sysp2.spid = sysp1.spid)  as block_count
		from 	master..sysprocesses sysp1,
				master..syslogins sysl,
				YOUR_DB..sysobjects syso
		where 	sysp1.suid = sysl.suid
		and 	sysp1.id = syso.id
		and		sysl.name = @user
		and 	sysl.dbname = 'YOUR_DB'
		and 	syso.name = @sp_name
	end
	else
	begin
		select block_count = 0
	end
end
go

Open in new window

0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 19

Expert Comment

by:grant300
ID: 24310682
What do you mean, precisely, by "blocking"?

It looks as if your code will only every show a 1 or a 0 as a result since you are just looking to see if the particular stored procedure is running at the instant you run this procedure.

Regards,
Bill
0
 
LVL 7

Author Comment

by:Hecatonchires
ID: 24311192
@grant300 Ah, I see what you mean.  It's meant to be sysp2.blocked = sysp1.spid instead of
sysp2.spid = sysp1.spid
0
 
LVL 7

Author Comment

by:Hecatonchires
ID: 24311198
The blocked column contains the spid of the blocking process.  This is what I mean by blocking.
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 24311909
OK, I see, that you want to detect, whether some procedure locks other processes. Your code seems to work (with that sysp2.blocked = sysp1.spid correction). May be you don't even need the if part - if there's no blocking, the select in first branch will return 0.

So the problem is solved, or is there still something to solve ?
0
 
LVL 19

Accepted Solution

by:
grant300 earned 2000 total points
ID: 24314943
I am not a big fan of the derived table with correlation construct.  It is much harder to read and you are making the optimizer work harder to figure out how to do this.

Try....

Also, you do not need to hard code the target database.  The beauty of the sp_ stored procedure when it resides in the sybsystemprocs database is that you can run it in the context of any database you want, even if you are not currently USEing that database.  For instance...

USE FUBAR
go
YOUR_DB..sp_help
go

will list the tables in YOUR_DB.  This is a neat trick for handling stuff like this.

Alternatively, you can use the object_id() function with a fully qualified object name, e.g. object_id(YOUR_DB..my_sp_name)

Regards,
Bill

CREATE PROCEDURE sp_blocking
                 @user_name varchar(32), @db_name varchar(32), @sp_name varchar(32)
AS
BEGIN
DECLARE @full_obj_name varchar(66)
SET @full_obj_name = @db_name + '..' + @sp_name
SELECT COUNT(*)
  FROM master..sysprocesses sysp2
  JOIN master..sysprocesses sysp1
    ON sysp2.blocked = sysp1.spid
   AND sysp1..id = object_id(@full_obj_name)
  JOIN master..syslogins sysl
    ON sysl.suid = sysp1.suid
   AND sysl.dbname = @db_name
   AND sysl.name = @user
END

Open in new window

0
 
LVL 7

Author Closing Comment

by:Hecatonchires
ID: 31577843
Much nicer than my hack
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
What we learned in Webroot's webinar on multi-vector protection.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question