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:

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 >
  WITH RECOVERY
  

• 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 >
  
  SET OFFLINE
  GO
  
  ALTER DATABASE < database_name >
  
  SET ONLINE
  GO
  

• If needed, run this script if the database is in a suspect state:

  EXEC sp_resetstatus < database_name >
  GO
  
  ALTER DATABASE < database_name >
  
  SET EMERGENCY
  GO
  
  DBCC CHECKDB (< database_name >)
  GO
  
  ALTER DATABASE < database_name >
  
  SET SINGLE_USER
  WITH
  
  ROLLBACK IMMEDIATE
  GO
  
  DBCC CHECKDB (
  		< database_name >,
  		REPAIR_ALLOW_DATA_LOSS
  		)
  GO
  
  ALTER DATABASE < database_name >
  
  SET MULTI_USER
  GO
  

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>')
$db.status
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.