Um log de transação cresce inesperadamente ou fica cheio de SQL Server

IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine translation ou MT), não tendo sido portanto revisto ou traduzido por humanos. A Microsoft tem artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais. O objectivo é simples: oferecer em Português a totalidade dos artigos existentes na base de dados do suporte. Sabemos no entanto que a tradução automática não é sempre perfeita. Esta pode conter erros de vocabulário, sintaxe ou gramática… erros semelhantes aos que um estrangeiro realiza ao falar em Português. A Microsoft não é responsável por incoerências, erros ou estragos realizados na sequência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza actualizações frequentes ao software de tradução automática (MT). Obrigado.

317375
Sumário
Se a opção de crescimento automático está definida no Microsoft SQL Server 2005, SQL Server 2000 e SQL Server 7.0, arquivos de log de transação podem expandir automaticamente.

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

No entanto, em alguns casos que o log de transações pode se tornar muito grande e execução espaço ou cheio. Normalmente, você receber a seguinte mensagem de erro quando um arquivo de log de transação usa o espaço em disco disponível e não pode expandir mais:
Erro: 9002 Gravidade: 17, estado: 2
O arquivo de log do banco de dados ' %. * ls' está cheio.
Se você estiver usando o SQL Server 2005, você receber uma mensagem de erro semelhante à seguinte:
Erro: 9002, gravidade: 17, Estado: 2
Log de transações do banco de dados ' %. * ls' está cheio. Para descobrir por que espaço no log não pode ser reutilizado, consulte a coluna log_reuse_wait_desc em sys. Databases
Mensagem de erro, além de SQL Server pode marcar bancos de dados como suspeito por falta de espaço para expansão de log de transação. Para obter mais informações 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, a expansão do 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 a reversão.
  • Transações podem levar muito tempo para concluir.
  • Podem ocorrer problemas de desempenho.
  • O bloqueio pode ocorrer.
Mais Informação
Expansão de log de transação pode ocorrer um dos seguintes motivos ou cenários.


Observação No SQL Server 2005, você pode analisar o log_reuse_wait e log_reuse_wait_desccolunas da exibição de catálogo sys. Databases para determinar por que o espaço de log de transação não é reutilizado e por que o log de transação não pode ser truncado.


Transações não confirmadas
Transações explícitas permanecem confirmadas, se você não emitir um comando COMMIT ou ROLLBACK explícito. Isso ocorre com mais freqüência quando um aplicativo emite um cancelamento ou um comando Transact-SQL KILL sem um comando ROLLBACK correspondente. O cancelamento de transação ocorre, mas não reverter de volta. Portanto, SQL Server não é possível truncar a cada transação 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 existe uma transação ativa em um banco de dados em um determinado momento.Para obter mais informações sobre este cenário específico, clique nos números abaixo para ler os artigos na Base de dados de Conhecimento da Microsoft:
295108Transação incompleta pode conter um grande número de bloqueios e bloqueio de maiúsculas
171224 Compreendendo como funciona o comando KILL Transact-SQL
Além disso, consulte o tópico "DBCC OPENTRAN" SQL Server Books Online.

Cenários que podem resultar em transações não confirmadas:
  • Design de um aplicativo que pressupõe que todos os erros causar reversões.
  • Design de aplicativo que não considera completamente o comportamento de SQL Server quando ele reverte para transações nomeadas ou aninhados especialmente nomeadas transações. Se você tentar reverter uma transação chamada interna, a seguinte mensagem de erro é exibida:
    Servidor: Msg 6401, nível 16, estado 1, linha 13 possível reverter InnerTran. Não transação ou savepoint com esse nome foi encontrado.
    Depois de SQL Server gera a mensagem de erro, ele continua para a próxima instrução. Isso é por Design. Para obter mais informações, consulte "Transações aninhadas" ou "dentro de SQL Tópico Server"nos Manuais Online do SQL Server.

    Ao projetar seu aplicativo, recomendamos o seguinte:
    • unidade de transação somente uma caneta (considere a possibilidade de que outro processo pode chamar seu).
    • Verificar @ @ TRANCOUNT antes de emitir uma confirmação, um REVERSÃO, retorno, ou um comando semelhante ou instrução.
    • Escreva o código com a suposição de que outro @ @ TRANCOUNT pode "aninhar" seu e planejar o TRANCOUNT @ @ externo ser implementada Quando ocorre um erro de volta.
    • Revisão opções savepoint e marca de transações. (Esses não liberar bloqueios!)
    • Execute o teste completo.
  • Um aplicativo que permite a interação do usuário dentro de transações. Isso faz com que a transação permaneça aberta por muito tempo e este faz com que o bloqueio de log de transações e crescimento porque a transação aberta não pode ser truncada e novas transações são adicionadas ao log de transação aberta depois de.
  • Um aplicativo que não verificar @ @ TRANCOUNT para verificar que não haja transações abertas.
  • Rede ou outros erros que fechar o aplicativo cliente conexão SQL Server sem informar a ele.
  • Pool de conexão. Após a criação de threads de trabalho, SQL Server reutiliza-los se eles não estão atendendo a uma conexão. Se a conexão de uma usuário inicia uma transação e desconecta antes de confirmar ou reverter a transação e uma conexão depois que 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 o truncamento de transações confirmadas no log. Isso resulta em tamanhos de arquivo de log grande. Para obter mais informações sobre conexão pool, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
    164221Como habilitar o pool de conexão em um aplicativo ODBC


Transações muito 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 é grande, a transação e as transações iniciadas após não são removidos do log de transações, a menos que ela é concluída. Isso pode resultar em arquivos de log grande. Se a transação for 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 Erro 9002 "log de transações cheio". Para obter mais informações sobre o que fazer quando receber esse tipo de mensagem de erro, consulte a seção "Mais informações" deste artigo. Além disso, ele leva muito tempo e sobrecarga de SQL Server para reverter transações grandes.

Operações: Índice de DBCC DBREINDEX e criar
Devido às alterações no modelo de recuperação no SQL Server 2000, ao usar o modo de recuperação total e executar o DBCC DBREINDEX, a transação log pode expandir significativamente mais comparado ao SQL Server 7.0 em um modo de recuperação equivalente com o uso de SELECT INTO ou BULK COPY e com "Trunc. Logoff no chkpt.".

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

Quando a restauração de backups de log de transação
Isso é descrito na Base dados de Conhecimento da Microsoft artigo:
232196 Espaço de log usado parece crescer após a restauração do backup

Se você definir SQL Server 2000 para usar o modo de log em massa e emitir uma instrução de cópia em MASSA ou SELECT INTO, cada extensão alterado é marcado e então backup para fazer backup do log de transação. Embora isso permite fazer backup de 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 só registra quais extensões são alteradas, mas não registra as extensões reais. Portanto, o log usa significativamente mais espaço no SQL Server 2000 que SQL Server 7.0 no modo de Log em massa, mas não tanto quanto no modo completo.

Aplicativos cliente não processam todos os resultados
Se você emitir uma consulta para SQL Server e você não manipular os resultados imediatamente, você pode bloqueios e reduzindo simultaneidade no servidor.

Por exemplo, suponha que você emitir uma consulta que requer linhas de duas páginas para preencher o conjunto de resultados. SQL Server analisa, compila e executa a consulta. Isso significa que os bloqueios compartilhados são adicionados em duas páginas que contêm as linhas que você deve ter para atender à sua consulta. Além disso, suponha que nem todas as linhas cabem em um pacote de SQL Server TDS (o método pelo qual o servidor se comunica com o cliente). Pacotes TDS são preenchidos e enviados ao cliente. Se todas as linhas da primeira página couberem no pacote TDS, SQL Server libera o bloqueio compartilhado nessa página, mas deixa um bloqueio compartilhado na segunda página. SQL Server 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 mantida até que o cliente solicita o restante dos dados. Outros processos dados de solicitação da segunda página podem ser bloqueados.

Consultas de tempo limite antes de um log de transação termina a expansão e receber mensagens de erro falsos 'Log completo'
Nessa situação, embora não haja espaço em disco suficiente, você ainda receber uma mensagem de erro "sem espaço".

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

Uma consulta pode causar o log de transações 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 limite por 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ê tem a opção de redução automática ativada para um banco de dados é muito pequeno tempo durante o qual o log de transação tenta expandir automaticamente. No entanto, ele não pode expandir porque a função de redução automática está em execução ao mesmo tempo. Isso também pode causar falsos instâncias Erro 9002.

Normalmente, a expansão automática dos arquivos de log de transação ocorre rapidamente. No entanto, nas seguintes situações pode levar mais de usual:
  • Incrementos de crescimento são muito pequenos.
  • O servidor está lento por vários motivos.
  • Unidades de disco não são rápidas o suficiente.


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

Para obter mais informações sobre como solucionar problemas 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:
306769CORREÇÃO: Log de transação de instantâneo de banco de dados publicado não pode ser truncado
240039 CORREÇÃO: DBCC OPENTRAN não relata informações de replicação
198514 CORREÇÃO: Restaurar para o novo servidor faz com que permanecem no log de transações
Informações avançadas
O log de transações para qualquer banco de dados é gerenciado como um conjunto de arquivos de log virtuais (VLFs). SQL Server determina o tamanho do arquivo VLF internamente com base no tamanho total do arquivo de log e o incremento de crescimento é usado quando o log expande. Um log sempre expande em unidades de VLFs inteiros e ele pode compactar somente a um limite VLF. Um VLF pode existir em um dos três estados: ativo, RECUPERÁVEL e REUTILIZÁVEL.
  • ACTIVE: A parte ativa do log começa o 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 escritos por último. Qualquer VLFs que contêm qualquer parte do log ativo são considerados ativo VLFs. (espaço não utilizado no log físico não é parte de qualquer VLF.)
  • RECUPERÁVEL: A parte do log que vem antes da transação ativa mais antiga só é necessária manter uma seqüência de backups de log de recuperação.
  • REUTILIZÁVEL: se você não está mantendo backups de log de transação, ou se você SQL Server já foi feito o log, reutiliza VLFs antes ativo mais antigo transação.
Quando SQL Server chega ao fim do arquivo de log físico, ele começa a reutilizar esse espaço no arquivo físico emitindo uma operação CIRCULANDO volta para o início dos arquivos. Na verdade, SQL Server recicla o espaço no arquivo de log não é mais necessário para fins de backup ou recuperação. Se uma seqüência de backup de log está sendo mantida, parte do log antes do mínimo LSN não pode ser substituído até que você faça backup ou truncar os registros de log. Após realizar o backup de log, poderá circular SQL Server voltar ao início do arquivo. Após SQL Server círculos para começar a escrever registros anteriormente no arquivo de log, parte reutilizável do log é então entre o final do log lógico e a parte ativa do log.

Para obter mais informações, consulte o tópico "Transação Log físico arquitetura" nos Manuais Online do SQL Server. Além disso, você pode ver um diagrama e uma discussão sobre isso na página 190 do "Inside SQL Server 7.0" (Soukup, Ron. Interior Microsoft SQL Server 7.0, Microsoft Press, 1999) e também nas páginas 182 a 186 de "Inside SQL Server 2000" (Delaney, Kalen. Dentro do Microsoft SQL Server 2000, Microsoft Press, 2000).Bancos de dados SQL Server 2000 e SQL Server 7.0 tem a opção de crescimento automático e autoshrink. Você pode usar essas opções para ajudá-lo a comprimir ou expandir o log de transação.

Para obter mais informações sobre como essas opções podem afetar o servidor, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
315512Considerações sobre a configuração de crescimento automático e Autoshrink em SQL Server
Truncamento do arquivo de log de transação difere de compactação do arquivo de log de transação. Quando SQL Server trunca o arquivo de log de transações, isso significa que o conteúdo do arquivo (por exemplo, as transações confirmadas) é excluído. No entanto, quando você está exibindo o tamanho do arquivo de uma perspectiva de espaço em disco (por exemplo, no Windows Explorer ou usando o comando dir ), o tamanho permanecerá inalterado. No entanto, o espaço dentro do arquivo. ldf agora pode ser reutilizado por novas transações. Somente quando SQL Server reduz o tamanho do arquivo de log de transação você realmente vê uma alteração no tamanho físico do arquivo de log.

Para obter mais informações sobre como reduzir logs de transação, clique nos números abaixo para ler os artigos na Base de dados de Conhecimento da Microsoft:
256650Como reduzir o log de transações SQL Server 7.0
272318 Reduzindo o log de transações SQL Server 2000 com DBCC SHRINKFILE
Para obter mais informações sobre o log de transações SQL Server 6.5 uso, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
110139Causas de log de transação SQL preenchendo

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

No SQL Server 2005, você pode usar o modo de exibição de gerenciamento dinâmico sys.dm_tran_database_transactions (DMV) para localizar consultas que consomem grandes quantidades de espaço de log. As seguintes colunas de 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 do exec_requests DMV para obter o texto de instrução real que consome grandes quantidades de espaço de log. Para fazer isso associando o DMV sys.dm_tran_database_transactions e o sys.dm_tran_session_transactions DMV na coluna transaction_id e adicionando uma associação com exec_requests adicional na coluna session_id.

Para obter mais informações sobre o sys.dm_tran_database_transactions DMV, vá para o sys.dm_tran_database_transactions (Transact-SQL) Site da Microsoft Developer Network (MSDN).

Para obter mais informações sobre o sys.dm_tran_session_transactions DMV, vá para o sys.dm_tran_session_transactions (Transact-SQL) Site do MSDN.

Para obter mais informações sobre o exec_requests DMV, vá para o exec_requests (Transact-SQL) Site do MSDN.

Aviso: Este artigo foi traduzido automaticamente

Propriedades

ID do Artigo: 317375 - Última Revisão: 07/16/2013 04:32:00 - Revisão: 1.1

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL 2005 Server Enterprise
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL 2005 Server Workgroup
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • kbsqlsetup kbinfo kbmt KB317375 KbMtpt
Esta informação foi útil?