Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Find if a specific sp is blocking

Posted on 2009-05-04
9
Medium Priority
?
957 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
Hire Technology Freelancers with Gigs

Work with 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.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

877 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