?
Solved

sql 2008 standard - alert for extreme locking (blocking)

Posted on 2012-03-12
3
Medium Priority
?
450 Views
Last Modified: 2012-03-14
Does anyone know of a script to use for an alert in sql 2008 standard for Extreme Deadlocks (blocking).  Only this type of alert.

I have the use of quest tools, spotlight in particular. But I need to be proactive I need to send an alert for extreme locks causing blocking to our helpdesk.
Does anyone have any advice? or a script i can create. Thanks in advance.
0
Comment
Question by:Amanda Walshaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 37712762
what do you mean "Extreme Deadlocks (blocking)." ? Deadlocks?


check:
   
Deadlock Notifications in SQL Server 2005
By Patrick LeBlanc
 http://www.sqlservercentral.com/articles/Administration/3243/
0
 

Author Comment

by:Amanda Walshaw
ID: 37716929
I mean locking causing blocks, which affects users unable to save data in the database until the block is cleared.
I need to create an alert for blocks, for our helpdesk to react to.
I will review the article you nominated.
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 37717488
here you are:

here you are:


Email Sqlserver Block monitor script
http://hassanszone.wordpress.com/2011/03/19/sqlserver-block-monitoring-mail-reporting-using-sqlagent-job/

/**—Create an Sqlserver Agent Job to report Blocking events and Mail to DBA team—-**/
 
declare @subj varchar(max),
 @cmd varchar(max),
 @file varchar(100)
 select    @subj = @@servername  + ‘—Encountered Blocking sessions’,
 @cmd = ‘Print ”Below listed Session_Id are blocked by.
 ————————————–
 

 select r.Session_id as SID,
 r.Start_time,
 DBName = db_name(database_id),
 r.Command,
 substring(st.text,
 r.statement_start_offset/2+1,
 (case when r.statement_end_offset = -1 then len(convert(nvarchar(max), st.text)) * 2 else r.statement_end_offset end – r.statement_start_offset)/2+1) As BlockedQuery,
 r.Blocking_session_id,
 s.Memory_usage,
 s.Host_name,
 s.Program_name,
 s.Login_name
 from sys.dm_exec_requests r
 join sys.dm_exec_connections c on r.session_id = c.session_id
 join sys.dm_exec_sessions s on s.session_id = r.session_id
 cross apply sys.dm_exec_sql_text(r.sql_handle) st
 where r.session_id > 50 and r.blocking_session_id>1
 order by r.session_id

 
exec msdb.dbo.sp_send_dbmail
 @profile_name = ‘sql – dba’,
 @recipients = ‘shamsul.hassan@hassanszone.com’,
 @subject = @subj,
 @body= ”,
 @query = @cmd,
 @query_result_separator = ‘|’,
 @query_result_no_padding = 1
 
/**—Create an Sqlserver Alert to trigger Blocking alerts Mail to DBA team—-**/
 
USE [msdb]
 GO
 EXEC msdb.dbo.sp_update_alert @name=N’Request Blocked Processes’,
 @message_id=0,
 @severity=0,
 @enabled=1,
 @delay_between_responses=0,
 @include_event_description_in=0,
 @database_name=N”,
 @notification_message=N”,
 @event_description_keyword=N”,
 @performance_condition=N’SQLSERVER:General Statistics|Processes blocked||>|1',
 @wmi_namespace=N”,
 @wmi_query=N”,
 –        @job_id=N’bc259ae4-d96b-4146-a372-1343e91cb5de’,
 @job_name=’DBA_BlockingCheck_Report’
 GO

Open in new window

0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

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