Pages

Thursday, 12 July 2012

SQL Server: Instant Blocking Alert Using WMI in Your Mailbox

As a DBA, it is important for all of us to get real time alert on various issues in our inbox to be more proactive and to manage the database servers in better way.There are many third party tools which you can easily configure to monitor the servers and to get the alerts. I feel more happy when we implement the required alert by our self to get the alert. we have implemented many custom alerts in our environment. I will share the important ones in this blog.

There are many method that you can adopt to implement custom alert. I have done this using WMI (Windows Management Instrumentation) .WMI is very powerful one to monitor the performance of the servers and application.

Blocking will happen when one process need  to wait for a resource(example lock) which currently using by another process. The first process has to wait till  the second process completes its action and release the resources.

Let us see how we can set up the alert for blocking. The first step to create blocking alert is configure the blocked process threshold values.This is server wide configuration which can be set using sp_configure.The value of blocked process threshold specify the threshold, in seconds,at which blocked process reports are generated.By default no blocked process reports are produces.In simple words, if the value configured as 30 second, we will get alert when a process is blocked for more than 30 seconds. This can be done using the below code.

EXEC sp_configure 'show advanced options',1
GO

RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'blocked process threshold (s)',30
GO

RECONFIGURE WITH OVERRIDE
Another configuration change that we need to do in the SQL server agent. Right click on the agent node , select properties. On the left pane select Alert System. On bottom of that page tick the check box of Replace tokens for all job responses to alert. Read more about the this on MSDN.Restart the SQL server agent service.

The next step is create a simple table in one database.In all our environment we have a database to implement the administrative tasks. This table contains only four fields .One identity column, a date column to store date and time of blocking,a Xml column to store the blocked processed report and a integer column to store the blocking process spid. The script to create this table is available here

The next step is to create a new job.The specialty of this job is, it does not have a schedule.Let us create a job namely DBA_BlockedAlert. In the step add the second part of the script mentioned in here

The final step is to create an alert . Refer below screenshot to create the alert. Mention an appropriate name for the alert. Select alert type as WMI event alert.In the name space add
\\.\root\Microsoft\SqlServer\ServerEvents\INSTANCENAME . 
Replace the instance name with your instance name. 
In the query section add SELECT * FROM BLOCKED_PROCESS_REPORT


In the response page tick the Execute job check box and select the job that we have created earlier.





















Now everything is set. Create a blocking and wait for 30+ seconds to get the alert in your mail box. In case if your are not receiving the alert follow below point to troubleshoot.
  • Check alert history and see number of occurrences. If it is still zero either blocking is not passed 30 seconds or there is some problem with WMI. Check your WMI service status. if it is running , restart the WMI service. 
  • If the number of occurrence is greater than zero, check the table BlockedEvents. If there is no entries, the token replacement may not be happening. Check the   Replace tokens for all job responses to alert of SQL server agent properties. Make sure that you have restarted the SQL Server agent service after making this change.
  • If there is an entry in the BlockedEvents table, there is something wrong with your database mail configuration /mailbox. Check the database mail is working and you have mentioned the correct profile name in the job step.
Hope this will help you to implement custom blocking alert in your environment.


If you liked this post, do like my page on FaceBook 

8 comments:

  1. Can you please put script to create table & Job step. When I clicked on here link to get script it retruns nothing. Can you please provide script for that?


    DKShah

    ReplyDelete
  2. Hi John,

    This is really Nice, I tried for 30 mins (EXEC sp_configure 'blocked process threshold (s)',1800), but is not working for that 30 mins threshold. Is there any limit for the threshold, Please let me know any other way if I want to get the blocking alert if it is blocking for 30 mins.

    Thanks in advance,

    Kasi

    ReplyDelete
    Replies
    1. As far as I know, there is no limit for threshold value, but you can test first with 30 or 60 seconds.Some time it might be problem with WMI or alert settings.Try to troubleshoot with the points given at the end of the points

      Delete
    2. John,

      Thanks for you reply. I have already tested it for 30 and 60 seconds, it is working greatly. And also I have observed that if go for a threshold of 15 minutes (EXEC sp_configure 'blocked process threshold (s)',900) then I am able to get alert after 30 minutes which is abnormal.

      Kasi

      Delete
  3. John,
    I got the below error in job history - Please help
    Message
    Unable to start execution of step 1 (reason: line(4): Syntax error). The step failed.

    ReplyDelete
  4. John,

    I love what you have done. I've implemented it several times. How about scripting the alert to get the current instance name -something like this:

    DECLARE @instance NVARCHAR(64), @namespace NVARCHAR(512);

    SELECT @instance = CONVERT(NVARCHAR, ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER'))
    SET @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instance


    EXEC msdb.dbo.sp_add_alert @name=N'Blocked Process',
    @message_id=0,
    @severity=0,
    @enabled=1,
    @delay_between_responses=600,
    @include_event_description_in=1,
    @category_name=N'[Uncategorized]',
    @wmi_namespace=@namespace,
    @wmi_query=N'SELECT * FROM BLOCKED_PROCESS_REPORT',
    @job_name=N'Utility - Blocked Process Alert'
    GO

    ReplyDelete
  5. Very useful...I tried to put the entire code in the job as an stored proc with parameters like @EmailAlert (1- sends email, 0 doesn't), @Receipients, @ProfileName, but it doesn't seem to work, but if the entire text is put in the job it work...would like to know why ?

    ReplyDelete