Cómo monitorear los cambios de estado de bases de datos en SQL Server

Vista general del estado de la base de datos

Cada base de datos en un ambiente SQL Server tiene dos estados básicos: disponibilidad completa (estado en línea) o no disponibilidad completa (estado fuera de línea).

SQL Server incorpora y utiliza siete posibles estados de base de datos, los cuales son presentados en la tabla abajo, ordenados por disponibilidad (desde completamente disponible a completamente no disponible), y con una explicación corta para cada estado:

Referencia rápida cuando la transición entre estados de la base de datos es interrumpida

Hay muchas ocasiones cuando una transición fluida entre estados de la base de datos podría fallar. Las transiciones desde estados restaurando, recuperando o recuperación pendiente a estado en línea pueden ser interrumpidos por eventos que paran el proceso previamente activo del respaldo, restauración o recuperación de la base de datos. Estos eventos podrían ser fallas de disco, problemas de conexión de red, archivos corruptos de la base de datos y otros.

Para resolver estos estados de la base de datos, realice las acciones mostradas abajo con precaución y sabiendo que las causas por las que ocurren las interrupciones pueden ser varias (por ejemplo, los problemas ya mencionados durante el proceso de restauración/recuperación de la base de datos, etc.):

• Si la base de datos está en un estado permanente de restauración: corra este scripts para forzar el proceso de recuperación y cambiar el estado de la base de datos a en línea:

  RESTORE DATABASE < database_name >
  WITH RECOVERY
  

• Si la base de datos está en un estado permanente de recuperación:

  • Pare el servicio de SQL Server;

  • Mueva el archivo de registro para esa base de datos (usualmente en c:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\_log.ldf) a otro lugar;

  • Ponga la base de datos problemática fuera de línea;

  • Mueva de vuelta el archivo de registro a su carpeta original y ponga la base de datos en línea.

• Si la base de datos está en un estado permanente de recuperación pendiente: ponga la base de datos fuera de línea, luego en línea:

  ALTER DATABASE < database_name >
  
  SET OFFLINE
  GO
  
  ALTER DATABASE < database_name >
  
  SET ONLINE
  GO
  

• Si es necesario, corra este script si la base de datos está en un estado sospechoso:

  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
  

Determinar los cambios de estados de una base de datos

Los cambios de estados de la base de datos pueden ser determinados programáticamente con PowerShell, decodificando eventos relacionados a estados de bases de datos en y fuera de línea desde el registro de Aplicaciones dentro del Visor de Eventos:

#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;

El resultado debería ser como esto:

Dentro de este archivo de registro filtrado, los momentos cuando una base de datos particular se puso fuera de, o en línea pueden ser fácilmente comparados por marca de tiempo (en la columna TimeCreated).

Cambie el estado de una base de datos en particular de fuera de línea a en línea (PowerShell)

Para configurar estar acción de alerta, descargue el script PowerShell Change_Database_Status.ps1 desde esta localización, y colóquelo en la localización deseada.

Este script recolecta información acerca del estado de una base de datos en particular, y si el estado es fuera de línea, el script la pondrá en línea. Si ya está en línea (normal), el script la terminará.

[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}

Luego, personalice el script descargado, particularmente las cadenas <server_name> y <database_name> con el nombre del servidor y el nombre de la base de datos, respectivamente, marcados en negrita arriba.

Después de descargar y personalizar el archivo de script, establezca la acción de alerta de comando dentro del contador Status Changed, e incluya este script:

powershell.exe "d:\change_database_status.ps1"

Cada vez que el estado de la base de datos monitoreada es cambiado, la acción de alerta llamará al archivo Change_Database_Status y ejecutará el script. También, dentro del perfil de acción de alerta, múltiples acciones de alerta de comando pueden ser incluidas, sólo establezca diferentes servidores y bases de datos en el script PowerShell mencionado.