Analisando seu log file – Parte 02

Continuando o assunto do post anterior, vou mostrar algumas formas de monitorar e lidar com a fragmentação de seu arquivo de log.

Inicialmente gostaria de listar um script que fiz para listar o número de VLFs de cada banco de dados, mostrando também quantos VLFs existem por cada tamanho (quantos de 256kb, quantos de 512kb, etc).

Esse script é útil para você analisar se precisa realizar manutenção no arquivo de log de algum de seus bancos de dados.

USE tempdb
GO

CREATE TABLE #LOGINFO
(
–recoveryunitid int, –só a partir do SQL Server 2012
Db_Name sysname not null default db_name(),
FileId tinyint,
FileSize bigint,
StartOffset bigint,
FSeqNo int,
Status tinyint,
Parity tinyint,
CreateLSN numeric(25,0)
);
GO

sp_msforeachdb’
USE ?

INSERT INTO #LOGINFO
(/*recoveryunitid,*/fileid, filesize, startoffset, fseqno, status, parity,createlsn)
EXEC (”DBCC LOGINFO”);

GO
–Em geral até 50 VLFs (Kimberly Tripp)
–Analisar quantidade e tamanho dos VLFs
;WITH DADOS(DB_NAME, SIZE_KB, QTDE)
AS
(
SELECT DB_NAME, FILESIZE/1024 AS SIZE_KB, COUNT(*) AS QTDE
FROM #LOGINFO
GROUP BY DB_NAME, FILESIZE/1024 WITH ROLLUP
)
SELECT DADOS.*,RM.*
FROM DADOS
CROSS APPLY(
SELECT RECOVERY_MODEL_DESC
FROM SYS.DATABASES D
WHERE D.NAME = DADOS.DB_NAME
) RM
–WHERE SIZE_KB IS NULL –Listar apenas os totais de VLFs de cada banco de dados
–WHERE DB_NAME = ‘BANCO’ –Listar apenas os VLFs de um banco de dados específico
ORDER BY 1,2 DESC,3 DESC

Ok, encontrei alguns bancos de dados com muitos VLFs e desejo fazer uma manutenção neles, como devo/posso proceder?

Bem, talvez a forma mais simples seja utilizar um DBCC SHRINKFILE para diminuir o transaction log para o mínimo possível (ficar apenas com os VLFs que foram criados no momento que o banco de dados foi criado, ou seja, os com valor zero na coluna CreateLSN) e depois aumentá-lo de forma a ficar com o número de VLFs que você deseja.

O processo será basicamente esse:

USE BANCO

GO

DBCC LOGINFO;

GO

DBCC SHRINKFILE(‘NOME_LOGICO_DO_ARQUIVO_DE_LOG’,TAMANHO_EM_MB)

GO

DBCC LOGINFO;

GO

Esse segundo DBCC LOGINFO serve para ver o número de VLFs restantes. Isso é necessário, pois mesmo com o SHRINK provavelmente o número de VLFs não irá diminuir até o mínimo possível, já que partes do seu log devem estar em uso e não podem ser reutilizadas (veja o que pode estar impedindo a reutilização dos VLFs na segunda tabela da parte 01 desse artigo).

Aqui vale uma outra informação. Quando você executa um DBCC SHRINKFILE em um arquivo de log, verá a coluna MinimumSize, relativa ao número de PÁGINAS (8kb) que o arquivo terá quando em seu tamanho mínimo. Você pode conferir e verá que esse valor multiplicado por 8kb será  o mesmo valor do tamanho do seu arquivo de log quando apenas restarem os VLFs de CreateLSN = 0 (zero).

Você pode utilizar o comando abaixo para analisar o que impede seu log de ser reaproveitado:

select NAME, LOG_REUSE_WAIT, LOG_REUSE_WAIT_DESC
from sys.databases where name = ‘BANCO’

Você deve então analisar as causas para o log não diminuir e tomar alguma ação (backup de log, parar/esperar por transações, etc) para então tentar novamente diminuir o log até o mínimo possível.

Após diminuir o log basta definir o tamanho e o número de VLFs que você deseja e então crescer seu arquivo de log de forma a alcançar o resultado desejado. Vale lembrar que o número de VLFs gerados está diretamente relacionado ao valor do crescimento do seu log (em quantos MBs você aumenta seu arquivo de log), de acordo com a tabela abaixo:

Crescimento Nº VLFs adicionados
< 64MB 4 VLFs
>= 64MB < 1GB 8 VLFs
>= 1GB 16 VLFs

Após alcançar o resultado esperado é hora de monitorar seus bancos de dados de forma a evitar que a fragmentação lógica dos logs aconteça novamente. Lembro que um dos pontos importantes para evitar a fragmentação lógica é a sua política atual de backup de logs.

Para concluir, caso você goste de Extended Events pode ler esse post do Jonathan Kehayias falando sobre como analisar o transaction log utilizando eles!

Fontes/Referências:

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

Uma resposta para Analisando seu log file – Parte 02

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

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