Solved

Find if a specific sp is blocking

Posted on 2009-05-04
9
919 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
  • 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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 500 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

When you’re making plans to join the modern business race, you should analyze various details that may affect your results. Nowadays, millions of businesses are trying to grow into established and appreciated professional enterprises.
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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