Transaction Log

O Transaction Log é um conceito de banco de dados muito importante e conhecer o seu funcionamento é valioso para qualquer DBA ou desenvolvedor que deseje trabalhar com o SQL Server , seja tirando um Backup ou mesmo efetuando somente um SELECT.

Relembrando que um banco de dados no SQL Server é constituído de um arquivo principal, (geralmente, mas nem sempre, com extensão .mdf) nenhum, um ou vários arquivos secundários, com extensão .ndf e um ou mais arquivos do Transaction Log, geralmente com extensão .ldf. É importante notar que as extensões dos arquivos não precisam ser necessariamente .mdf, .ndf e .ldf. Ou seja, nem sempre o Transaction Log possui a extensão .log.

É uma boa idéia colocar estes arquivos em controladores de disco rígidos diferentes para aumentar o desempenho. É importante dizer que devemos colocar em HD’s diferentes e não em partições diferentes de uma mesma HD, onde não há ganho de performance.

Os arquivos primários e secundários armazenam a definição dos objetos do banco e dos dados e o arquivo de Transaction Log armazenas instruções Transact-SQL. A figura abaixo foi traduzida do Books OnLine e mostra como estes arquivos se encaixam no contexto de banco de dados do SQL Server:

 

 

Antes de começar a entender o Transaction Log do SQL Server, é bom recordarmos um pouco sobre o conceito de transação, o conceito de transação completa (a transação que foi completada com COMMIT) e o conceito de transação incompleta (a transação que foi terminada por um ROLLBACK) 

Estes conceitos são extremamente importantes para entender como o Transacion Log do SQL Server funciona e para que ele serve. O que devemos adicionar aqui é que todo o comando enviado para o SQL Server faz parte de uma transação, seja ela explícita por um BEGIN TRANSACTION, ou seja ela implícita.

No início da transação o SQL Server obtém os dados com que o usuário deseja trabalhar dos arquivos físicos e os coloca na memória em um lugar chamado de buffer cache. Enquanto o usuário está trabalhando com os dados na transação nenhuma alteração é feita nos arquivos físicos do banco de dados: somente os dados que estão na memória são alterados e, dependendo do nível de isolação da transação, outros usuários podem ou não enxergar as mudanças feitas enquanto a transação não foi completada.

O local onde os dados ficam na memória é chamado de buffer cache e eles ficam no buffer cache antes de serem escritos fisicamente no disco rígido.

Uma vez que a transação seja completada com sucesso, através de um COMMIT ou internamente, todas as instruções Transact-SQL que esta transação efetuou são armazenadas no Transaction Log, fisicamente dentro do arquivo .ldf.

Com isso o Transaction Log do banco de dados vai ficando cheio. De tempos em tempos o SQL Server faz o que chamamos de checkpoint. Um checkpoint é o processo de escrever as dirty pages que estão no buffer cache para o(s) arquivo(s) de banco de dados físico primário(s) e/ou secundário(s). Ao final do processo de checkpoint, a porção inativa do Transaction Log que contém os comandos correspondentes ao que foi escrito no(s) arquivo(s) é liberada e o tamanho do Transaction Log diminui. Este processo de escrever dirty pages que estão no buffer cache para o disco rígido é chamado de flushing the page.

Esta técnica de primeiro escrever as modificações em um arquivo de log e periodicamente ‘limpar’ este arquivo de log depois de se escrever o que está na memória para um arquivo físico do banco de dados é chamada write-ahead log. Esta técnica permite um grande ganho de desempenho e outros bancos de dados como o Oracle e o DB2 também utilizam um conceito parecido. No SQL Server todo banco de dados obrigatoriamente deve possuir ao menos um arquivo de Transaction Log, ou seja, esta técnica é obrigatória e é feita automaticamente.

Vamos ver um exemplo deste processo passo-a-passo:

1. A aplicação do usuário envia um comando para alterações de dados, como um UPDATE em várias linhas de uma tabela, por exemplo.

2. Internamente o SQL Server retira um certa quantidade de linhas desta tabela, incluindo as linhas que se deseja alterar, do arquivo físico do banco de dados e as coloca no buffer cache (memória). Se estas linhas já estão na memória o SQL Server não faz nada neste passo.

3. O comando é executado, afetando as linhas que estão no buffer cache , gerando dirty pages.

4. Caso o comando UPDATE tenha conseguido alterar as linhas desejadas com sucesso no buffer cache, o SQL Server escreve o comando UPDATE no Transaction Log da mesma maneira que ele foi executado pela aplicação do usuário. No caso de falha do UPDATE em alguma linha, todas as linhas que já tiveram sido alteradas são voltadas para o seu estado inicial, nada é escrito no Transaction Log e uma mensagem de erro é devolvida para a aplicação do usuário que enviou o comando UPDATE.

5. Por enquanto as linhas alteradas já estão modificadas na memória. Qualquer outro usuário que tente visualizar estas linhas irá obtê-las do buffer cache e já as encontrará modificadas.

6. No próximo processo de checkpoint as linhas que foram modificadas e estão na memória são escritas nos arquivos físicos de banco de dados no disco rígido. Feito isso, a instrução UPDATE que estava escrita no Transaction Log é retirada, diminuindo o tamanho do Transaction Log.

O  Transaction Log armazena instruções Transact-SQL até o ponto de Checkpoint, onde o Transaction Log é limpo. Cabe aqui dizer que o Transaction Log permite que se tenha uma facilidade para a recuperação de transações. Ou seja, o Transaction Log atua durante o processo de recovery.

O processo de recovery do SQL Server acontece sempre que o serviço do SQL Server é iniciado e cada banco de dados efetua o seu processo de recovery. O processo de recovery funciona da seguinte maneira:

* Ao iniciar o banco de dados, o SQL Server lê o Transaction Log para aplicar ao arquivo físico as transações que foram completadas com sucesso (COMMIT) e ainda não foram escritas para o arquivo físico do banco de dados. Aqui um checkpoint ocorre e dizemos que estamos fazendo um roll folward na transação.

* Caso haja transações que por acaso não foram terminadas corretamente (ROLLBACK) devemos retirar o que foi feito desta transação e não gravar nada no arquivo físico. Dizemos que estamos dando um roll back na transação.

Além de ser utilizando durante o processo de recovery do SQL Server, o Transaction Log também é utilizado no Backup/Restore. Com Backups do Transaction Log, podemos fazer um backup incremental do banco de dados ao ponto de chegarmos a escolher até qual hora do dia devemos fazer um Restore deste backup. O Transaction Log também é crucial para o Log Shipping , geralmente utilizado para a replicação de dados entre diferentes servidores SQL Server.

O processo de Backup/Restore é completamente influenciado pelo modelo de recuperação (recovery model) escolhido para cada banco de dados assim como o funcionamento e crescimento do Transaction Log. Existem três modelos de recuperação para um banco de dados disponibilizados a partir do SQL Server 2000, cada um para suas próprias características:

1. Simple
2. Full
3. Bulk – Logged

Além destes modelos de recuperação existem várias opções de bancos de dados que podem afetar como o Transaction Log funciona. O valor padrão para o tamanho do Transaction Log de um banco de dados é 25% do tamanho dos arquivos de dados.

Crescimento do Tamanho do Transaction Log

Além do modelo de recuperação escolhido, existem vários fatores que fazem com o arquivo de Transaction Log cresça.

Antes de falar sobre estes fatores gostaria de citar que existem alguns comandos que não gravam informações no Transaction Log. Um deles é o comando TRUNCATE TABLE. Este comando simplesmente apaga todas as linhas da tabela e, caso a tabela possua algum campo com a propriedade IDENTITY, volta ao primeiro valor especificado na criação da coluna IDENTITY. Um exemplo:

TRUNCATE TABLE TB_TESTE

Outro detalhe importante a ser comentado diz respeito às transações implícitas e explícitas no SQL Server. Como vimos anteriormente, uma transação explícita começa com um BEGIN TRANSACTION. Existem também as transações implícitas, que são tratadas internamente pelo SQL Server, como o UPDATE que vimos na semana passada. Podemos controlar a necessidade de utilização de um COMMIT em uma transação implícita para uma conexão através do comando SET IMPLICIT_TRANSACTIONS.

Beleza até aqui. Porém, como visto na semana passada o Transaction Log vai crescendo até chegar em um check point. Geralmente o que faz o Transaction Log crescer tanto é o uso de transações no banco de dados, importações em massa e a falta de limpeza do Transaction Log.

O processo de checkpoint interno ocorre de tempos de tempos. Este tempo varia muito e o processo também ocorre quando o Transaction Log chega a ficar 70% preenchido. Além do processo de checkpoint, podemos truncar a parte inativa do Transaction Log através de um Backup do Transaction Log especial.

Mas cuidado: tanto o processo de checkpoint como Backup especial do Transaction Log NÃO diminuem o tamanho do arquivo físico do Transaction Log. Eles somente truncam a parte inativa do arquivo, sem liberar o espaço em disco para o sistema operacional.

Comandos para trabalhar com o Transaction Log

Existe vários comandos para trabalhar com o Transaction Log. Vamos ver alguns aqui.

Para começar, podemos observar o tamanho do Transaction Log, em MB, e o quando, em percentual, está sendo utilizado do Transaction Log com o seguinte comando DBCC:

DBCC SQLPERF (LOGSPACE)

Com este comando podemos monitorar o crescimento no Transaction Log para todos os bancos de dados.

O SQL Server efetua checkpoints internos automaticamente. Porém existe uma maneira de se forçar um processo de checkpoint no SQL Server: através do comando CHECKPOINT:

CHECKPOINT

E o backup especial do Transaction Log citado acima pode ser feito com este comando (substitua dbTeste pelo nome do seu banco de dados):

BACKUP LOG dbTeste WITH TRUNCATE_ONLY

Vimos até aqui como limpar o Transaction Log e como forçar a um processo de checkpoint. Mas nenhum dos dois comandos diminui o tamanho do arquivo físico do Transaction Log ( .ldf ) e libera o seu espaço em disco para sistema operacional. Para diminuir o tamanho do arquivo físico .ldf do Transaction Log devemos utilizar o comando
DBCC SHRINKFILE. O detalhe aqui é que só podemos diminuir este arquivo até certo ponto. Exemplo:

DBCC SHRINKFILE (dbTesteLog, 1)

O primeiro parâmetro é nome lógico do Transaction Log e o segundo é o tamanho que se deseja que o arquivo físico do Transaction Log possua. O SQL Server tenta alcançar o mais próximo deste valor. Para mais informações sobre estes comandos, consultem o Books OnLine.

5 comentários sobre “Transaction Log

  1. Cara Muito show seu post, tenho um pequeno problema, sem querer apaguei o arquivo de log do banco de dados que estava fazendo teste… tentei acessar o banco e não consegui mais, estou tentando restaurar o banco e também ainda não consegui…. qual o procedimento para restaurar o banco com base apenas no mdf dele?

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s