Analisando seu log file – Parte 01

Aproveitando que o amigo Leandro Ribeiro entrou no tema de fragmentação dos arquivos de Log (Post1 e Post2) e o fato de que eu estava preparando alguns materiais sobre o mesmo tema para uso próprio, resolvi fazer um post com alguns materiais e explicações que acho que podem ser úteis.

No blog do Leandro vocês podem encontrar algumas explicações sobre o que é a fragmentação lógica do arquivo de log, quais os problemas causados e como tentar contorná-las, além de algumas recomendações de best practices.

Durante a análise do seu arquivo de log existem uma série de ferramentas que podem te ajudar e vou listar algumas delas.

Até o SQL Server 6.5 havia uma tabela de sistema chamado de SYSLOGS onde podíamos ler o transaction log, mas a mesma foi removida no SQL Server 7.

Também na versão 6.5 do SQL Server havia o comando DBCC LOG (não sei se existia em uma versão anterior). Esse comando DBCC na verdade não é documentado, apesar de amplamente difundido entre os DBAs e permite que você analise seu arquivo de log, vendo as operações registradas no mesmo.

A sintaxe desse comando é a seguinte:

DBCC LOG(DB_NAME ou DB_ID, Tipo_de_Output)

Ou seja, ele recebe como primeiro parâmetro o nome ou ID do banco de dados que terá seu arquivo de log analisado e também um outro parâmetro que define o tipo de output que desejamos.

Existem 6 possíveis outputs para esse comando, sendo o padrão o valor 0 (zero). Explico os 6 possíveis parâmetros abaixo:

Output Descrição
-1 Registros do log completos além de um dump hexadecimal do log da transação atual, além de checkpoint start, Database Version e MAX XDESID
0 Informações mínimas, informando o LSN, Operation, Context, Transaction ID e se um bloqueio de Log foi gerado
1 Mesmos dados do tipo 0, adicionando o record length, previous LSN, e uma descrição do registro de log
2 Mesmos dados do tipo 1, adicionando o Allocation Unit ID, object name, SQL Server Page, Slot location dentro da página, e informações sobre locks
3 Registro do log completo
4 Similar ao tipo 1, incluindo também um dump em hexadecimal dos registros do log

O tipo de saída a ser escolhido vai depender do tipo de análise que será feita, ou seja, do tipo de informação que você necessita. Vale lembrar que ter mais informações nem sempre ajuda, então escolha apenas aquelas que realmente são necessárias para sua análise.

De qualquer forma o comando acima pode ser muito útil para analisar alguma operação que tenha ocorrido em seu ambiente.

Com esse comando você pode inclusive ler até a parte inativa do seu transaction log, utilizando o Trace Flag 2536 (maiores explicações no blog da Kimberly Tripp).

Desde o SQL Server 2000 foi adicionada a função (também não documentada!) FN_DBLOG().

Essa função recebe também 2 parâmetros que são o LSN inicial e final que você deseja analisar (ou seja, basicamente filtros) e normalmente recebe os valores NULL,NULL para listar tudo que há no log.

O resultado da mesma é basicamente o mesmo do DBCC LOG (na verdade a DBCC LOG aparentemente tem algumas colunas a mais, relativas , a Bulk allocs), mas com as vantagens de ser uma função, o que permite uma melhor manipulação dos dados. Eu até imaginava que a DBCC LOG seria descontinuada, mas ela continua a existir até mesmo no SQL Server 2012.

Uma outra consulta que você pode analisar em suas análises do transaction log é a seguinte:

select NAME, LOG_REUSE_WAIT, LOG_REUSE_WAIT_DESC
from sys.databases

Essa consulta lista os bancos de dados e o motivo, caso haja, para que o log desse banco não possa ser reutilizado no momento (ou seja, podendo causar um crescimento do arquivo de log).

Os possíveis valores retornados são:

LOG_REUSE_WAIT LOG_REUSE_WAIT_DESC
0 NOTHING
1 CHECKPOINT
2 LOG_BACKUP
3 ACTIVE_BACKUP_OR_RESTORE
4 ACTIVE_TRANSACTION
5 DATABASE_MIRRORING
6 REPLICATION
7 DATABASE_SNAPSHOT_CREATION
8 LOG_SCAN
9 AVAILABILITY_REPLICA
10 For internal use only
11 For internal use only
12 For internal use only
13 OLDEST_PAGE
14 OTHER_TRANSIENT

Ou seja, se para o seu banco de dados ele retornar o valor “LOG_BACKUP” quer dizer que seu log não terá espaço liberado para reutilização até que você faça um backup de log. Durante esse período, caso haja alguma operação que precise escrever no log ele provavelmente será expandido automaticamente (se o autogrowth do arquivo de log estiver ativado!).

Veja que há também o valor “ACTIVE_TRANSACTION“, ou seja, enquanto uma transação está alterando uma porção do log esta não pode ser reaproveitada pelo SQL Server. Isso é um dos motivos de ser recomendado utilizar transações pequenas e também alterar dados em volumes pequenos sempre que possível.

