Um log de transação aumenta inesperadamente ou se torna normal em um computador que está executando o SQL Server

No SQL Server 7.0, no SQL Server 2000 e no SQL Server 2005, com a configuração de crescimento automático, arquivos de log de transações podem expandir automaticamente.

Normalmente, o tamanho do arquivo log de transação stabilizes quando ele pode conter o número máximo de transações que podem ocorrer entre truncamentos de log de transação que pontos de verificação ou backups do log de transações do disparador.

No entanto, em algumas situações que o log de transações pode se tornar muito grande e execução fora do espaço ou cheio. Normalmente, você receber a seguinte mensagem de erro quando uma transação logon arquivo leva até o espaço em disco disponível e não é possível expandir mais: Se você estiver usando o SQL Server 2005, você receber uma mensagem de erro semelhante à seguinte:Com essa mensagem de erro, SQL Server pode marcar bancos de dados suspeito por causa de uma falta de espaço para expansão de log de transação. Para obter informações adicionais sobre como se recuperar dessa situação, consulte o tópico "Espaço insuficiente em disco" nos manuais online do SQL Server.

Além disso, expansão de log de transação pode resultar nas seguintes situações:

• Um arquivo de log de transações muito grandes.
• As transações podem falhar e podem iniciar reverter.
• Transações podem levar muito tempo para concluir.
• Problemas de desempenho podem ocorrer.
• O bloqueio pode ocorrer.

Faz com que

Expansão de log de transação pode ocorrer devido a razões ou cenários a seguir:

• Uncommitted transactions
• Extremely large transactions
• Operations: DBCC DBREINDEX and CREATE INDEX
• While restoring from transaction log backups
• Client applications do not process all results
• Queries time out before a transaction log completes the expansion and you receive false 'Log full' error messages
• Unreplicated transactions

Observação No SQL Server 2005, você pode analisar as colunas log_reuse_wait e log_reuse_wait_desc a exibição de catálogo sys.databases para determinar o seguinte:

• Por que o espaço de log de transação não é reutilizado
• Por que o log de transação não pode ser truncado

Transações não confirmadas

Transações explícitas permanecem não confirmadas se você não emitir um comando COMMIT ou ROLLBACK explícito. Este mais freqüentemente ocorre quando um aplicativo emite um cancelar ou um comando Transact SQL KILL sem um comando ROLLBACK correspondente. O cancelamento da transação ocorre, mas ele não reverter; portanto, o SQL Server não é possível truncar todas as transações que ocorre após isso porque a transação anulada ainda está aberta. Você pode usar a referência de DBCC OPENTRAN Transact-SQL para verificar se há uma transação ativa em um banco de dados em um determinado momento. Para obter mais informações sobre esse cenário específico, clique nos números abaixo para ler os artigos na Base de dados de Conhecimento da Microsoft: Além disso, consulte o tópico "OPENTRAN DBCC" no SQL Server Books Online.

cenários que podem resultar em transações não confirmadas :

• Um design de aplicativo que assume que todos os erros causam reversões.
• Um design de aplicativo que não completamente leva em conta do comportamento do SQL Server quando ele reverte para transações nomeadas ou aninhados especialmente nomeadas transações. Se você tentar reverter para uma transação chamada interna, você receber a seguinte mensagem de erro:
  Servidor: Mensagem 6401, nível 16, estado 1, linha 13 não pode reverter InnerTran. Nenhuma transação ou savepoint esse nome foi encontrada.
  Depois que o SQL Server gera a mensagem de erro, ele continua para a próxima instrução. Isso ocorre por design. Para obter mais informações, consulte o tópico "Transações aninhadas" ou "Inside SQL Server" nos manuais online do SQL Server.
  
  A Microsoft recomenda o seguinte quando você cria seu aplicativo:
  • Abra somente uma unidade de transação (considere a possibilidade de que o outro processo pode chamar seu).
  • Verificar @@ TRANCOUNT antes de emitir um COMMIT, um ROLLBACK, ENTER, ou um comando semelhante ou uma instrução.
  • Escreva o código com a suposição que outro @@ TRANCOUNT pode "aninhar" seu e planejar o TRANCOUNT @@ externo para ser partilhado quando ocorre um erro.
  • Revisão opções para transações de ponto de salvamento e marca. (Eles não liberar bloqueios!)
  • Realize teste completo.
