¿Como comprobar el estado de tus instancias, BBDD y agentes de un solo vistazo?

Una de las situaciones en las que te puedes encontrar en tu carrera profesional como DBA es que al llegar a un nuevo cliente no exista ningún reporte u opción para ver de un simple vistazo si todas las instancias SQL Server están arriba, si las BBDD están en estado ONLINE o si el SQL Agent está corriendo.

Para ello desarrollamos la siguiente query que lanzada desde un servidor de gestión (Central Management Servers) o desde un paquete de Integration Services podemos obtener un listado y en él observar en qué estado se encuentran tus entornos:

select @@SERVERNAME as InstanceName, case when count (name) >  0 then 'NO' else 'SI' end as TodasOnline,
(SELECT case when count (name) > 0 then 'SI' else 'NO' end as SQLLoginsLocked FROM sys.sql_logins WHERE LOGINPROPERTY(name, N'isLocked') = 1) as SQLLoginsLocked,
(select case when datediff (HOUR,create_date,getdate()) < 24 then 'Reiniciada24h' else create_date end as InstanceStartTime  from sys.databases where name = 'tempdb') as InstanceStartTime,
(SELECT case when count (spid) > 0 then 'OK' else 'SQLAgenteCaído' end as EstadoSQLAgent FROM master.dbo.sysprocesses WITH (nolock) WHERE Program_Name LIKE 'SQLAgent%') as EstadoSQLAgent
from sys.databases
where state_desc <> 'ONLINE'

Dándonos un resultado similar a esto:

Como podrás comprobar, en la primera columna observamos el nombre de instancia gracias a la variable @@SERVERNAME:

select @@SERVERNAME

En la segunda columna consultamos la tabla de sistema sys.databases las BBDD que no están en estado “ONLINE”:

select @@SERVERNAME as InstanceName, case when count (name) >  0 then 'NO' else 'SI' end as TodasOnline
from sys.databases
where state_desc <> 'ONLINE'

La tercera columna nos indicara si existe algún login SQL bloqueado, usando la función LOGINPROPERTY:

select name
FROM sys.sql_logins WHERE LOGINPROPERTY(name, N'isLocked') = 1

También añadimos una comprobación para saber si en las últimas 24H se ha reiniciado la instancia, por verificar a primera hora de la mañana, al llegar a la oficina, que esté todo corriendo. Esto lo obtenemos con la fecha de creación de la base de datos TEMPDB (esta información se puede obtener de muchas otras formas, pero está funciona en todas las versiones de SQL Server):

select case when datediff (HOUR,create_date,getdate()) < 24 then 'Reiniciada24h' else create_date end as InstanceStartTime  
from sys.databases 
where name = 'tempdb'

Y por último, consultamos si el SQL Agent está corriendo:

SELECT case when count (spid) > 0 then 'OK' else 'SQLAgenteCaído' end as EstadoSQLAgent 
FROM master.dbo.sysprocesses WITH (nolock) 
WHERE Program_Name LIKE 'SQLAgent%'

Esperemos que haya sido de vuestro interés y os sirva de utilidad en futuras ocasiones.

¡Hasta la próxima!