Fiona Gazeley
My feedback
7 results found
-
153 votes
Adam
responded
We’ve made some improvements in this area but will leave it open so you can continue to give us your suggestions
An error occurred while saving the comment An error occurred while saving the comment Fiona Gazeley
commented
Hi Stephen
As you say, you will need an additional machine for the Base Monitor, however it doesn't need SQL Server installed on it as you can have the Base Monitor connect to a SQL Monitor repository database that resides on your clustered SQL Server (i.e. the Base Monitor Service can point to a SQL Database on a different machine).
Alternatively if you don't want SQL Monitor repository database running on the cluster, you could use SQL Server express edition on the Base Monitor machine as long as your purge window is quite small and/or collection frequency reduced (we can send you details on how to edit your configuration file to do the latter).
If you would like addition details on any of the above, either respond here or drop me a line at fiona.gazeley@red-gate.com
Hope that's of some help, Fiona. -
12 votes
An error occurred while saving the comment Fiona Gazeley
commented
Hi Steve
Thanks for your idea. This is exactly the type of thing we have introduced Custom Metrics for in SQL Monitor version 3.0 (http://www.red-gate.com/monitor).
The idea behind Custom Metrics is that you can measure things that are important to you.
To show you how it might work I have put together some T-SQL that would do the job. I'm sure it can be improved or be written in a nice way, but it should show the idea:You would create a custom metric with the following T-SQL.
It will return 0 is a job hasn't started, if it's completed, or if it's running within the expected timescales. It will return 1 if it's still running and outside the expected timescales.You would need to edit the T-SQL to contain the name of your job and the expected start and finish hours / minutes.
-- Code to examine if a job has overrun to a specific time
DECLARE @todaysdate DATETIME, @job_name NVARCHAR(50),
@expected_start_hour INT, @expected_start_minute INT,
@expected_finish_hour INT, @expected_finish_minute INT
SELECT @todaysdate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())),
-- ******************************************************
-- FILL IN DETAILS SPECIFIC TO YOUR JOB
-- ******************************************************
@job_name = 'TestJob',
@expected_start_hour = 17,
@expected_start_minute = 0,
@expected_finish_hour = 17,
@expected_finish_minute = 2
-- Expected outcomes
-- 0=job in expected timescales
-- 1=job has overrun
SELECT COUNT(*) AS JobOverRunning
FROM msdb..sysjobs j
WHERE
j.name = @job_name
AND EXISTS
(
SELECT TOP 1 *
FROM msdb..sysjobsteps sj
INNER JOIN msdb..sysjobactivity ja ON ja.job_id = j.job_id
WHERE sj.job_id = j.job_id
AND ja.start_execution_date >= DATEADD(mi, @expected_start_minute, DATEADD(hh, @expected_start_hour, @todaysdate))
AND ja.stop_execution_date IS NULL -- only include stuff that is still running
AND GETDATE() >= DATEADD(mi, @expected_finish_minute, DATEADD(hh, @expected_finish_hour, @todaysdate))
ORDER BY start_execution_date
)In this example the job is called "TestJob" and it starts at 17:00 (5pm) and is expected to be complete by 17:02 (5.02pm).
You would then add an alert on this job which would be raised if the value goes *above* 0 (I.e. it will alert when the job returns 1). This indicates it's gone over the expected time-scales.
I hope that gives you some idea of the types of things that can be done using custom metrics.
Best regards, Fiona
-
32 votes
An error occurred while saving the comment Fiona Gazeley
commented
Thank you very much for your feedback.
-
65 votes
Daniel Rothig
responded
For SQL Server perf counters, you can create a Custom Metric against sys.dm_os_performance_counters. For Machine perf counters, you have to use CLI at the moment
An error occurred while saving the comment Fiona Gazeley
commented
Hi James. I don't know exactly what perfmon counters you are interested in, but if they are available through SELECT * FROM sys.dm_os_performance_counters, then from version 3.0 of SQL Monitor, you will be able to Analyse this data by using the Custom Metrics feature.
Custom Metrics can collect, alert and analyse on data retrieved through T-SQL. If you are interested in trying this out, we have an Early Access Program (EAP) which you can download here http://www.red-gate.com/products/dba/sql-monitor/eap-download
Thanks, Fiona -
19 votesDaniel Rothig
responded
An error occurred while saving the comment Fiona Gazeley
commented
Max - thank you for your feedback.
I don't know if you have been involved with our Early Access Program (EAP), but in v3.0 we have a new feature called Custom Metrics. Within that you can measure a rate of change which may go somewhat towards what you are after. Custom Metrics are metrics you want to add to the system (over and above the defaults) and you do this by entering T-SQL that returns the value you are interested in.
If you are interested, you can access the EAP here http://www.red-gate.com/products/dba/sql-monitor/eap-download
Thanks, Fiona -
36 votes
An error occurred while saving the comment Fiona Gazeley
commented
Thank you for your feedback Rusty.
-
6 votes
An error occurred while saving the comment Fiona Gazeley
commented
Thank you for your feedback Don.
If you are interested in altering the purge window only then that can be done in-product as follows:
Configuration Tab
Data Purging
Drop these values down to the smallest practicable time frame especially the high volume data.
Thanks.