July 31, 2008 at 12:45 pm
Hello all,
I am trying to create a job which run every 15 minutes on our sql server and check for all the processes....and send us notification if any process on that server is getting blocked...in our email we need the following details
1.spid
2.dbname
3.which command it is running
4.is getting blocked by which process id
Here is the script I wrote, but it only gives the information on spid and dbname if it is getting blocked..
-------------
DECLARE @Blocked int
SET @Blocked = (select count(*) from sysprocesses where blocked<>0)
IF @Blocked > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'profilename',
@recipients = 'recipients@domain.com',
@query = 'select spid,db_name(dbid) from sysprocesses where blocked<>0' ,
@subject = 'Process ids which are getting Blocked on wpsqlp3\sql1',
@attach_query_result_as_file = 1 ;
END
-----------------
I put this script in a job and i m running this job every 15 minutes...if this job is failed(i.e. if some process on this server is getting blocked then it gives it's spid and dbname....but i also want details
like
(a) which command it is running while it is getting blocked and
(b)is getting blocked by which process id
Thanks in advance..
July 31, 2008 at 12:52 pm
Well, you'll soon find that you're going to get annoyed by the emails as it will happen frequently ... there is a certain amount of blocking that is ok and acceptable.
What I've done in the past is use sp_blocker_pss80 from Microsoft. I just created a simple VBScript that will run it on a specified internal and save the results to disk for later analyzing/historical troubleshooting. This will give you everything and anything you ever wanted to know about blocking. You could even send off email notifications if you'd like ... Yet at first review, the information is somewhat difficult to understand. Until you get a hang of how to interpret the data, use Sherlock to analyze the output.
July 31, 2008 at 1:21 pm
Hi Adam,
First of all thanks for your reply...we just want to run this job for temp. basis...so We are ok if our mails get full because of some temp. blocking....We are having one application and when they start that application at that time it just start blocking every other processes...we just want to know that which is first process which is getting blocked and by which process...Is there any way for that....or is there any other solution for that...
I am sure that this is happening because some bad database level programming..but they need our help to find that out...
Is there anything you can think about that..
Thanks
July 31, 2008 at 1:27 pm
See the attached ... modify appropriately to filter on blocked sessions, but this will tell everything you wanted to know about a session.
I am leaving for the weekend, but let me know if you need help building something as I could whip up a nice procedure for you if you haven't made much progress by Monday.
Good luck
July 31, 2008 at 1:33 pm
Thank you Sir....I really appreciate
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply