SQL Job for Monitoring Blockings on SQL Server [duplicate]

Question

Is it anyway possible to create a SQL Job for monitoring blocking on SQL Instance, if the blocking are still happening after some interval of time?

We are using SCOM Monitoring tool to monitor events from the event viewer and generated alert on the basis of this that. Kindly guide as to what can I do generate an event in case of blocking persists for more than a minute.

I tried enabling the blocking process threshold and created blocking, but I see no event either in event viewer or in SQL Error Logs. We are using Sql Server 2008 R2 Enterprise.

Thanks in advance.

Answer 1

One way to do this is to set Blocked Process Report to 60 seconds, then an event will be raised by SQL. This event can be picked up in a number of ways.

1) Via SQL Profiler, this isnt a great way as you have to run trace etc to find them

2) Via Extended Events, again not great you would have to have a job constantly polling the target.

3) Via Event Notifications. This is a great way and I think the best way of doing it. You can create an Event Notification and a Service to direct the notification into a Service Broker queue, then you can process the queue messages with a stored procedure. It is a fantastic way of getting a free monitoring tool for yourself.

http://i.zdnet.com/whitepapers/Quest_How_to_Use_SQL_Server_Extended_Events_and_Notifications_to_Proactively_Resolve_Performance_Issues.pdf

Just follow page 24, but change the event from ERRLOG to BLOCKED_PROCESS_REPORT

By Default blocked process report is set to 0 (disabled) so to enable you will have to:

SP_CONFIGURE'blocked process threshold',60;
GO
RECONFIGURE;
GO

Answer 2

There are lots of methods you can use to find the same:

1. You can use the DMV's like sys.dm_exec_requests DMV and Sys.dm_os_waiting_task

2. SQL Server Management Studio Reports

3. SQL Server Profiler

4. Also, you can download Adam Mechanics stored proc SP_WHOISActive and use that in sql job as required to find necessary info.

http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

5. Additionally below link is very useful from codeplex which i've been using and is quite helpful:

http://sqlblockedprocessmon.codeplex.com/