December 4, 2012 at 1:30 am
Firstly I must tell U I AM new to SQL Server but am learning fast 🙂 We use SP_WHO2 to show users who are liocked by other users and I was wondering is there a way to have the stored procedure run automatically (every 10-15 minutes or so) and if any locked users are found it would then e-mail an Admin (me!) so that action could be taken?
December 4, 2012 at 4:37 am
You could, but you'd be even better served by running a query against sys.dm_exec_requests and looking for blocked processes. This will work better because instead of having to execute a procedure, output the information into a location, such as a temp table, and then find the interesting information, then determine if you want to send an email, you can simply query directly for the interesting information, blocked processes, and then decide to send the email.
Easiest way to do this would be to set it up within SQL Agent. This will let you manage the scheduling. You can have to steps in the job. First step runs the query. If it successfully returns data, run the step that sends email.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 4, 2012 at 4:45 am
Grant Fritchey (12/4/2012)
You could, but you'd be even better served by running a query against sys.dm_exec_requests and looking for blocked processes. This will work better because instead of having to execute a procedure, output the information into a location, such as a temp table, and then find the interesting information, then determine if you want to send an email, you can simply query directly for the interesting information, blocked processes, and then decide to send the email.Easiest way to do this would be to set it up within SQL Agent. This will let you manage the scheduling. You can have to steps in the job. First step runs the query. If it successfully returns data, run the step that sends email.
Sounds good Grant but I have NO idea where to start with this!! I DID find this when I was searching through the forum, would U advise against my using it? http://qa.sqlservercentral.com/scripts/sp_who2/72823/
December 4, 2012 at 4:52 am
I suspect that will work.
If you query sys.dm_exec_requests, you'll see that there is a pretty easy way to spot that you have blocking:
SELECTCOUNT(*)
FROMsys.dm_exec_requests AS der
WHEREder.blocking_session_id > 0;
Now all you have to do is create the two steps in the SQL Agent job, one for running this query, the other for sending email if this query finds data.
The other process will very likely work, but you can simplify it quite a bit.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 4, 2012 at 4:53 am
Thinking about it a little more, you can also look at using extended events. There's actually a blocked_process_report. But, I'm not sure exactly how to modify that. I may have just found a new blog post.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 4, 2012 at 6:28 am
Grant Fritchey (12/4/2012)
I suspect that will work.If you query sys.dm_exec_requests, you'll see that there is a pretty easy way to spot that you have blocking:
SELECTCOUNT(*)FROMsys.dm_exec_requests AS der
WHEREder.blocking_session_id > 0;
Now all you have to do is create the two steps in the SQL Agent job, one for running this query, the other for sending email if this query finds data.
The other process will very likely work, but you can simplify it quite a bit.
Sorry Grant I am a real newbie at this, how do I do the above ? if U can give me a bit of a step-by-step or point me towards somewhere I can read on how to do this that would be great!
December 4, 2012 at 6:37 am
OK I have created a new Agent Job and the 1st step calls the SQl Query "Blocked processes" which consist of:
SELECTCOUNT(*)
FROMsys.dm_exec_requests AS der
WHEREder.blocking_session_id > 0
What do I need on the Advanced Tab and how do I get it to alert me if it finds a blocked process?
many thanks for you assistance with this..
December 4, 2012 at 7:43 am
I have also followed the steps in: http://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/
to setup SQL Server alerts and email operator notifications but don't know how to trigger the alert from the 1st step :-S
December 4, 2012 at 1:09 pm
david.williams 50026 (12/4/2012)
OK I have created a new Agent Job... and the 1st step calls the SQl Query "Blocked processes" which consist of:SELECTCOUNT(*)
FROMsys.dm_exec_requests AS der
WHEREder.blocking_session_id > 0
What do I need on the Advanced Tab and how do I get it to alert me if it finds a blocked process?
many thanks for you assistance with this..
I think you may have alerts confused with notifications. Notifications are what SQL Agent jobs use to notify someone setup as an Operator the exit condition of the job. I can think of a couple ways to do what you're asking. If you have database mail enabled in your SQL Server and just want simple notification:
IF EXISTS (SELECT NULL FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
EXEC msdb.dbo.sp_send_dbmail
@recipients = N'youremail@yourserver.com',
@subject = N'Blocking Detected',
@body = N'Blocking has been detected'
then you could use a query like this to see who's blocking who:
SELECT s.session_id, s.host_name, s.program_name, s.login_name, s.status AS session_status,
db_name(er.database_id) AS database_name,
er.status AS request_status, er.command,
er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
WHERE s.is_user_process = 1
AND er.blocking_session_id <> 0
December 4, 2012 at 1:46 pm
another method, which is the one I use in a couple of production OLTP type systems utilizes Service Broker. The advantage with this is you can set the threshold of how many seconds of blocking causes notification, and once that threshold is reached you will be notified immediately instead of 15 minutes later. Here's a pretty good article explaining how to set that up:
December 5, 2012 at 1:40 am
Chris Harshman (12/4/2012)
another method, which is the one I use in a couple of production OLTP type systems utilizes Service Broker. The advantage with this is you can set the threshold of how many seconds of blocking causes notification, and once that threshold is reached you will be notified immediately instead of 15 minutes later. Here's a pretty good article explaining how to set that up:
Hi Chris,
Firstly many thanks for your assistance with this, it is frustrating to know what I want to do but not be able to do it due to lack of knowledge and I AM pressuruing my company to send me on a SQL course in the new year!!
I have followed through each step at the link that you posted but it doesnt mention how to then get the server to notify you, how do I do that ?
Thanks
DW
December 5, 2012 at 1:41 am
Chris Harshman (12/4/2012)
david.williams 50026 (12/4/2012)
OK I have created a new Agent Job... and the 1st step calls the SQl Query "Blocked processes" which consist of:SELECTCOUNT(*)
FROMsys.dm_exec_requests AS der
WHEREder.blocking_session_id > 0
What do I need on the Advanced Tab and how do I get it to alert me if it finds a blocked process?
many thanks for you assistance with this..
I think you may have alerts confused with notifications. Notifications are what SQL Agent jobs use to notify someone setup as an Operator the exit condition of the job. I can think of a couple ways to do what you're asking. If you have database mail enabled in your SQL Server and just want simple notification:
IF EXISTS (SELECT NULL FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
EXEC msdb.dbo.sp_send_dbmail
@recipients = N'youremail@yourserver.com',
@subject = N'Blocking Detected',
@body = N'Blocking has been detected'
then you could use a query like this to see who's blocking who:
SELECT s.session_id, s.host_name, s.program_name, s.login_name, s.status AS session_status,
db_name(er.database_id) AS database_name,
er.status AS request_status, er.command,
er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
WHERE s.is_user_process = 1
AND er.blocking_session_id <> 0
Hi Chris,
is there a way to combine the two parts so that I get a notification AND it tells me who has locked?
Cheers
DW
December 5, 2012 at 1:47 am
Hi Again Chris,
Looking at what U have below that woudl be enough as I woudl ahve to RDp to the SQL box anyway to clear the block but how woudl I automate this query so it runs every few minutes ?
IF EXISTS (SELECT NULL FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
EXEC msdb.dbo.sp_send_dbmail
@recipients = N'david.williams@charlies.uk.com',
@subject = N'Blocking Detected on Charlies-HO SQL Server',
@body = N'Blocking Detected on Charlies-HO SQL Server'
Thanks
DW
December 5, 2012 at 2:12 am
Sent this a little too soon, figured out how to put it into a SQL Agent Job and schedule it every 2 minutes to run. Now waiting to see if it actaully e-mails me when there is a block. Is there a way to test it easily?
December 5, 2012 at 6:23 am
david.williams 50026 (12/5/2012)
Sent this a little too soon, figured out how to put it into a SQL Agent Job and schedule it every 2 minutes to run. Now waiting to see if it actaully e-mails me when there is a block. Is there a way to test it easily?
Sure. Do something like this:
BEGIN TRANS
UPDATE MyTable
SET Val = 42
WHERE ID = 42;
--ROLLBACK TRANS
That's in one connection. Start the transaction, then, in another connection do:
BEGIN TRANS
UPDATE MyTable
SET Val = 42
WHERE ID = 42;
ROLLBACK TRANS
The first will block the second. Obviously you'll have to substitute the table names, column names and values to something meaningful on your system.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply