Solved

sql 2008 standard - alert for extreme locking (blocking)

Posted on 2012-03-12
3
445 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 500 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

695 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