Instance status

Job details

Name:	Instance status
Platform:	Sqlserver
Category:	Availability
Description:	This alert checks if the instance has been restarted since the last check
Long description:
Version:	1.3
Default schedule:	* * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Name	Default value	Description
instance time down threshold	0	A warning/alarm is triggered if the number of seconds the instance was stopped is greater than this parameter value.
return status	2	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the instance is restarted.

Purpose: The purpose of this job is to monitor and alert on SQL Server and Azure SQL Managed Instance restarts, efficiently managing instances by identifying unexpected downtimes and changes in the server host.
Why: This job is essential to ensure the high availability and reliability of SQL Server and Azure Managed Instances. Monitoring restarts help in proactive maintenance and troubleshooting, preventing potential disruptions in database services.
Manual checking: You can manually check this information in the database through the following SQL commands:

SELECT @@servername AS "Server name", MIN(login_time) AS "Started" FROM master.dbo.sysprocesses

The job uses a set of SQL procedures and tables to track and report the status of instances:

A table (dbw_instance_status_tab) records the instance start time, the last check time, and the host name.
A procedure (dbw_instance_status_proc) calculates the downtime and updates the table accordingly. It triggers a warning or alarm based on the set thresholds for the downtime duration.

This job has dependencies which include:

Its own monitoring procedure (dbw_instance_status_proc).
A table (dbw_instance_status_tab) that holds the downtime and last checked datetimes.

The job includes an upgrade path from versions 1.1 and 1.2, where:

Incase of an upgrade, the same procedures are modified to reflect possible changes in the way downtime calculations or status updates are handled.

The alerts generated by this job are configurable:

“instance time down threshold” – This parameter sets the threshold for the downtime duration before a warning or alarm is triggered.
“return status” – Indicates the severity of the alert, whether it is OK (0), WARNING (1), or ALARM (2).

The default report for this job includes:

This information provides quick insights into the instance start time directly related to any noticed resets or reboots.

The default scheduling for the job is:

Every minute, as per the default schedule in the XML configuration (\<default-schedule>* * * * \</default-schedule>).

This frequent checking ensures almost real-time monitoring and alerting on the status of the database instances.