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:

Estado de la base de datos Descripción Disponiblidad
Enlínea La base de datos está funcionando normalmente, y está disponible para su uso. Disponible
Restaurando La base de datos está en proceso de restauración, lo cual significa que el usuario inició la restauración de la base de datos. No disponible (sin errores, inducido por el usuario)
Recuperando La base de datos está en proceso de recuperación. Si es un éxito, cambiará el estado a en línea. Si el proceso falla, cambiará el estado a sospechoso. No disponible (sin errores)
Recuperación pendiente El proceso de recuperación falló en medio, pero la base de datos no está dañada. Se requiere la acción del usuario para resolver este problema (vea en el siguiente párrafo). No disponible (ocurrieron errores)
Sospechosa En este estado, hay la posibilidad de que la base de datos esté o haya sido dañada durante el proceso de recuperación. Se require la acción del usuario para resolver este problema. No disponible (ocurrieron errores)
Emergencia Este cambio de estado de la base de datos es inducido por el usuario para realizar procesos de mantenimiento, restauración o recuperación en una base de datos particular. Una nota: derechos de sysadmin son requeridos para administrar este estado de la base de datos. No Disponible (sin errores, inducido por el usuario)
Fuera de línea La base de datos no está funcionando y no está disponible para su uso. Este estado también es inducido por el usuario, y requiere la acción del usuario para cambiar el estado de la base de datos. No Disponible (sin errores, inducido por el usuario)

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

Para monitorear constantemente los cambios de estados de la base de datos, incluya el script de arriba dentro del trabajo de SQL Server Agent (refiérase a este artículo para crear el trabajo mencionado), si es necesario.

Monitoreando cambios de estados de la base de datos con ApexSQL Monitor

Como herramienta de desempeño de SQL Server, ApexSQL Monitor está continuamente auditando los estados de todas las bases de datos y sus cambios presentes en una instancia SQL Server monitoreada, con sus correspondientes métricas y alertas.

Información acerca del cambio de estado de la base de datos desde el panel principal

En el panel izquierdo, cuando All instances es seleccionado, la información del estado de todas las bases de datos aparecerá en la cuadrícula del panel de Group:

En este caso, como se muestra arriba, todas las bases de datos están en función.

Si algunas de las bases de datos dentro de la instancia monitoreada cambiaran de estado en algún momento, el ícono de alerta aparecerá en la cuadrícula, como se muestra abajo:

Seleccione la instancia en particular en el panel izquierdo, o directamente desde el panel principal Group, haga clic en el contador de alerta en el panel Database, para revisar alertas presentes:

En el subsistema Alerts, hay una alerta relacionada a la base de datos Test, la cual puede ser vista dentro de la columna Instance, y significa que el estado de esa base de datos Test ha cambiado:

Contador de desempeño especial Status changed

Debajo de Metrics, en el subsistema Configuration, “Status changed” es el contador especial de desempeño, el cual realmente rastrea estos eventos:

El contador Status changed puede ser configurado para bases de datos particulares haciendo clic en el ícono cerrar para el nombre de la métrica, y una lista de bases de datos aparecerá en la cuadrícula:

Resolver alertas relacionadas al contador Status changed

Para examinar y resolver las alertas relacionadas a los cambios de estado de la base de datos, vaya al subsistema Alerts y seleccione la alerta, como se muestra abajo:

En esta vista, presente en la imagen de abajo, puede ser fácilmente visto en qué instancia y base de datos (Test) se cambió el estado. También, algunos de los estados previamente mencionados de la base de datos están presentes en el gráfico para mostrar la transición entre estados.

La alerta seleccionada representa el momento cuando la base de datos Test se puso fuera de línea, con la fecha y el tiempo exactos.

Automatizando alertas para cambios de estados de bases de datos

Durante el monitoreo de los cambios de las bases de datos, para ser efectivamente notificado cuando una base de datos cambia de estado, configure el perfil de correo electrónico y/o use la acción de alerta de comando personalizada, para asegurarse de que esa base de datos en particular está siempre funcionando.

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.

Descargas

Por favor descargue los scripts asociados con este artículo en nuestro repositorio GitHub.

Por favor contáctenos para cualquier problema o pregunta acerca de los scripts.

julio 7, 2017