Solved

Find if a specific sp is blocking

Posted on 2009-05-04
9
903 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
Comment Utility
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
Comment Utility
You might want to tell us the Sybase product and version...

Regards,
Bill
0
 
LVL 7

Author Comment

by:Hecatonchires
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 7

Author Comment

by:Hecatonchires
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Much nicer than my hack
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Return results from Sybase DB Query with Powershell 7 1,494
dbisql 5 548
SIMPLE QUESTION BACKUP AND RESTORE ORACLE DATABASE 13 80
sybase 3 26
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
A safe way to clean winsxs folder from your windows server 2008 R2 editions
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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

11 Experts available now in Live!

Get 1:1 Help Now