Database status overview
Every database in a SQL Server environment has two basic states: full availability (online state) or full unavailability (offline state).
SQL Server incorporates and utilizes seven possible database states, which are present in the grid below, ordered by availability (from fully available to fully unavailable), and with a short explanation for each state:
Database state
Description
Availability
Online
Database is functioning normally, and it is available for use.
Available
Restoring
Database is in process of restoration, which means that user initiated the database restoring.
Unavailable (without errors, user-induced)
Recovering
Database is in process of recovering. If succeed, it will change state to online. If process fails, it will change state to suspect.
Unavailable (without errors)
Recovery pending
Recovery process failed in between, but database is not damaged. Further user action is required in order to solve the issue (see in the next paragraph).
Unavailable (error occurred)
Suspect
In this state, there is possibility that database is or was damaged during the recover process. Further user action is required in order to solve the issue.
Unavailable (error occurred)
Emergency
This database state change is user-induced, in order to safely perform maintenance, restore or recovering process on particular database. One note: sysadmin rights are required to manage this database state.
Unavailable (without errors, user-induced)
Offline
Database is not functioning, and is unavailable for use. This state is also user-induced, and it requires further action, in order to change a database state.
Unavailable (without errors, user-induced)
Quick reference when transition between database states is interrupted
There are several occasions when a smooth transition between database states could fail. Transitions from restoring, recovering or recovery pending database states to online state can be interrupted by events that stop previously active processes of database back up, restoring or recovery. These events could be disk failures, network connection issues, corrupted database files and other.
In order to solve these database states, perform actions shown below with caution, and with note that causes why interruptions happen can be various (already mentioned issues during the process of database restoration/recover etc.):
If the database is in a permanent restoring state: run this script, to force the recovering process and set database state to online:
If the database is in a permanent recovering state:
stop SQL Server service;
move the log file for that database (usually in c:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\<database_name>_log.ldf) to another place;
take problematic database offline;
move back the log file to its original folder and take database online.
If the database is in a permanent recovery pending state: take database offline, then online:
If needed, run this script if the database is in a suspect state:
Determine a database(s) status changes
Database status changes can be determined programmatically with PowerShell, by parsing events related to offline and online database(s) states from Application log within Event Viewer:
The result should appear like this:
Within this filtered log file, moments when particular database went offline or online can be easily compared by timestamp (in TimeCreated column).
To constantly monitor database status change, include the script from above within SQL Server Agent job (refer to this article in order to create mentioned job), if needed.
Monitoring database status change with ApexSQL Monitor
As SQL Server performance monitoring tool, ApexSQL Monitor is continuously auditing status of all databases and their changes present on a monitored SQL server instance, with corresponding metric and alerts.
Information about database status change from Dashboard
On the left pane, when All instances is selected, the information on all databases status will appear in the Dashboard’s grid:
In this case, shown above, all databases are in function.
If some of the databases on the selected instance changed the status in some moment, the Databases counter on the top will show the corresponding number of alert(s), and alert icon will appear in the grid, like shown below:
Select the particular instance in the left pane, scroll down within Overview tab, and check all databases, for their status, database size, log size and related alerts:
In the picture above, there is an alert (or alerts) related to the Test database, which means that Test database’s status has changed.
Also, information about status and related alerts about databases are present in Database performance tab, along with information like recovery model, compatibility level etc.:
Status changed special performance counter
Placed under Database metrics, “Status changed” is the special performance counter, which actually tracks these events:
Status changed counter can be set to particular databases by clicking icon close to the metric name, and Database metric dialog will appear:
Resolving alerts related to Status changed counter
To examine and resolve alerts related to the database status change, go to the Alerts view:
In the General view, present on the picture above, it can be easily seen on which instance particular database (Test) changed its status. Also, the previously mentioned seven database statuses are present on the graph to show the transition between the states.
The selected alert represents the moment when Test database went offline, and the next shows when the same database changed status to online, with exact dates and times.
Automating alerts for database status changes
During monitoring of database status changes, to be effectively notified when a database changes status, set the email profile and/or use the custom command alert action, to make sure that particular database is always functioning.
Change the status of the particular database from offline to online (PowerShell)
In order to set this alert action, download Change_Database_Status.ps1 PowerShell script from this location, and place it on a desired location.
This script collects information on particular database’s status, and if the status is offline, the script will set it online. If it is already online (aka normal), the script will terminate.
Next, customize the downloaded script, particularly <server_name> and <database_name> strings with valid and desired ones, bolded above.
After downloading and customizing the script file, set the custom command alert action within the Status Changed counter, and include this script:
Every time the monitored database status is changed, the alert action will call the Change_Database_Status file and execute the script. Also, within alert action profile, multiple custom command alert actions can be included, just set different servers and databases in mentioned PowerShell script.
Downloads
Please download the script(s) associated with this article on our GitHub repository.
Please contact us for any problems or questions with the scripts.
The post How to monitor database status changes in SQL Server appeared first on Solution center.