Consultoria DBA SQL Server

Aprenda a descobrir quais são os maiores arquivos Transaction Log do SQL Server.

O que são Transaction logs?

Para quem não sabe ainda o que são os transaction logs, é bem simples. Em resumo todos os bancos de dados de SQL Server possui um arquivo transaction log e basicamente o que esse arquivo faz é o seguinte: Ele é responsável por guardar todas as transações que ocorreram no seu banco de dados. Ou seja, se você fizer um update, delete, ou insert, isso é gravado nesse arquivo.

Essas informações são apagadas desse arquivo de transação apenas quando é feito um backup transaction log no seu banco de dados.

Então o que faz esse arquivo crescer tanto? Basicamente a quantidade de transações que são feitas no seu banco de dados é o que faz crescer tanto o arquivo de transaction log.

O que isso causa se eu não fizer o backup dele? Chegará uma hora que você não terá mais espaço em disco no seu servidor, parando completamente todas as outras operações do banco de dados. Então você pode diminuir o crescimento fazendo os backups desses arquivos.

Em um outro artigo eu vou explicar melhor de quanto em quanto tempo é recomendável você fazer o backup dos seus arquivos transactions log, depende muito da criticidade do negócio, conheço lugares que fazem backups de 5 em 5 minutos e lugares que fazem de hora em hora ou até mesmo de 12 em 12 horas, tudo depende de vários fatores.

Mas o que nós queremos falar aqui é como encontrar os maiores arquivos de transaction log do SQL Server. De forma bem simples, vou deixar um script que verifica na sys.master_files os arquivos que são maiores que 10 GB, o que eu considero um tamanho preocupante para um arquivo de log.

Você pode alterar o filtro de acordo com a sua necessidade, basta alterar o valor do parâmetro @tamanhoDoLog.
Script:

 USE master
 GO
 DECLARE @tamanhoDoLog INT;
 SET @tamanhoDoLog = 10240; -- Tamanho do arquivo em MB
 SELECT name,
 state_desc,
 physical_name
 FROM master.sys.master_files
 WHERE type_desc = 'LOG'
 AND ( ( [size] * 8 ) / 1024 ) > @tamanhoDoLog
 AND database_id = DB_ID();

Grande Abraço!