SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts


Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts

Author
Message
Keith Mescha
Keith Mescha
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 202
Jesse can you point to a reference on how alert suppression works?
peter_sideris
peter_sideris
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
Marios,

Very nice work. I just implemented this at work. It was an instant hit with our DBA teams.

Thanks,
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12314 Visits: 3766
Thank you, I'm glad it was helpful.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Srinivas-490730
Srinivas-490730
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 13
Marios.

Thanks for posting the MP and the relevant scripts.. It helped me a lot.
I also want to know if we can modify these scripts to alert on long running queries and locking queries.
If so, what is the SQL script that I have to use?
Please advise.

Thanks.
Srini.
Keith Mescha
Keith Mescha
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 202
I have this solution partly implemented. I have recently upgraded quite a few servers to Windows 2008 R2 and SQL 2008 R2 and just found out that the vb script from within SCOM is not writing to the event log. Just started digging but figured I would ping this forum to see if anyone has noticed this behavior.

If you have appreciate any direction. When I find the cause I'll be sure to post back.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96437 Visits: 33013
Keith Mescha (12/9/2010)
I have this solution partly implemented. I have recently upgraded quite a few servers to Windows 2008 R2 and SQL 2008 R2 and just found out that the vb script from within SCOM is not writing to the event log. Just started digging but figured I would ping this forum to see if anyone has noticed this behavior.

If you have appreciate any direction. When I find the cause I'll be sure to post back.


I don't have a specific answer for you, but, since there's probably something wrong with either the script or the parameters, you need to debug to determine what's what. Here's a link to how I figured out debugging. Hopefully that helps.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12314 Visits: 3766
Srinivas-490730 (11/5/2010)
Marios.

Thanks for posting the MP and the relevant scripts.. It helped me a lot.
I also want to know if we can modify these scripts to alert on long running queries and locking queries.
If so, what is the SQL script that I have to use?
Please advise.

Thanks.
Srini.


Sorry for the long delay in responding.

For SQL scripts that monitor locks, see this excellent link:
http://www.simple-talk.com/sql/database-administration/investigating-transactions-using-dynamic-management-objects/

You will need to customize the SCOM objects to reference these scripts accordingly.

For long-running queries, you may want to use something like this:


--http://www.sql-server-performance.com/articles/per/tsql_statement_performance_p1.aspx

SELECT
creation_time
, last_execution_time
, total_physical_reads
, total_logical_reads
, total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
, DatabaseName = DB_NAME(st.dbid)
, SUBSTRING(st.text, (qs.statement_start_offset/2) + 1
,(( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
, qp.query_plan
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY
total_elapsed_time / execution_count DESC;



__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12314 Visits: 3766
Keith Mescha (12/9/2010)
I have this solution partly implemented. I have recently upgraded quite a few servers to Windows 2008 R2 and SQL 2008 R2 and just found out that the vb script from within SCOM is not writing to the event log. Just started digging but figured I would ping this forum to see if anyone has noticed this behavior.

If you have appreciate any direction. When I find the cause I'll be sure to post back.


Unfortunately, I don't have an answer for this at the moment.

There may be some changes in the way WMI interfaces with the new O/S and SQL versions that are breaking the scripts.

Incidentally, we have also upgraded to Windows 2008 R2, and we are still getting the blocking alerts.

However, we have only upgraded one SQL instance to SQL 2008 R2 as well as Windows 2008 R2, and we may be having the same issue as you.

So the issue may be with SQL 2008 R2.

If I come up with something new on this, I will post it here.

Parenthetically, see this link for a memory-leak issue with WMI and Windows 2008 R2:
http://support.microsoft.com/kb/977357

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Keith Mescha
Keith Mescha
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 202
Good point let me check a system with SQL 2008 Non R2 and see what it's doing.
Keith Mescha
Keith Mescha
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 202
Still working through my problem but I did have some updates I thought I would share. So far a couple of things going on here.

1) first off the script I used in the rule is slightly modified from what you posted on this article. However bottom line was that it was failing on the part where it logs to the event log.

So I changed this:
If bWriteLOG = True Then
oAPI.LogScriptEvent("SQLBlocks",999,EVT_WARNING,sAlertDescription)
Else
oAPI.LogScriptEvent("SQLBlocks",998,EVT_INFO,sAlertDescription)
End If

To this:
If bWriteLOG = True Then
CALL oAPI.LogScriptEvent("SQLBlocks",999,EVT_WARNING,sAlertDescription)
Else
CALL oAPI.LogScriptEvent("SQLBlocks",998,EVT_INFO,sAlertDescription)
End If

This article helped http://msdn.microsoft.com/en-us/library/bb437630.aspx

2) The other issue is that on Windows 2008 servers the events are logging to the Operations Manager Event log. On my 2003 servers it goes to Application Event. Log. However My monitor is looking at the App log not Ops Manager. So I'm going to create 2 Monitors unless anyone can think of another way around this?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search