May 27, 2009 at 2:46 pm
When does data get added to the central DB? We have this setup and we saw data get added once, but it hasn't worked since then. The script is running and creating the event in the Application log, but we haven't seen data appended to the central Db?
Any help would be appreciated.
May 28, 2009 at 7:29 am
marc.schmieder (5/27/2009)
When does data get added to the central DB? We have this setup and we saw data get added once, but it hasn't worked since then. The script is running and creating the event in the Application log, but we haven't seen data appended to the central Db?Any help would be appreciated.
If the event is being generated in the App Log, it's possible that the procedure entering the data in the analysis db is failing. But then, the fact that an event is being created means that the procedure does return a true value for bWriteLOG:
If cmd.Parameters("@WriteLog").Value = True Then
bWriteLOG = True
End If
So I'm actually puzzled why this is happening. I would suggest that you run the vbscript directly on your server to dig deeper.
Lower these 2 parameters in the script to 1 minute to ease testing:
iWaitInMinutes = 10
iAlertFreq = 10
Run the blocking-condition script indicated in the article on the server instance in question. After about a minute, run the vbscript from the command line.
Check whether anything has been added to your db. If not, troubleshoot through the script by adding msgbox debug lines close to the call to the stored procedure.
Sorry, I can't think of any reason offhand why this would not work. Please post any more info that you may have available.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 28, 2009 at 1:17 pm
Marios,
We tried to run the script form the command line and he get an error. Are we supposed to use the computer name as a parameter on the cmd line? Ex. Cscript SQLBlocking.vbs SERVERNAME?
Also, we are no long seeing the event being created either. So all in all we've seen the data written to the central db once on 5/26 and we have seen the alert occur 2 times, but never when we expect it to. We have set the all parameters to 1min so we should be seening something after this 2-3 mins or so. At this point we don't know what else to check. Any additional advice would be appreciated.
May 28, 2009 at 1:34 pm
marc.schmieder (5/28/2009)
Marios,We tried to run the script form the command line and he get an error. Are we supposed to use the computer name as a parameter on the cmd line? Ex. Cscript SQLBlocking.vbs SERVERNAME?
Also, we are no long seeing the event being created either. So all in all we've seen the data written to the central db once on 5/26 and we have seen the alert occur 2 times, but never when we expect it to. We have set the all parameters to 1min so we should be seening something after this 2-3 mins or so. At this point we don't know what else to check. Any additional advice would be appreciated.
The parameter should be the server/instance in question, eg. myServerName/myInstanceName.
What is the error and where in the script does it occur?
Also, is this the SQL-2005 or the SQL-2000 script?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 22, 2009 at 1:50 am
How do i import the 2 .rpc files?
March 23, 2010 at 11:51 am
Jesse can you point to a reference on how alert suppression works?
July 9, 2010 at 9:56 am
Marios,
Very nice work. I just implemented this at work. It was an instant hit with our DBA teams.
Thanks,
July 12, 2010 at 7:58 am
Thank you, I'm glad it was helpful.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 5, 2010 at 12:10 pm
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.
December 9, 2010 at 8:29 am
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.
December 9, 2010 at 8:37 am
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[/url] 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2010 at 9:20 am
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:
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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 9, 2010 at 9:34 am
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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 9, 2010 at 9:36 am
Good point let me check a system with SQL 2008 Non R2 and see what it's doing.
December 21, 2010 at 3:19 pm
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?
Viewing 15 posts - 46 through 60 (of 73 total)
You must be logged in to reply to this topic. Login to reply