Por que minha instância do RDS para SQL Server está presa no estado de reinicialização?

Breve descrição

A reinicialização da instância de banco de dados pode levar mais tempo do que o esperado quando há um grande número de transações em andamento na instância. Ou a reinicialização pode parecer travada se houver um grande número de arquivos de log virtuais (VLFs) na instância que estão retardando a reinicialização.

Para resolver esses problemas, siga um ou os dois procedimentos abaixo:

• Verifique se há consultas em andamento na instância de banco de dados.
• Reduza o número de VLFs na instância de banco de dados.

Resolução

Verifique se há consultas contínuas na instância de banco de dados

A reinicialização interrompe todas as transações em andamento, e o SQL Server executa a recuperação ao iniciar a instância. O SQL Server executa a reversão e a reversão das transações para colocar o banco de dados em um estado consistente.

Você pode ver o tempo gasto por esse processo de recuperação no log de erros do SQL Server. A entrada de log inclui o tempo consumido em cada fase de recuperação. No exemplo de entrada de log a seguir, X é a quantidade de tempo gasto pelo SQL Server em cada fase e na recuperação completa. Se você tiver grandes transações em andamento, a reinicialização poderá demorar muito.

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 esse problema, reduza o número de consultas em andamento. Use o comando a seguir para verificar se há alguma transação ativa de modificação de dados no banco de dados.

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;

A saída do comando fornece informações sobre as sessões em execução no seu banco de dados, junto com a hora de início e o texto do SQL. Se houver consultas ainda em execução, permita que elas sejam concluídas antes de executar uma reinicialização.

Reduza o número de VLFs na instância de banco de dados

A reinicialização pode demorar muito devido ao alto número de VLFs na instância. Um número excessivo de VLFs pode se acumular a partir de um log de transações inicialmente pequeno que foi ampliado (manual ou automaticamente) em incrementos muito pequenos.

Antes de iniciar as fases de recuperação durante a reinicialização, o SQL Server verifica todos os VLFs em série. Essa fase é chamada de fase de descoberta. Se um grande número de VLFs for encontrado, a seguinte notificação será exibida no log de erros:

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 reduzir o número de VLFs, faça o seguinte:

Observação: é uma boa prática realizar essas etapas em horários lentos ou fora do horário comercial.

1. Faça login na instância do RDS para SQL Server a partir do Microsoft SQL Server Management Studio como usuário principal.
2. Execute a consulta DBCC SQLPERF(LOGSPACE) para verificar a utilização do arquivo de log.
3. Reduza o arquivo de log do banco de dados que recebeu a alta notificação de VLFs no log de erros.
4. Expanda o arquivo de log de transações para um tamanho de uso adequado de uma só vez. Isso evita o crescimento automático que causa um alto número de VLFs.

Observação: reduzir o arquivo de log reduz o número de VLFs. A realização de uma expansão única cria uma quantidade limitada de VLFs necessários de acordo com os critérios de criação do VLF. Para obter mais informações, consulte Arquivos de log virtuais (VLFs) nos documentos do Microsoft SQL.

---

Informações relacionadas

Como posso solucionar problemas de consumo de armazenamento em minha instância de banco de dados do Amazon RDS que está executando o SQL Server?