Há ainda o comando DBCC LOGINFO(DB_NAME), outro DBCC não documentado, que permite analisar o seu transaction log por uma outra perspectiva.

O DBCC LOGINFO retorna as seguintes colunas:

Coluna Descrição
FileId Id do arquivo físico
FileSize Tamanho do VLF em BYTES
StartOffset Offset do VLF, em bytes. A primeira página (8192 bytes) sempre é do HEADER
FSeqNo Define a sequência de utilização dos VLFs. Se for 0 (zero) o VLF nunca foi utilizado
Status Informa o status atual do VLF (explicação detalhada abaixo)
Parity Pode ter os valores 0 (zero), 64 ou 128. 0 (zero) é um valor de paridade ilegal, utilizado somente quando o VLF é criado. Após o primeiro uso ele assume um valor inicial (acredito que seja sempre 64, mas não encontrei documentação disso) e então toda vez que esse VLF é reutilizado tem seu valor alterado.
CreateLSN LSN no momento da criação dos VLFs. Se for 0 então foi criado ao criar o Database

Quanto ao valores da coluna STATUS temos as seguintes explicações:

Valor Descrição
Active Contem ao menos um registro que faz parte do log ainda ativo, que são as partes do log que ainda precisam ser utilizados por  alguma operação (ex: Active Transaction). Um backup de log não o afeta.
Recoverable Não tem registros de log ativos, mas ainda não está disponível até que um backup de log ocorra.
Reusable Registros de log que estão inativos e cujo espaço pode ser reutilizado
Unused Espaço onde nunca foram escritos registros de log. VLF está preenchido apenas com 0 (zeros).

Uma “curiosidade” ainda sobre esses valores é o fato da “Parity” ser a responsável pelo “Instant File Initialization” não funcionar com os arquivos de log. O valor de “Parity” é utilizado após um travamento do SQL Server para determinar onde termina a parte ativa do log da seguinte forma.

Ao criar o arquivo de log ele é preenchido com zeros e a medida que os VLFs vão sendo escritos uma paridade é definida para ele. Quando o fim do arquivo de log é encontrado o SQL Server volta ao início do arquivo de log e inverte o valor da paridade, garantindo que as porções de log reescritas tenham um valor de paridade que seja o oposto das demais.

Sendo assim, no momento de um travamento o SQL Server ao tentar se recuperar processa o log até encontrar uma porção que tenha os bits de paridade invertidos, sabendo então que aquele ponto é onde ele deve parar de processar o log.

Se o arquivo de log não fosse “zerado” esse processo não seria possível!

Por fim há ainda o comando (esse é documentado!) DBCC SQLPERF(LOGSPACE).

Esse comando tem outras funções, mas com o argumento LOGSPACE ele retorna uma tabela com 4 colunas dando informações básicas sobre os arquivos de log de todos os bancos de dados.

As colunas retornadas por ele são o Database Name, Log Size (MB), Log Space Used (%) e Status, e são auto explicativas.

[Adicionado em 12/09/2012 – Início]

Com o lançamento do SQL Server 2012 temos agora uma DMV bem semelhante ao DBCC SQLPERF(LOGSPACE), a sys.dm_db_log_space_usage.

Com o comando abaixo você consegue informações sobre o tamanho e espaço em uso do seu arquivo de log.

SELECT db_name(database_id) [Banco],
CONVERT(numeric(18,3),CONVERT(numeric ,total_log_size_in_bytes)/1024/1024) as [Tamanho (MB)],
CONVERT(numeric(18,3),CONVERT(numeric,used_log_space_in_bytes )/1024/1024) as [Espaço em Uso (MB)],
used_log_space_in_percent as [% em Uso]
FROM sys.dm_db_log_space_usage

[Adicionado em 12/09/2012 – Fim]

Há ainda um outro comando para trabalhar com os arquivos de log, mas esse eu prefiro apenas citar e deixar um link com melhores referências.

Trata-se do FN_DUMP_DBLOG e você pode encontrar melhores informações no blog do Paul Randal.

Bem, para esse post já escrevi demais. No próximo post irei explicar um pouco melhor o funcionamento do transaction log e algumas formas de lidar e monitorar com a fragmentação do mesmo!

Outras referências sobre o assunto:

Blog do Luti (na época que trabalhava na Microsoft):

Esse post foi publicado em Artigos, Virtual PASS BR. Bookmark o link permanente.

3 respostas para Analisando seu log file – Parte 01

  1. Pingback: Transações no SQL Server (Commit e RollBack Transaction) « Alex Souza

  2. Pingback: Analisando seu log file – Parte 02 | Vladimir M. B. Magalhães – Learn and Share

  3. Pingback: Analisando seu log file – Parte 03 | Vladimir M. B. Magalhães – Learn and Share

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