Solved

Find if a specific sp is blocking

Posted on 2009-05-04
9
933 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Suggested Solutions

Title # Comments Views Activity
Visual Studio 2010 with SyBase 3 852
installl replication server 15.7 x64 on Windows 8.1 with update 1 11 603
Clean up a mailbox 5 162
Sybase conversion 2 76
After-hours service is a fact of life for most MSPs. While not the most pleasant aspect of the job, there are ways to make after-hours servicing a more profitable and organized enterprise.
Read our guide on how to survive being on-call.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 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