• Um aplicativo que permite a interação do usuário dentro de transações. Isso faz com que a transação para permanecer aberta por um longo tempo, que faz com que o bloqueio e transação log crescimento porque a transação aberta não pode ser truncada e novas transações são adicionadas para o log após a transação aberta.
• Um aplicativo que não faz check @@ TRANCOUNT para verificar se existe são não transações abertas.
• Rede ou outros erros que fechar a conexão de aplicativo cliente com SQL Server sem informando-lo.
• Pool de conexão. Após a criação threads de trabalho, SQL Server reutiliza-los se eles não estão atendendo a uma conexão. Se uma conexão de usuário inicia uma transação e desconecta antes de confirmar ou reverter a transação, e uma conexão daí em diante reutiliza o mesmo thread, a transação anterior ainda permanece aberta. Essa situação resulta em bloqueios permanecem abertos da transação anterior e impede que o truncamento das transações confirmadas no log, o que resulta em tamanhos de arquivo de log grandes.Para obter mais informações sobre o pool de conexões, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
  164221  (http://support.microsoft.com/kb/164221/ ) Como habilitar o pool de conexão em um aplicativo ODBC

Transações extremamente grandes

Registros de log nos arquivos de log de transação são truncados em uma base de transação por transação. Se o escopo de transação for grande, que transação e quaisquer transações iniciado depois que não são removidos do log de transações, a menos que sua conclusão. Isso pode resultar em arquivos de log grande. Se a transação é grande o suficiente, o arquivo de log pode usar o espaço em disco disponível e fazer com que tipo de mensagem de erro, como Erro 9002 "log de transações cheio". Para obter informações adicionais sobre o que fazer quando você recebe esse tipo de mensagem de erro são fornecidas na seção "Mais informações" deste artigo. Além disso, demora muito tempo e sobrecarga de SQL Server para reverter transações grandes.

Operações: DBCC DBREINDEX e CREATE INDEX

Devido às alterações no modelo de recuperação no SQL Server 2000, quando você usa o modo de recuperação total e executar o DBCC DBREINDEX, o log de transações pode expandir significativamente mais comparada do SQL Server 7.0 em um modo de recuperação equivalente com o uso de SELECT INTO ou BULK COPY e com "trunc. Logoff chkpt.".

Embora o tamanho do log de transação após a operação DBREINDEX pode ser um problema, essa abordagem fornece melhor desempenho de restauração do log.

Durante a restauração de backups de log de transação

Isso é descrito no seguinte artigo Microsoft Knowledge Base:
Se você definir o SQL Server 2000 para usar o modo registrado e emitir uma instrução BULK COPY ou SELECT INTO, cada extensão alterado é marcada e backup, em seguida, quando você faz backup do log de transações. Embora isso lhe permite fazer backup dos logs de transação e recuperar de falhas mesmo depois de executar operações em massa, isso aumenta o tamanho dos logs de transação. SQL Server 7.0 não inclui esse recurso. SQL Server 7.0 somente registra quais extensões são alteradas, mas não grava as extensões reais. Portanto, o log ocupará muito mais espaço no SQL Server 2000 que no SQL Server 7.0 no modo de log em massa, mas não o quanto como no modo completo.

Aplicativos cliente não processam todos os resultados

Se você emite uma consulta para o SQL Server e você não manipular os resultados imediatamente, você pode ser bloqueios e reduzindo simultaneidade no servidor.

Por exemplo, suponha que você emite uma consulta que requer linhas de duas páginas para preencher o resultado definir. SQL Server analisa, compila e executa a consulta. Isso significa que bloqueios compartilhados sejam colocados em duas páginas que contêm linhas que você deve ter para atender à sua consulta. Além disso, vamos supor que nem todas as linhas cabem em um pacote do SQL Server TDS (o método pelo qual o servidor se comunica com o cliente). Pacotes TDS são preenchidos e enviados para o cliente. Se todas as linhas da primeira página couber no pacote TDS, o SQL Server libera o bloqueio compartilhado nessa página, mas deixa um bloqueio compartilhado na segunda página. SQL Server, em seguida, aguarda o cliente solicitar mais dados (você pode fazer isso usando DBNEXTROW/DBRESULTS, SQLNextRow/SQLResults ou FetchLast/FetchFirst por exemplo).

Isso significa que o bloqueio compartilhado é mantido até que o cliente solicita o restante dos dados. Outros processos que solicitam dados da segunda página podem estar bloqueados.

Tempo limite de consulta antes de um log de transações conclui a expansão e você recebe mensagens de erro 'Log completo' falsos

Nessa situação, embora não haja espaço suficiente em disco, você ainda receber uma mensagem de erro "falta de espaço".

Essa situação varia para SQL Server 7.0 e SQL Server 2000.

Uma consulta pode fazer com que o log de transação expandir automaticamente se o log de transações está quase cheio. Isso pode levar mais tempo e uma consulta pode ser interrompida ou pode exceder seu período de tempo limite devido a isso. SQL Server 7.0 retorna Erro 9002 nessa situação. Esse problema não se aplica ao SQL Server 2000.

No SQL Server 2000, se você tiver a opção de redução automática ativada para um banco de dados, não há um extremamente pequeno tempo durante o qual um log de transações tenta expandir automaticamente, mas ele não é possível porque a função de redução automática está sendo executado simultaneamente. Isso também pode causar falsos instâncias Erro 9002.

Normalmente, a expansão automática de arquivos de log de transação ocorre rapidamente. No entanto, nas seguintes situações, pode levar mais tempo que o habitual:

• Incrementos de crescimento são muito pequenos.
• Servidor está lenta por vários motivos.
• Unidades de disco não são rápidas o suficiente.

Transações unreplicated

O tamanho do banco de dados Editor do log de transação pode expandir se você estiver usando duplicação. As transações que afetam os objetos que são replicados são marcadas como "Para replicação". Essas transações, como transações não confirmadas, não são excluídas depois de ponto de verificação ou depois de você fazer backup do log de transações até que a tarefa do leitor de log copia as transações para o banco de dados de distribuição e desmarca-los. Se um problema com a tarefa do leitor de log impede a leitura essas transações no banco de dados Editor , o tamanho do log transação poderá continuar Expandir como o número de transações não replicado aumenta. Você pode usar a referência de DBCC OPENTRAN Transact-SQL para identificar a transação mais antiga que não são replicados.

Para obter mais informações sobre como solucionar problemas unreplicated transações, consulte os tópicos "sp_replcounters" e "sp_repldone" nos manuais online do SQL Server.

Para obter mais informações, clique nos números abaixo para ler os artigos na Base de dados de Conhecimento da Microsoft:

O log de transações para qualquer banco de dados é gerenciado como um conjunto de virtual arquivos de log (VLFs) cujo tamanho SQL Server internamente determina com base no tamanho total do arquivo de log e o incremento de crescimento no uso quando expande o log. Um log sempre expande em unidades de VLFs inteiros e ele pode compactar somente a um limite de VLF. Um VLF pode existir em um dos três estados: ACTIVE RECUPERÁVEL e REUTILIZÁVEL.

• ACTIVE : A parte ativa do log começa no número de seqüência de log mínimo (LSN) que representa uma transação ativa (não confirmada). A parte ativa o log termina em LSN gravado por último. Qualquer VLFs que contêm qualquer parte do log ativo são considerados ativo VLFs. (espaço não usado no log físico não faz parte de qualquer VLF.)
• RECUPERÁVEL : A parte do log que precede a transação de ativa mais antiga só é necessário para manter uma seqüência de backups de log para fins de recuperação.
• REUTILIZÁVEL : se você não está mantendo backups do log de transações, ou se você já tiver feito backup do log, SQL Server reutiliza VLFs antes da transação de ativa mais antiga.

Quando o SQL Server atingir o final do arquivo de log físico, ele começa a reutilizar esse espaço no arquivo físico emitindo um DESTACANDO voltar operação para o início dos arquivos. Na verdade, o SQL Server recicla o espaço no arquivo de log que não é mais necessário para fins de backup ou recuperação. Se uma seqüência de backup de log está sendo mantida, a parte o log antes do mínimo LSN não pode ser substituído até que você faça backup ou truncar os registros de log. Depois de executar o backup de log, SQL Server pode circular voltar ao início do arquivo. Após círculos do SQL Server novamente começar a escrever log registra anteriormente no arquivo de log, a parte reutilizável o log, em seguida, é entre a parte a parte ativa do log e log lógico.

Para obter informações adicionais, consulte o tópico "Transaction log físico arquitetura" nos manuais online do SQL Server. Além disso, você pode ver um diagrama excelente e a discussão sobre isso na página 190 do "Inside SQL Server 7.0" (Soukup, Ron. Interno Microsoft SQL Server 7.0, Microsoft Press, 1999) e também em páginas 182 através de 186 do "Inside SQL Server 2000" (Delaney, Kalen. Interno Microsoft SQL Server 2000, Microsoft Press, 2000). Bancos de dados SQL Server 7.0 e SQL Server 2000 tem as opções para crescimento automático e autoshrink. Você pode usar essas opções para ajudá-lo para compactar ou expandir o log de transações.

Para obter mais informações sobre como essas opções podem afetar seu servidor, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft: Há uma diferença entre o truncamento versus a compactação do arquivo de log de transações. Quando o SQL Server Trunca um arquivo de log de transações, isso significa que o conteúdo desse arquivo (por exemplo, as transações confirmadas) são excluídos. No entanto, quando você está exibindo o tamanho do arquivo da perspectiva de espaço em disco (por exemplo, no Windows Explorer ou usando o comando dir ) o tamanho permanece inalterado. No entanto, o espaço dentro do arquivo .ldf agora pode ser reutilizado por novas transações. Somente quando o SQL Server reduz o tamanho do arquivo log de transação, você realmente vê uma alteração do tamanho físico do arquivo de log.

Para obter mais informações sobre como reduzir os logs de transação, clique nos números abaixo para ler os artigos na Base de dados de Conhecimento da Microsoft: Para obter mais informações sobre uso de log de transação do SQL Server 6.5, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:

Como localizar consultas que consomem uma grande quantidade de espaço de log no SQL Server 2005

No SQL Server 2005, você pode usar a exibição de gerenciamento dinâmico de sys.dm_tran_database_transactions (DMV) para localizar consultas que consomem grandes quantidades de espaço em log. As seguintes colunas na sys.dm_tran_database_transactions DMV pode ser útil:

• database_transaction_log_bytes_used
• database_transaction_log_bytes_used_system
• database_transaction_log_bytes_reserved
• database_transaction_log_bytes_reserved_system
• database_transaction_log_record_count

Você pode consultar a coluna sql_handle de sys.dm_exec_requests DMV para obter o texto da instrução real que consome grandes quantidades de espaço em log. Você pode fazer isso associando a DMV sys.dm_tran_session_transactions na coluna transaction_id e sys.dm_tran_database_transactions DMV e, em seguida, adicionando uma associação com sys.dm_exec_requests adicional na coluna session_id.

Para obter mais informações sobre o sys.dm_tran_database_transactions DMV, visite o seguinte site da Web Microsoft Developer Network (MSDN): Para obter mais informações sobre o sys.dm_tran_session_transactions DMV, visite o seguinte site da MSDN: Para obter mais informações sobre a DMV sys.dm_exec_requests, visite o seguinte site da MSDN: