¡Hola, chicos!
¿Está todo bien?

En esta publicación me gustaría escribir sobre una característica de SQL Server que todavía vemos implementada en instancias de varias compañías hoy en día y que hace que todos los recursos (CPU, disco, memoria...) se liberen después de que el último usuario los deduzca de las bases de datos. Me refiero a la propiedad Auto-Close, que muchos consideran una de las PEORES prácticas en la administración de instancias de SQL Server.

Explicando la función de cierre automático

Según la documentación de Microsoft, cuando la función de cierre automático está habilitada en una base de datos, todos los recursos relacionados con esa base de datos se liberan y la base de datos se cierra después de que el último usuario activo se desconecta de esa base de datos. Si un usuario intenta acceder a esta base de datos nuevamente, los recursos del servidor se reasignan nuevamente, la base de datos se vuelve a abrir y vuelve a estar disponible.

En teoría, esta característica parece ser ventajosa, ya que ahorra recursos del servidor y sólo los utiliza cuando es necesario. Sin embargo, en la práctica sucede lo contrario: hay un costo por eliminar las conexiones inactivas, el manejo de archivos y las asignaciones de búfer para "apagar" la base de datos cuando se cierra automáticamente. Este costo es aún mayor al tener que “reconectar” la base de datos cuando se solicita un nuevo acceso a esa base de datos cuando está “apagada”.

Como no se crea caché, si la base de datos se cierra mediante cierre automático y después de 1 ms se solicita nuevamente, la base de datos tendrá que desasignar todos los recursos y reasignarlo todo nuevamente dentro de un intervalo de 1 ms. Imaginemos un caso en el que esto ocurra con frecuencia. La sobrecarga de esta operación, repetida una y otra vez, exige un esfuerzo mucho mayor que dejar los recursos asignados indefinidamente, sin justificar el uso de esta propiedad.

Además, esta característica está en línea con los beneficios que obtiene el SQL Server Connection Pool, ampliamente utilizado por aplicaciones .NET, cuyo propósito es reducir el alto costo de tener que abrir y cerrar conexiones con cada comando enviado a la base de datos.

Otro punto negativo al utilizar esta propiedad es que comenzarás a ver varios eventos en el log de SQL Server con el mensaje “Iniciando base de datos seu_database”, como se muestra a continuación:

Esto es muy malo, por ejemplo, en la administración, ya que termina generando un gran volumen de información en el registro de la instancia y desviando la atención de las alertas que en realidad podrían indicar una alerta o un problema con la instancia.

Para empeorar aún más la vida del DBA que suele activar esta propiedad, algunos DMV de SQL Server, que utiliza para ajustar el rendimiento, validaciones y extracción de información, terminan restableciendo sus valores cada vez que la base de datos se desconecta, lo que significa que no tiene información confiable para realizar este tipo de análisis.

Otro punto negativo del Auto Close es que he visto algunos casos en los que termina generando “confusión” en el procedimiento indocumentado master.dbo.sp_MSforeachdb, provocando que en su bucle de iteraciones entre bases de datos termine no listando una base de datos que tiene el Auto Close activado, y por lo tanto no ejecuta el comando que debería ejecutarse. Esto ocurre porque este procedimiento solo ejecuta comandos en bases de datos cuyo estado sea “ONLINE”. Con Auto Close activado, es común que esté en estado “En Recuperación” al salir del estado inactivo cuando un usuario accede a él.

Si aún no está satisfecho con todos los argumentos presentados, esta propiedad se marca como obsoleta (Enlace de referencia), es decir, se eliminará en alguna versión futura de SQL Server y ya no debería usarse en entornos nuevos ni en instancias recién creadas.

De hecho, ni siquiera deberías utilizar el cierre automático.

¿En qué escenarios se debe utilizar el cierre automático?

Como se considera una mala práctica entre los administradores de bases de datos de SQL Server, es muy poco probable que se encuentre con un escenario en el que se deba utilizar el cierre automático.

Un escenario en el que creo que estaría justificado es cuando tienes una base de datos en un servidor con pocos recursos de CPU y memoria, y al que se accede una vez al día, mediante un trabajo del Agente SQL o una consulta específica. Esta consulta ejecutaría una rutina de procesamiento leyendo los datos de esta base de datos y, después de este procesamiento, ningún trabajo, usuario o aplicación leería, escribiría ni accedería a esta base de datos.

Como sabrá, en el mundo real es muy poco probable que ocurra este escenario y es difícil garantizar que nadie más usará la base de datos, lo que significa que incluso si este escenario existe, en el futuro puede correr el riesgo de que este escenario cambie y su instancia sufra esta sobrecarga generada por el cierre automático.

¿Cómo identificar si una base de datos tiene activado el Auto Cierre?

Para identificar instancias que tienen activada la propiedad Cierre automático, simplemente haga clic derecho en una base de datos, seleccione la opción “Propiedades”

En esta pantalla, seleccione la categoría "Opciones" y busque la propiedad "Cierre automático" en la Cuadrícula.

¿Cómo identificar bases de datos con Auto Close activado en la instancia usando T-SQL?

Para identificar bases de datos con cierre automático activado en la instancia usando T-SQL, puede ejecutar la siguiente consulta:

SELECT database_id, [name], user_access_desc, state_desc
FROM sys.databases
WHERE is_auto_close_on = 1

Resultado:

¿Cómo deshabilitar el cierre automático usando T-SQL?

Para deshabilitar la propiedad de cierre automático de una base de datos en su instancia, use el siguiente comando:

USE [master]
GO

ALTER DATABASE [dirceuresende] SET AUTO_CLOSE OFF
GO

Si desea aplicar este comando a todas las bases de datos en su instancia, puede usar este comando:

USE [master]
GO

DECLARE @Comando VARCHAR(MAX) = ''

SELECT @Comando += 'ALTER DATABASE [' + [name] + '] SET AUTO_CLOSE OFF; '
FROM sys.databases
WHERE is_auto_close_on = 1

EXEC(@Comando)

¡Eso es todo!
Espero que te haya gustado este consejo.

Un abrazo y nos vemos en el próximo post.

base de datos del servidor SQL cómo identificar deshabilitar cómo identificar deshabilitar qué es la propiedad de cierre automático opción de propiedad opción todas las bases de datos

base de datos del servidor SQL cómo identificar deshabilitar cómo identificar deshabilitar qué es la propiedad de cierre automático opción de propiedad opción todas las bases de datos