Início / dba / Perguntas / 31018
Accepted
Mitch
Asked: 2012-12-25 09:33:36 +0800 CST

Transações vazadas do SQL Server

  • 772

Eu tenho um banco de dados acessado por cerca de 50 clientes via TDS sobre TCP, que parece não estar liberando espaço de log. O número de processos fica em torno dos 50 esperados, sendo alguns deles bastante longos (>120 dias).

O banco de dados agora tem 40 gb de espaço de log (tem apenas 14 gb de dados), 39 gb livres. Devido a limitações de espaço na unidade, gostaria de reduzir para algo mais razoável (10 GB). Quando eu executo DBCC SHRINKFILE('db_log', 10000), retorna um erro que o final do log está em uso.

Para liberar o acesso ao final do log, tentei colocar o banco de dados em modo monousuário com o seguinte:

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE db SET MULTI_USER
GO

mas o script está retornando a seguinte mensagem repetida centenas de vezes:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

O que me leva a acreditar que em algum lugar, estou deixando algumas transações não confirmadas. Não tenho conhecimento de nenhum processo que abrisse intencionalmente tantas transações de uma só vez, então acho que elas devem se acumular ao longo do tempo, nunca sendo fechadas.

Pergunta: Como localizo o processo ou script incorreto ou por que o log não está sendo liberado?

sys.dm_tran_active_transactionsestá mostrando um número razoável de 18 transações com propósitos compreensíveis. sp_whomostra apenas os processos que conheço.

Versão do SQL Server:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
Apr  2 2010 15:48:46 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Versão do servidor:

Windows Server 2008 R2 x64 - Datacenter 4 vCPUs, 16 GB de memória, disco de passagem para dados e log, disco do sistema operacional é VHD

no Hyper-V (Windows Server 2008 R2 SP1 x64 Datacenter) Dual Intel X5650 (6 núcleos, 12 threads a 2,67 GHz) 72 GB de memória

O hipervisor possui apenas três VMs e não apresenta alto uso de recursos. A VM do SQL Server mostra cerca de 40% da CPU sob carga e 99% de acessos ao cache.

sql-server

3 respostas

  1. Existe um comando SQL que mostrará transações OPEN. (DBCC ABERTO)

    http://msdn.microsoft.com/en-us/library/ms182792.aspx

    Exibe informações sobre a transação ativa mais antiga e as transações replicadas distribuídas e não distribuídas mais antigas, se houver, no banco de dados especificado. Os resultados são exibidos apenas se houver uma transação ativa ou se o banco de dados contiver informações de replicação

    • 4
  2. Best Answer

    Por alguma razão, parecia não haver nada mantendo o arquivo de log aberto. Ao executar vários backups de log (>10), o final do log foi liberado e a redução pode ocorrer. Não sei por que... mas funcionou.

    backup log db to disk = '\\l-backup1\drop\2012-12-23_db_log.bak' with stats = 1
go 15
dbcc shrinkfile('db_log', 10240)
go
    • 4

  3. Se entendi sua pergunta corretamente, você tem um log de transação de 40 Gb com 39 Gb grátis? O log é uma estrutura circular, composta por Arquivos de Log Virtual menores. Cada vez que um VLF fica cheio, o SQL começa a usar o próximo VLF. (Não necessariamente na mesma ordem em que os VLFs estão no arquivo). Quando você reduz o arquivo de log, ele libera espaço livre no END do log. Se a parte ativa do log estiver no final, nenhum espaço pode ser liberado, se estiver no meio em algum lugar, você só poderá recuperar parte do espaço. DBCC LOGINFO mostrará todos os VLFs no log e um status mostrando que o VLF contém atualmente algum log ativo. Acredito que o status 2 é ativo e o 0 é inativo. Tenho certeza de que o Google pode fornecer mais informações, se necessário.

    Se o seu problema é apenas que a parte ativa está no final, sua melhor aposta é apenas esperar até que ela volte para o início novamente e reduza o log. Isso pode levar um tempo surpreendente, seja paciente. Ele vai chegar lá.
    Lembre-se também de que, se QUALQUER parte de um VLF estiver ativo no momento, todo o VLF será mantido ativo.

    No entanto, você deve monitorar o tamanho do arquivo de log; se ele aumentar inesperadamente novamente, será necessário investigar a causa. Você deve evitar a redução desnecessária do arquivo de log, quando ele crescer novamente, pode diminuir o desempenho.

    Mais informações sobre VLFs podem ser encontradas na postagem do blog Kimberly Tripps aqui .

    • 3

relate perguntas