KB2455009 – CORREÇÃO: desempenho lento ao recuperar um banco de dados se houver muitos VLFs dentro do log de transações no SQL Server 2005, no SQL Server 2008 ou no SQL Server 2008 R2

Sintomas

Considere o seguinte cenário:

Instale o Microsoft SQL Server 2005, o Microsoft SQL Server 2008 ou o Microsoft SQL Server 2008 R2 em um computador.
Um ou mais bancos de dados estão no modelo de recuperação completa ou bulk-logged.
O tamanho inicial de um arquivo de log de transações para o banco de dados é pequeno. No entanto, o arquivo de transação aumenta.
O incremento de crescimento automático do arquivo de log de transações é configurado para um tamanho pequeno. Por exemplo, o incremento de crescimento de crescimento automático é configurado para um megabyte ou para um por cento. Ou você faz o arquivo de log de transações aumentar usando um pequeno crescimento manual.
O arquivo de log de transações aumenta de acordo com o pequeno incremento de crescimento. Após algum tempo, o arquivo de log de transações é grande.

Por exemplo, o arquivo de log de transações aumenta de cem megabytes para dez gigabytes. O arquivo de log de transações aumenta em um incremento de megabyte em cada crescimento.

Nesse cenário, você pode ter os seguintes problemas:

A recuperação do banco de dados será lenta quando o banco de dados for iniciado se houver um grande volume de trabalho a ser recuperado.

Por exemplo, uma transação está pendente aberta por um longo tempo. Ou, muitas transações com tamanho pequeno não são confirmadas quando o SQL Server é desligado ou reiniciado. Nesse caso, você pode receber um erro por um longo tempo de recuperação estimado na fase de análise, na fase de desfazer ou na fase de refazer. O erro é semelhante ao seguinte e é registrado no arquivo de log de SQL Server 2005:

A análise do banco de dados 'mydatabase' (7) está 0% concluída (aproximadamente 1234 segundos permanecem). Esta é apenas uma mensagem informativa. Nenhuma ação do usuário é necessária.
A recuperação de banco de dados é lenta quando um arquivo de backup completo do banco de dados e uma sequência de log de transações adicionais são restaurados se o banco de dados for colocado online.

Observação Para colocar o banco de dados online durante a operação de restauração, use a sintaxe WITH RECOVERY ou WITH STANDBY =< opções> sintaxe.
A recuperação de espelhamento de banco de dados está lenta em um servidor parceiro no par de espelhamento.
O progresso do agente de leitor de log para replicação transacional é lento para as seguintes operações:
- O log de transações de um banco de dados publicador é analisado.
- Um comando é adicionado a um banco de dados de distribuição.
- As transações são replicadas.
O desempenho lento ocorre quando um instantâneo de banco de dados é criado se muitas transações são registradas ou se os logs de transação permanecem ativos por um longo tempo. Esse problema ocorre porque o instantâneo precisa executar a recuperação e os logs de transação precisam ser revertidas.
O desempenho lento ocorre quando você usa a sintaxe DBCC CHECKDB para criar um instantâneo de banco de dados oculto que precisa recuperar totalmente o instantâneo do banco de dados para executar as verificações de consistência.

Causa

Esses problemas ocorrem porque os VLFs (Arquivos de Log Virtual) em um arquivo de log de transações podem causar um desempenho lento quando SQL Server verifica esses VLFs durante as seguintes operações:

Recuperação de banco de dados
Espelhamento de banco de dados
Instantâneo do banco de dados
Atividades do leitor de log de replicação transacional do banco de dados

Quando um arquivo de log de transações para um banco de dados é aumentado por pequenos incrementos durante condições de crescimento extremo, milhares a centenas de milhares de VLFs estão presentes dentro de um único conjunto de arquivos de log de transações (.ldf). No entanto, o grande crescimento de log por incrementos grandes cria apenas vários VLFs por comparação. Por exemplo, vários podem ter menos de 100 VLFs.

Observe que os VLFs são as divisões ou segmentos que são usados internamente SQL Server no arquivo de log de transações.

Resolução

Informações sobre a atualização cumulativa

SQL Server 2008 R2

A correção para esse problema foi lançada pela primeira vez na Atualização Cumulativa 6. Para obter mais informações sobre como obter esse pacote de atualização cumulativa para o SQL Server 2008 R2, clique no seguinte número de artigo para exibir o artigo na Base de Dados de Conhecimento Microsoft:

2489376 Atualização Cumulativa pacote 6 para SQL Server 2008 R2 Observação Como os builds são cumulativos, cada nova versão de correção contém todos os hotfixes e todas as correções de segurança que foram incluídas na versão de correção anterior do SQL Server 2008 R2. Recomendamos que você considere aplicar a versão de correção mais recente que contém esse hotfix. Para obter mais informações, clique no número abaixo para ler os artigos na Base de Dados de Conhecimento Microsoft:

981356 As SQL Server 2008 R2 que foram lançadas após o SQL Server 2008 R2 foi lançado

SQL Server 2008 Service Pack 1

A correção para esse problema foi lançada pela primeira vez na Atualização Cumulativa 12 para SQL Server 2008 Service Pack 1.

Observação Como os builds são cumulativos, cada nova versão de correção contém todos os hotfixes e todas as correções de segurança que foram incluídas na versão de correção anterior do SQL Server 2008. A Microsoft recomenda que você considere aplicar a versão de correção mais recente que contém esse hotfix. Para obter mais informações, clique no número abaixo para ler os artigos na Base de Dados de Conhecimento Microsoft:

970365 Os builds do SQL Server 2008 que foram lançados depois que o SQL Server 2008 Service Pack 1 foi lançado, os hotfixes do Microsoft SQL Server 2008 são criados para pacotes de serviço SQL Server específicos. Você deve aplicar um hotfix SQL Server 2008 Service Pack 1 a uma instalação do SQL Server 2008 Service Pack 1. Por padrão, qualquer hotfix fornecido em um SQL Server service pack é incluído no próximo SQL Server service pack.

SQL Server 2008 Service Pack 2

A correção para esse problema foi lançada pela primeira vez na Atualização Cumulativa 2 para SQL Server 2008 Service Pack 2.

Observação Como os builds são cumulativos, cada nova versão de correção contém todos os hotfixes e todas as correções de segurança que foram incluídas na versão de correção anterior do SQL Server 2008. A Microsoft recomenda que você considere aplicar a versão de correção mais recente que contém esse hotfix. Para obter mais informações, clique no número abaixo para ler os artigos na Base de Dados de Conhecimento Microsoft:

2402659 Os builds do SQL Server 2008 que foram lançados após o lançamento do SQL Server 2008 Service Pack 2 foram lançados hotfixes do Microsoft SQL Server 2008 são criados para pacotes de serviço SQL Server específicos. Você deve aplicar um hotfix SQL Server 2008 Service Pack 2 a uma instalação do SQL Server 2008 Service Pack 2. Por padrão, qualquer hotfix fornecido em um SQL Server service pack é incluído no próximo SQL Server service pack.

SQL Server 2005 Service Pack 3

A correção desse problema foi lançada pela primeira vez na Atualização Cumulativa 13 para SQL Server 2005 Service Pack 3.

Observação Como os builds são cumulativos, cada nova versão de correção contém todos os hotfixes e todas as correções de segurança incluídas na versão de correção anterior do SQL Server 2005. A Microsoft recomenda que você considere aplicar a versão de correção mais recente que contém esse hotfix. Para obter mais informações, clique no número abaixo para ler os artigos na Base de Dados de Conhecimento Microsoft:

960598 Os builds do SQL Server 2005 que foram lançados depois que o SQL Server 2005 Service Pack 3 foi lançado, os hotfixes do Microsoft SQL Server 2005 são criados para pacotes de serviço SQL Server específicos. Você deve aplicar um hotfix SQL Server 2005 Service Pack 3 a uma instalação do SQL Server 2005 Service Pack 3. Por padrão, qualquer hotfix fornecido em um SQL Server service pack é incluído no próximo SQL Server service pack.

SQL Server 2005 Service Pack 4

A correção desse problema foi lançada pela primeira vez na Atualização Cumulativa 1 para SQL Server 2005 Service Pack 4.

Observação Como os builds são cumulativos, cada nova versão de correção contém todos os hotfixes e todas as correções de segurança incluídas na versão de correção anterior do SQL Server 2005. A Microsoft recomenda que você considere aplicar a versão de correção mais recente que contém esse hotfix.

Você deve aplicar um hotfix SQL Server 2005 Service Pack 4 a uma instalação do SQL Server 2005 Service Pack 4. Por padrão, qualquer hotfix fornecido em um SQL Server service pack é incluído no próximo SQL Server service pack.

Status

A Microsoft confirmou que este é um problema nos produtos Microsoft listados na seção "Aplicável a".

Solução alternativa

Instale esse hotfix e reinicie SQL Server melhorar o desempenho da recuperação. Por exemplo, o desempenho pode ser aprimorado para as seguintes operações depois que você instalar esse hotfix e reiniciar SQL Server:

Recuperação de banco de dados
Espelhamento de banco de dados
Instantâneo do banco de dados
Atividades do leitor de log de replicação transacional do banco de dados

Se não for possível instalar esse hotfix, você poderá executar as seguintes soluções alternativas para atenuar um problema existente e evitar uma ocorrência futura.

Atenuar um problema existente

Aguarde a conclusão da operação de restauração ou recuperação

Se você tiver um banco de dados não recuperado que esteja apresentando o desempenho lento ao restaurar ou recuperar o banco de dados, talvez seja necessário aguardar a conclusão da operação de restauração ou recuperação. Por exemplo, você pode ver o status offline ou o status de recuperação no SQL Server Management Studio (SSMS) para um banco de dados não recuperado. Parar SQL Server geralmente não oferece alívio para uma recuperação lenta e pode levar mais tempo para repetir a mesma fase de análise de recuperação, fase de refazer ou fase de desfazer.
Evite restaurar a sequência de log de transações que contém milhares de VLFs

Se você experimentar o desempenho lento ao restaurar e recuperar um banco de dados usando um arquivo de backup, poderá evitar restaurar as sequências de log de transações que contêm milhares de VLFs. Para identificar o arquivo de backup que tem a maioria dos arquivos de log virtuais registrados, use a seguinte instrução para ver as colunas FirstLSN e LastLSN nos arquivos de backup de log:
RESTORE HEADERONLY FROM DISK='C:\folder\file.trn'

Você pode decidir evitar restaurar os arquivos de backup de log. Ou você pode usar a instrução STOP AT nos comandos RESTORE para evitar as partes altamente fragmentadas dos logs de transações. Se você não restaurar totalmente as sequências de log até o ponto mais recente no tempo durante um cenário de recuperação de falha, ocorrerá perda de dados no banco de dados SQL Server. Essa perda de dados ocorre porque nem todas as transações estão sendo mantidas. Portanto, há uma decisão de compensação empresarial. Você pode restaurar totalmente um log de transações altamente fragmentado. No entanto, essa operação pode levar muitas horas. Ou você pode usar a instrução STOP AT na recuperação para interromper a recuperação antes da parte altamente fragmentada do log. No entanto, todas as transações ausentes que você omitir serão perdidas.

Observação Sem instalar esse hotfix, normalmente não há recurso seguro para recuperação acelerada após a reinicialização SQL Server. SQL Server localizar a lista de VLFs para analisar os arquivos de log, refazer transações concluídas e, em seguida, desfazer transações incompletas para concluir a recuperação para colocar o banco de dados online com segurança. Não é possível ignorar transações com segurança durante a recuperação.

Impedir uma ocorrência futura

Definir o incremento de crescimento automático do banco de dados para um tamanho apropriado

Se o tamanho do incremento de crescimento automático for muito pequeno, haverá muitos VLFs (Arquivos de Log Virtual) e você poderá ter um desempenho lento SQL Server. Se o tamanho do incremento de crescimento automático for muito grande, as consultas que fazem com que os logs de transação cresçam automaticamente poderão ter que esperar por um longo tempo para concluir um crescimento. Portanto, um erro de tempo limite pode ocorrer SQL Server. Para contornar esses problemas, você pode definir o tamanho do incremento de crescimento automático do banco de dados para um tamanho apropriado.
Eliminar o grande número de VLFs e usar um crescimento manual

Se muitos VLFs estão no log de transações, reduza o tamanho do log de transações e aumente-o antes do pico de negócios para atender à demanda usando um crescimento manual. Por exemplo, o log de transações aumenta de volta para um tamanho médio razoável em um incremento grande ou em um único crescimento manual. Portanto, o tamanho do log de transações atinge uma capacidade de pico e os arquivos de backup de log são agendados de forma frequente e periódica. Além disso, o log de transações pode ser truncado e os VLFs do log de transações podem ser reutilizados em um ciclo.
Como reduzir e aumentar o log de transações manualmente

Para corrigir um log que tem MUITOS VLFs, siga estas etapas para reduzir o log e aumente-o novamente manualmente:
1. Se o banco de dados estiver em um modelo de recuperação completo ou bulk-logged, você precisará fazer backup do log de transações para permitir que os VLFs ativos sejam truncados e reutilizados. BACKUP LOG databasename TO DISK='C:\folder\log_backupfile.trn'
  Para obter mais informações sobre como fazer backup do arquivo de log de transações usando o SSMS, visite o seguinte site do MSDN (Microsoft Developer Network):
  
  Como fazer backup do arquivo de log de transações usando o SSMSPara obter mais informações sobre como fazer backup do arquivo de log de transações usando instruções Transact-SQL, visite o seguinte site do MSDN:
  
  Como fazer backup do arquivo de log de transações usando instruções Transact-SQL
2. Para determinar o nome lógico do arquivo de log de transações, execute uma das instruções a seguir.
  Instrução 1
  exec sp_helpfile instrução 2
  select * from sys.sysfiles To reduce the size of the transaction log file to the desired size, use the following code:DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
3. Você pode aumentar o tamanho do arquivo de log de transações para um tamanho apropriado. Recomendamos que você permita que o tamanho do arquivo de log de transações aumente para o tamanho normal de pico. Portanto, o incremento de crescimento automático é evitado. Para definir o tamanho do log de transações, use a página Propriedades do Banco de Dados no SSMS ou use a seguinte sintaxe ALTER DATABASE: MODIFY FILE ( NAME = transactionloglogicalfilenae, SIZE=newtotalsize MB)
  Para obter mais informações sobre como aumentar o tamanho de um banco de dados no SSMS, visite o seguinte site do MSDN:
  
  Como aumentar o tamanho de um banco de dados no SSMSPara obter mais informações sobre a sintaxe ALTER DATABASE MODIFY FILE, visite o seguinte site do MSDN:
  
  Informações gerais sobre a sintaxe ALTER DATABASE MODIFY FILE

Informações adicionais

Você pode verificar o número de segmentos VLF examinando o arquivo de log de erros do SQL e, em seguida, localizando o LSN (número de sequência de log) em cada arquivo de backup de log de transações. Os primeiros dígitos antes do símbolo de dois-pontos nos LSNs correspondem ao número do LSN.

Por exemplo, o primeiro número na primeira mensagem informativa para o LSN é 1. No entanto, o primeiro número na segunda mensagem informativa para o LSN é 100001. Nesse cenário, há 100.000 VLFs que são usados entre o horário da primeira mensagem informativa e da segunda mensagem informativa. Portanto, o log de transações fragmentado registrado que tem muitos VLFs (Arquivos de Log Virtual) é semelhante ao seguinte:

{O backup do log foi feito. Banco de dados: mydbname, creation date(time): 2010/07/08(12:36:46), first LSN: 1:5068:70, last LSN: 1:5108:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\folder\logbackup1.trn'}). Esta é apenas uma mensagem informativa. Nenhuma ação do usuário é necessária.

Foi feito backup do log. Banco de dados: mydbname, creation date(time): 2010/07/08(15:36:46), first LSN: 100001:5108:1, last LSN: 100002:5108:1, number of dump devices: 1, device information: (FILE=2, TYPE=DISK: {'C:\folder\logbackup2.trn'}). Esta é apenas uma mensagem informativa. Nenhuma ação do usuário é necessária.}

A tabela a seguir fornece mais informações sobre os produtos ou ferramentas que verificam automaticamente a condição descrita na seção "Sintomas" em sua instância do SQL Server e nas versões do SQL Server nas quais a regra é avaliada.

Software de regra	Título da regra	Descrição da regra	Versões do produto em relação às quais a regra é avaliada
System Center Advisor	SQL Server com a Replicação Transacional, o desempenho do Agente de Leitor de Log pode ser afetado devido ao tamanho do log de transações ou ao número de VLF	Neste caso, SQL Server de instância detectou a replicação transacional de presença com o número de VLFs ou tamanho de TLOG consideravelmente maior. O desempenho do Agente de Leitor de Log é afetado negativamente pelo tamanho do log de transações ou pelo número de VLF. Reduza o tamanho do log de transações e o número de VLF para melhorar o desempenho do Agente de Leitor de Log.	SQL Server 2008 SQL Server 2008 R2 SQL Server 2012

Referências

Para obter mais informações sobre a arquitetura física do log de transações, visite o seguinte site do MSDN:

Informações gerais sobre a arquitetura física do log de transações

Para obter mais informações sobre LSN (números de sequência de log), visite o seguinte site do MSDN:

Informações gerais sobre números de sequência de log

Para obter mais informações sobre o erro 1413 quando o espelhamento de banco de dados é iniciado, visite o seguinte site do MSDN:

Informações gerais sobre o erro 1413 quando o espelhamento de banco de dados é iniciado

Para obter mais informações sobre como uma estrutura de arquivo de log pode afetar o tempo de recuperação do banco de dados, visite o seguinte site do MSDN:

Como uma estrutura de arquivo de log pode afetar o tempo de recuperação do banco de dados

Para obter mais informações sobre os VLFs do log de transações, visite o seguinte site do MSDN:

Informações gerais sobre o arquivo de log de transações

Para obter mais informações sobre como criar um instantâneo de banco de dados, visite o seguinte site do MSDN:

Como criar um instantâneo de banco de dadosPara obter mais informações sobre o Modelo de Manutenção Incremental para SQL Server, clique no seguinte número de artigo para exibir o artigo na Base de Dados de Conhecimento Microsoft:

935897 Um Modelo de Manutenção Incremental está disponível na equipe do SQL Server para fornecer hotfixes para problemas relatados. Para obter mais informações sobre o esquema de nomenclatura para atualizações do SQL Server, clique no seguinte número de artigo para exibir o artigo na Base de Dados de Conhecimento Microsoft:

822499Novo esquema de nomenclatura para pacotes de atualização de software do Microsoft SQL Server Para obter mais informações sobre a terminologia de atualização de software, clique no seguinte número de artigo para exibir o artigo na Base de Dados de Conhecimento Microsoft:

824684 Descrição da terminologia padrão utilizada para descrever as atualizações de software da Microsoft