Page tree

SQL Elements performs Health Checks on your SQL Server instances to alert you about the availability, performance, and other critical issues of your environment. 

The following table describes what type of metrics are measured, their status, when alerts are raised, their criticality levels, the collection frequency for each metric, and if email alerts are sent. 

MetricCategoryStatusAlert raised when/threshold

Collection

Interval

Email Alert
Instance statusAvailability

up

down

critical: instance downRuns every half minuteYes
Instance response timeAvailability

response time ok

slow response time

warning: 2000 ms

critical: 5000 ms

Runs every half minuteYes
Database statusAvailability

normal

suspect

offline

inaccessible

emergency mode

critical: suspect, offline, inaccessible, emergency mode

 

Runs every dayYes
Database never backed upDisaster recovery

backed up

never backed up

 

critical: never backed up

 

Runs every dayNo
Database not backed up recentlyDisaster recovery

backed up recently in the last 7 days

no full backup in 7 days or more

no differential backup in 7 days or more

critical: no full backup in 7 days or more

warning: no differential backup in 7 days or more

Runs every dayNo
Databases with no CHECKDB integrity checkDisaster recoveryCHECKDB never runcritical: CHECKDB neverRuns every dayNo
Databases with no recent CHECKDB integrity checkDisaster recovery

run CHECKDB in the last 7 days

no recent CHECKDB in more than 7 days

no recent CHECKDB in more than 30 days

no recent CHECKDB in more than 7 days

no recent CHECKDB in more than 30 days

Runs every dayNo
Drives at riskStorage capacity

below 75 % capacity

between 75% and 90%

above 90%

warning: between 75% and 90%

critical: above 90%, server drive is at risk of being full soon

Runs hourlyYes
Databases at riskStorage capacity

below 75 % capacity

between 75% and 90%

above 90%

warning: between 75% and 90%

critical: above 90%

Runs hourlyYes
Database Auto shrink enabledConfiguration check

enabled

disabled

warning: enabledRuns every dayNo
Instance xp_cmdshell enabledConfiguration check

enabled

disabled

critical: enabledRuns every dayNo
Database Tempdb files not all the same sizeConfiguration check

same size

not the same size

warning: not the same sizeRuns every dayNo

Refresh collection

SQL Elements allows you to refresh these metrics at any time from the instance view. For more information about this option, please refer to  Viewing instance information

Definitions

Availability Data

Availability Data refers to the instance status (up or down), instance response time, and database status (normal, suspect, offline, inaccessible or emergency mode). 

Capacity data

Capacity data refers to storage capacity of your drivers and databases of your environment. SQL Elements lets you know if your drives or databases are at risk of being full soon.

Configuration Data

The following configuration data is collected:

  • Auto Shrink  SQL Server automatically shrinks databases to remove unused space. In some cases shrinking databases may not be the best option for your environment since it may cause fragmentation.  This configuration check allows you to know if the Auto Shrink option is enabled or disabled.
  • Xp_cmdshell  Xp_cmdshell is essentially a mechanism to execute arbitrary calls into the system using either the SQL Server context (i.e. the Windows account used to start the service) or a proxy account that can be configured to execute xp_cmdshell using different credentials.  When xp_cmdshell is enabled, the user could escalate his/her privileges to sysadmin. It is important to be aware which instances have the xp_cmdshell option enabled for security reasons. This configuration check allows you to know if xp_cmdshell is enabled or disabled in an instance. 
  • Tempdb files  It is important to configure the files at the same initial size and with the same growth settings so SQL Server can write the data across the files as evenly as possible. The data files for tempdb should all be the same size and large enough to handle the workload of 24 hours a day. This helps SQL Server distribute the work evenly across the tempdb data files.  This configuration check allows you to know if tempdb files are the same size or not.

Disaster recovery

Disaster recovery is a process to help you recover information if disaster occurs. Disaster recovery planning refers to the preparation that must occur in response to a disaster. SQL elements provides Health Checks that give you information of your latest backups, alerting you if any of your databases have not been backed up recently.

CHECKDB  Checks the logical and physical integrity of all the objects in the specified database. If corruption has occurred for any reason, the DBCC CHECKDB command will find it, and tell you exactly where the problem is.

For more term definitions, refer to Definition of terms.