How to monitor database status changes in SQL Server

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:

    RESTORE DATABASE < database_name >
  • 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:

    ALTER DATABASE < database_name >
    ALTER DATABASE < database_name >
  • If needed, run this script if the database is in a suspect state:

    EXEC sp_resetstatus < database_name >
    ALTER DATABASE < database_name >
    DBCC CHECKDB (< database_name >)
    ALTER DATABASE < database_name >
    		< database_name >,
    ALTER DATABASE < database_name >

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 first part of the script is fetching events related to OFFLINE status;
Get-WinEvent -FilterHashtable @{logname=’application’;id=5084;} | 
?{$_.message -match "Setting database option OFFLINE"} -ErrorAction SilentlyContinue | 
Out-File d:\DatabaseStatusChange.txt -Append -Force
#The second part of the script is fetching events related to ONLINE status;
Get-WinEvent -FilterHashtable @{logname=’application’;id=5084;} | 
?{$_.message -match "Setting database option ONLINE"} -ErrorAction SilentlyContinue | 
Out-File d:\DatabaseStatusChange.txt -Append -Force
#After data fetching, all events will be parsed into one text file, and every next attempt of executing this script will be appended in the same text file;

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).

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.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') <server_name>
$db = $s.Databases.item('<database_name>')
if ($db.status -eq 'OFFLINE, AUTOCLOSED'){$db.SetOnline()}
else {Break}

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:

powershell.exe "d:\change_database_status.ps1"

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.


February 20, 2017