Solved

Find if a specific sp is blocking

Posted on 2009-05-04
9
913 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Cloud-based technologies and services will continue to grow in popularity in 2017 thanks to the simple, scalable and cost-effective solutions they deliver. Here are three areas where cloud adoption is poised to really take off.
An analysis of the phishing scam that has been affecting Google users, along with steps to take for protection, as well as what to do if you receive one of the emails.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now