Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»»

Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts Expand / Collapse
Author
Message
Posted Tuesday, March 23, 2010 11:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 22, 2013 3:31 PM
Points: 63, Visits: 182
Jesse can you point to a reference on how alert suppression works?
Post #888400
Posted Friday, July 9, 2010 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 9, 2010 9:55 AM
Points: 1, Visits: 0
Marios,

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

Thanks,
Post #950063
Posted Monday, July 12, 2010 7:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:31 AM
Points: 1,864, Visits: 3,612
Thank you, I'm glad it was helpful.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #950768
Posted Friday, November 5, 2010 12:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 10, 2010 2:25 PM
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.
Post #1016712
Posted Thursday, December 9, 2010 8:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 22, 2013 3:31 PM
Points: 63, Visits: 182
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.
Post #1032484
Posted Thursday, December 9, 2010 8:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:27 PM
Points: 13,776, Visits: 28,178
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1032488
Posted Thursday, December 9, 2010 9:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:31 AM
Points: 1,864, Visits: 3,612
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;



__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1032528
Posted Thursday, December 9, 2010 9:34 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:31 AM
Points: 1,864, Visits: 3,612
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


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1032543
Posted Thursday, December 9, 2010 9:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 22, 2013 3:31 PM
Points: 63, Visits: 182
Good point let me check a system with SQL 2008 Non R2 and see what it's doing.
Post #1032544
Posted Tuesday, December 21, 2010 3:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 22, 2013 3:31 PM
Points: 63, Visits: 182
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?
Post #1037998
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse