KB2455009 – CORREÇÃO: desempenho lento quando recupera uma base de dados se houver muitos VLFs no registo de transações no SQL Server 2005, no SQL Server 2008 ou no SQL Server 2008 R2

Sintomas

Considere o seguinte cenário:

Pode instalar o Microsoft SQL Server 2005, o Microsoft SQL Server 2008 ou o Microsoft SQL Server 2008 R2 num computador.
Uma ou mais das bases de dados estão no modelo de recuperação completo ou com registo em volume.
O tamanho inicial de um ficheiro de registo de transações para a base de dados é pequeno. No entanto, o ficheiro de transação aumenta.
O incremento de crescimento automático do ficheiro de registo de transações está configurado para um tamanho pequeno. Por exemplo, o incremento de crescimento automático está configurado para um megabyte ou para um por cento. Também pode fazer com que o ficheiro de registo de transações aumente ao utilizar um pequeno crescimento manual.
O ficheiro de registo de transações cresce através do pequeno incremento de crescimento. Após algum tempo, o ficheiro de registo de transações é grande.

Por exemplo, o ficheiro de registo de transações cresce de 100 megabytes para dez gigabytes. O ficheiro de registo de transações cresce um incremento de megabyte em cada crescimento.

Neste cenário, poderá detetá-lo dos seguintes problemas:

A recuperação da base de dados é lenta quando a base de dados é iniciada se houver um grande volume de trabalho a recuperar.

Por exemplo, uma transação está pendente durante muito tempo. Em entanto, muitas transações de pequena dimensão não são comprometidas quando a SQL Server é encerrada ou reiniciada. Neste caso, poderá receber uma mensagem de erro durante um longo período estimado de tempo de recuperação na fase de análise, na fase de andição ou na fase de refação. O erro assemelha-se ao seguinte e é apresentado no ficheiro de registo do SQL Server de erro 2005:

A análise da base de dados 'mydatabase' (7) é de 0% concluída (faltam aproximadamente 1234 segundos). Esta é uma mensagem apenas informada. Não é necessária qualquer ação do utilizador.
A recuperação de bases de dados é lenta quando um ficheiro de cópia de segurança de base de dados completo e uma sequência de registo de transações adicional são restaurados se a base de dados for apresentada online.

Nota Para pôr a base de dados online durante a operação de restauro, utilize a sintaxe WITH RECOVERY ou WITH STANDBY = < opções>sintaxe.
A recuperação do espelhamento de bases de dados está lenta num servidor parceiro no par espelhamento.
O progresso do agente de leitor de registos para a replicação transacional é lento para as seguintes operações:
- O registo de transações de uma base de dados do editor é analisar.
- É adicionado um comando a uma base de dados de distribuição.
- As transações são replicadas.
O desempenho lento ocorre quando é criado um instantâneo de base de dados se várias transações estiverem registradas ou se os registos de transação permanecerem ativos durante muito tempo. Este problema ocorre porque o instantâneo tem de executar a recuperação e os registos de transação têm de ser recuados.
O desempenho lento ocorre quando utiliza a sintaxe DBCC CHECKDB para criar um instantâneo de base de dados oculto que tem de recuperar completamente o instantâneo da base de dados para executar as verificações de consistência.

Causa

Estes problemas ocorrem porque os Ficheiros de Registo Virtual (VLFs) num ficheiro de registo de transações podem causar um desempenho lento quando o SQL Server analisa estes VLFs durante as seguintes operações:

Recuperação de bases de dados
Espelhamento de bases de dados
Instantâneo de base de dados
Atividades do leitor de registo de replicação transacional de bases de dados

Quando um ficheiro de registo de transações de uma base de dados é crescedo 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 ficheiros de registo de transações (.ldf). No entanto, o grande crescimento dos registos por grandes incrementos cria apenas vários VLFs por comparação. Por exemplo, vários podem ser menores do que 100 VLFs.

Nota: os VLFs são as divisões ou segmentos utilizados internamente pelos SQL Server no ficheiro de registo de transações.

Resolução

Informações de atualização cumulativas

SQL Server 2008 R2

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

2489376 Pacote de Atualização Cumulativa 6 para o SQL Server 2008 R2 Nota: uma vez que as com builds são cumulativas, cada nova versão de correção contém todas as correções de segurança incluídas no lançamento da correção anterior do SQL Server 2008 R2. Recomendamos que considere aplicar a versão de correção mais recente que contém esta correção. Para mais informações, clique no número de artigo que se segue para ver o artigo na Base de Dados de Conhecimento Microsoft:

981356 As SQL Server de 2008 R2 lançadas após o lançamento SQL Server 2008 R2

SQL Server 2008 Service Pack 1

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

Nota Uma vez que as com builds são cumulativas, cada nova versão de correção contém todas as correções e todas as correções de segurança incluídas no lançamento da correção anterior do SQL Server 2008. A Microsoft recomenda que considere aplicar a versão de correção mais recente que contém esta correção. Para mais informações, clique no número de artigo que se segue para ver o artigo na Base de Dados de Conhecimento Microsoft:

970365 As com bases do SQL Server 2008 que foram lançadas após o service pack 1 do SQL Server 2008 ser lançado, são criadas hotfixes do Microsoft SQL Server 2008 para service packs específicos do SQL Server. Tem de aplicar uma SQL Server 2008 Service Pack 1 a uma instalação do SQL Server 2008 Service Pack 1. Por predefinição, qualquer hotfix fornecido num service pack do SQL Server está incluído no service pack SQL Server seguinte.

SQL Server 2008 Service Pack 2

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

Nota Uma vez que as com builds são cumulativas, cada nova versão de correção contém todas as correções e todas as correções de segurança incluídas no lançamento da correção anterior do SQL Server 2008. A Microsoft recomenda que considere aplicar a versão de correção mais recente que contém esta correção. Para mais informações, clique no número de artigo que se segue para ver o artigo na Base de Dados de Conhecimento Microsoft:

2402659 As com bases do SQL Server 2008 que foram lançadas após o SQL Server 2008 Service Pack 2 ter sido lançado, são criadas hotfixes do Microsoft SQL Server 2008 para service packs específicos do SQL Server. Tem de aplicar uma SQL Server 2008 Service Pack 2 a uma instalação do SQL Server 2008 Service Pack 2. Por predefinição, qualquer hotfix fornecido num service pack do SQL Server está incluído no service pack SQL Server seguinte.

SQL Server Service Pack 3 do SQL Server 2005

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

Nota Uma vez que as com builds são cumulativas, cada nova versão de correção contém todas as correções e todas as correções de segurança incluídas no lançamento da correção anterior do SQL Server 2005. A Microsoft recomenda que considere aplicar a versão de correção mais recente que contém esta correção. Para mais informações, clique no número de artigo que se segue para ver o artigo na Base de Dados de Conhecimento Microsoft:

960598 As com bases do SQL Server 2005 que foram lançadas após o SQL Server 2005 Service Pack 3 ter sido lançado, são criadas hotfixes do Microsoft SQL Server 2005 para pacotes de serviços SQL Server específicos. Tem de aplicar uma hotfix do SQL Server Service Pack 3 do SQL Server 2005 a uma instalação do SQL Server Service Pack 3 do SQL Server 2005. Por predefinição, qualquer hotfix fornecido num service pack do SQL Server está incluído no service pack SQL Server seguinte.

SQL Server 2005 Service Pack 4

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

Nota Uma vez que as com builds são cumulativas, cada nova versão de correção contém todas as correções e todas as correções de segurança incluídas no lançamento da correção anterior do SQL Server 2005. A Microsoft recomenda que considere aplicar a versão de correção mais recente que contém esta correção.

Tem de aplicar uma hotfix do SQL Server Service Pack 4 do SQL Server 2005 a uma instalação do SQL Server Service Pack 4 do SQL Server 2005. Por predefinição, qualquer hotfix fornecido num service pack do SQL Server está incluído no service pack SQL Server seguinte.

Estado

A Microsoft confirmou que este problema ocorre nos produtos da Microsoft listados na secção "Aplica-se a".

Solução

Instale esta melhoria e reinicie o SQL Server poderá melhorar o desempenho de recuperação. Por exemplo, o desempenho poderá ser melhorado nas seguintes operações após instalar esta melhoria e reiniciar o SQL Server:

Recuperação de bases de dados
Espelhamento de bases de dados
Instantâneo de base de dados
Atividades do leitor de registo de replicação transacional de bases de dados

Se não conseguir instalar esta hotfix, pode efetuar as seguintes soluções para mitigar um problema existente e impedir uma ocorrência futura.

Mitigar um problema existente

Aguarde que a operação de restauro ou recuperação seja concluída

Se tiver uma base de dados não recuperada que tenha um desempenho lento ao restaurar ou recuperar a base de dados, poderá ter de aguardar que a operação de restauro ou recuperação seja concluída. Por exemplo, poderá ver o estado offline ou o estado de recuperação no SQL Server Management Studio (SSMS) para uma base de dados não recuperada. Parar SQL Server normalmente não ajuda a uma recuperação lenta e pode demorar mais tempo a repetir a mesma fase de análise de recuperação, a fase de refação ou a fase de remoção.
Evite restaurar a sequência de registo de transações que contém milhares de VLFs

Se tiver um desempenho lento enquanto restaura e recupera uma base de dados através de um ficheiro de cópia de segurança, pode evitar restaurar as sequências de registo de transações que contêm milhares de VLFs. Para identificar o ficheiro de cópia de segurança que tem mais ficheiros de registo virtuais gravados, utilize a seguinte declaração para ver as colunas FirstLSN e LastLSN nos ficheiros de cópia de segurança de registos:
RESTAURAR CABEÇALHO APENAS A PARTIR DE DISCO='C:\pasta\file.trn'

Pode optar por evitar restaurar os ficheiros de cópia de segurança de registos. Em vez disso, pode utilizar a inserção STOP AT nos comandos RESTORE para evitar as partes altamente fragmentadas dos registos de transação. Se não restaurar completamente as sequências de registos até ao ponto mais recente no tempo durante um cenário de recuperação de falhas, a perda de dados ocorre na sua base de SQL Server. Esta perda de dados ocorre porque nem todas as transações são mantidas. Consequentemente, existe uma decisão de comércio comercial. Pode restaurar completamente um registo de transações altamente fragmentado. No entanto, esta operação poderá demorar várias horas. Em vez disso, pode utilizar a inserção STOP AT na recuperação para parar a recuperação antes da parte altamente fragmentada do registo. No entanto, quaisquer transações em falta que omita são perdidas.

Nota Sem instalar esta hotfix, normalmente não existe um recurso seguro para a recuperação rápida depois de reiniciar a SQL Server. SQL Server de localizar a lista de VLFs para analisar os ficheiros de registo, refazer transações concluídas e, em seguida, anlar transações incompletas para concluir a recuperação para colocar a base de dados online em segurança. Não pode ignorar as transações em segurança durante a recuperação.

Impedir uma ocorrência futura

Definir o incremento de secções automáticas da base de dados para um tamanho adequado

Se o tamanho do incremento de secções automáticas for demasiado pequeno, haverá muitos Ficheiros de Registo Virtual (VLFs) e o desempenho poderá ser lento no SQL Server. Se o tamanho do incremento de linha automática for demasiado grande, as consultas que fazem os registos de transações aumentarem automaticamente poderão ter de esperar muito tempo para concluir o crescimento. Consequentemente, pode ocorrer um erro de tempo de SQL Server. Para resolver estes problemas, pode definir o tamanho do incremento de secções automáticas da base de dados para um tamanho adequado.
Elimine o grande número de VLFs e utilize um crescimento manual

Se muitos VLFs estiverem no registo de transações, reduza o tamanho do registo de transações e aumente-o antes do pico de negócio para cumprir a procura utilizando um crescimento manual. Por exemplo, o registo de transações volta a aumentar para um tamanho médio razoável num incremento grande ou num único crescimento manual. Portanto, o tamanho do registo de transações atinge uma capacidade de pico e os ficheiros de cópia de segurança de registos são agendados de forma frequente e periódico. Além disso, o registo de transações poderá estar truncado e os VLFs do registo de transações podem ser reutilizados num ciclo.
Como reduzir e aumentar o registo de transações manualmente

Para corrigir um registo com demasiados VLFs, siga estes passos para reduzir o registo e aumente-o novamente manualmente:
1. Se a base de dados estiver num modelo de recuperação completo ou com registo em volume, tem de efetuar uma pagamento de uma fazer uma pagamento do registo de transações para permitir que os VLFs ativos sejam truncados e reutilizados. BACKUP LOG nome de base de dados TO DISK='C:\folder\log_backupfile.trn'
  Para obter mais informações sobre como criar uma segurança do ficheiro de registo de transações através de SSMS, visite o seguinte site do Microsoft Developer Network (MSDN):
  
  Como efetuar uma chamada de pagamento para o ficheiro de registo de transações através de SSMSPara obter mais informações sobre como fazer uma segurança do ficheiro de registo de transações através das instruções transact-SQL, visite o seguinte site do MSDN:
  
  Como efetuar uma back-up do ficheiro de registo de transações com as declarações transact-SQL
2. Para determinar o nome lógico do ficheiro de registo de transações, execute uma das seguintes instruções.
  Declaração 1
  exec sp_helpfile Statement 2
  selecione * de sys.sysfiles Para reduzir o tamanho do ficheiro de registo de transações para o tamanho pretendido, utilize o seguinte código:BDCC ENCOLHER(transactionloglogicalfilename, TRUNCATEONLY)
3. Pode aumentar o tamanho do ficheiro de registo de transações para um tamanho adequado. Recomendamos que deixe o tamanho do ficheiro de registo de transações aumentar para o tamanho normal máximo. Desta forma, será evitado o incremento de secções automáticas. Para definir o tamanho do registo de transações, utilize a página Propriedades da Base de Dados no SSMS ou utilize a seguinte sintaxe ALTER DATABASE:MODIFY FILE ( NAME = transactionlogicalfilenae, SIZE=newtotalsize MB)
  Para obter mais informações sobre como aumentar o tamanho de uma base de dados no SSMS, visite o seguinte site do MSDN:
  
  Como aumentar o tamanho de uma base 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 MODIFICAR FICHEIRO ALTER DATABASE

Mais Informações

Pode verificar o número de segmentos VLF ao rever o ficheiro de registo de erros SQL e, em seguida, ao encontrar o número de sequência de registo (LSN) em cada ficheiro de cópia de segurança do registo de transações. Os primeiros dígitos antes dos dois pontos nos LSNs correspondem ao número do LSN.

Por exemplo, o primeiro número na primeira mensagem informacional do LSN é 1. No entanto, o primeiro número na segunda mensagem informacional do LSN é 100001. Neste cenário, existem 100 000 VLFs que são utilizados entre a hora da primeira mensagem informativo e a segunda mensagem informativo. Por conseguinte, o registo de transações fragmentado que tem muitos Ficheiros de Registo Virtual (VLFs) é assemelha-se ao seguinte:

{Log foi e sim, foi es-lhe e-toscado. Base de dados: omemídbnomo, data(hora): 2010/07/08(12:36:46), primeiro LSN: 1:5068:70, último LSN: 1:5108:1, número de dispositivos de informações: 1, informações do dispositivo: (FILE=1, TYPE=DISK: {'C:\pasta\logbackup1.trn'}). Esta é uma mensagem apenas informada. Não é necessária qualquer ação do utilizador.

Foi e sim que foi es-lhe e quando foi e quando o registo foi e quando o Base de dados: nomedomínio, data(hora): 2010/07/08(15:36:46), primeiro LSN: 100001:5108:1, Último LSN: 100002:5108:1, número de dispositivos de informações: 1, informações do dispositivo: (FILE=2, TYPE=DISK: {'C:\folder\logbackup2.trn'}). Esta é uma mensagem apenas informada. Não é necessária qualquer ação do utilizador.}

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

Software da regra	Título da regra	Descrição da regra	Versões de produtos nas quais a regra é avaliada
Aviso do System Center	SQL Server com a Replicação Transacional, o Desempenho do Agente do Leitor de Registos poderá ser afetado devido ao tamanho do registo de transações ou ao número de VLF	Neste SQL Server detetou a replicação transacional de presença com número de VLFs ou TLOG consideravelmente maior. O Desempenho do Agente do Leitor de Registos é afetado de forma negativa pelo tamanho do registo de transações ou do número de VLF. Reduza o Tamanho do Registo de Transações e o número de VLF para melhorar o desempenho do Agente de Leitor de Registos.	SQL Server 2008 SQL Server 2008 R2 SQL Server 2012

Referências

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

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

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

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

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

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

Para obter mais informações sobre como uma estrutura de ficheiro de registo pode afetar o tempo de recuperação da base de dados, visite o seguinte site do MSDN:

Como uma estrutura de ficheiro de registo pode afetar o tempo de recuperação da base de dados

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

Informações gerais sobre o ficheiro de registo de transações

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

Como criar um instantâneo de base de dadosPara obter mais informações sobre o Modelo de Atualizações Incrementais SQL Server, clique no número de artigo seguinte para ver o artigo na Base de Dados de Conhecimento Microsoft:

935897 A equipa do SQL Server disponibiliza um Modelo de Atualizações Incrementais para problemas comunicadosPara obter mais informações sobre o esquema de no nome de atualizações do SQL Server, clique no seguinte número de artigo para ver o artigo na Base de Dados de Conhecimento Microsoft:

822499Novo esquema de nominação para pacotes de atualização de software do Microsoft SQL Server Para obter mais informações sobre a terminologia de atualizações de software, clique no número do artigo seguinte para ver 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