When we have a cluster instance restart, sqldm doesn’t realize this has happened – it sees it if the physical server (node) that owns the instance has changed and uses this to trigger an alert, but it doesn’t seem to realize if an instance has restarted in place (unless it happens to catch the instance while it’s actually restarting). It could find out a couple of different ways if the instance has restarted since last query and alert appropriately.
For example, something like:
– Check the SQL Agent log to see if there have been restart/connectivity related events
– Check the age of the tempdb database – it’s recreated on restart and would be an indicator
If a restart has occurred, it should be an option to alert on this.
Response
Tep Chantra over 2 years ago
In my environment, I’ve created a custom counter for this kind of scenario. The custom counter is based on a T-SQL script and basically queries sqlserver_start_time from sys.dm_os_sys_info. The custom counter is based on a T-SQL script, which requires a single numeric value to be returned. In this case, I have it return the number of minutes since the last restart. The lower the number, the worst the condition is.
The query used for the custom counter is the following:
SELECT DATEDIFF(mi,sqlserver_start_time,GETDATE()) FROM sys.dm_os_sys_info
With this, I can then be alerted if SQL Server happens to have been restarted in between scheduled refreshes of SQL diagnostic manager. Perhaps this can hold you over until SQLDM implements something internal.
Tep Chantra over 2 years ago in reply to Tep Chantra
Attaching an export of the custom counter that I use in my environment in case anyone wants to take a look at it.