¿Por qué mi instancia de RDS para SQL Server está atascada en el estado de reinicio?

Descripción corta

Reiniciar la instancia de la base de datos puede llevar más tiempo del esperado si hay una gran cantidad de transacciones en curso en ella. O bien, puede parecer que el reinicio se atasca si hay una gran cantidad de archivos virtuales de registro (Virtual Log Files, VLF) en la instancia que ralentizan el reinicio.

Para resolver estos problemas, realice una de las siguientes acciones o ambas:

• Verifique si hay consultas en curso en la instancia de base de datos.
• Reduzca la cantidad de VLF en la instancia de base de datos.

Resolución

Verifique si hay consultas en curso en la instancia de base de datos

El reinicio detiene todas las transacciones en curso y SQL Server ejecuta la recuperación mientras se inicia la instancia. SQL Server realiza la consolidación y la reversión de las transacciones para llevar la base de datos a un estado coherente.

Puede ver el tiempo que tarda este proceso de recuperación en el registro de errores de SQL Server. La entrada del registro incluye el tiempo consumido en cada fase de recuperación. En el siguiente ejemplo de entrada de registro, X es la cantidad de tiempo que tarda SQL Server en cada fase y en la recuperación completa. Si tienes grandes transacciones en curso, el reinicio puede tardar mucho tiempo.

Recovery completed for database <<DB_NAME>> (database ID <<id of database>>) in X second(s) (analysis X ms, redo X ms, undo X ms [system undo X ms, regular undo X ms].) This is an informational message only. No user action is required.

Para resolver este problema, reduzca la cantidad de consultas en curso. Utilice el siguiente comando para verificar si hay transacciones de modificación de datos activas en la base de datos.

SELECT r.session_id,
       r.start_time,
       r.status,
       r.cpu_time,
       r.total_elapsed_time,
       st.TEXT AS batch_text
FROM sys.dm_exec_requests AS r
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
     CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
     where session_id <> @@SPID
ORDER BY cpu_time DESC;

El resultado del comando brinda información sobre las sesiones que se ejecutan en la base de datos, junto con la hora de inicio y el texto de SQL. Si hay consultas que todavía se están ejecutando, permita que se completen antes de reiniciar el sistema.

Reduzca la cantidad de VLF en la instancia de base de datos

El reinicio puede llevar mucho tiempo debido a la gran cantidad de VLF en la instancia. Se podría acumular una cantidad excesiva de VLF a partir de un registro de transacciones inicialmente pequeño que se amplió (de forma manual o automática) en incrementos muy pequeños.

Antes de iniciar las fases de recuperación durante el reinicio, SQL Server escanea todos los VLF en serie. Esta fase se denomina fase de descubrimiento. Si se encuentra una gran cantidad de VLF, aparece la siguiente notificación en el registro de errores:

Database %ls has more than %d virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Para reducir la cantidad de VLF, haga lo siguiente:

Nota: Se recomienda realizar estos pasos en horarios lentos o fuera del horario laboral.

1. Inicie sesión en la instancia de RDS para SQL Server desde Microsoft SQL Server Management Studio como usuario maestro.
2. Ejecute la consulta DBCC SQLPERF(LOGSPACE) para verificar el uso de los archivos de registro.
3. Reduzca el archivo de registro de la base de datos que recibió la notificación de VLF altos en el registro de errores.
4. Amplíe el archivo de registro de transacciones a un tamaño de uso adecuado de una sola vez. Así se evita el crecimiento automático que provoca una gran cantidad de VLF.

Nota: Al reducir el archivo de registro, se reduce la cantidad de VLF. Al realizar una ampliación única se crea una cantidad limitada de VLF requeridos según los criterios de creación de VLF. Para obtener más información, consulte Archivos virtuales de registro (VLF) en los documentos de Microsoft SQL.

---

Información relacionada

¿Cómo puedo solucionar el consumo de almacenamiento en mi instancia de base de datos de Amazon RDS que ejecuta SQL Server?