17 10 2014
Dica rápida: Compactando o arquivo de log do SQL Server
Você já tentou alguma vez compactar o log do SQL Server e o arquivo não diminuía? Essa situação se deve a um pequeno detalhe, que é o que veremos na dica rápida de hoje.
Disclaimer: Não sou DBA, e sim um simples desenvolvedor. Meu conhecimento de banco de dados não é aprofundado, então, se algum DBA achar que essa dica é uma má prática, por favor, poste um comentário.
Hoje em dia espaço em disco nas máquinas de desenvolvimento podem se tornar um recurso muito valioso. Com a popularidade dos SSDs aumentando, são grandes as chances de você ter que ficar lutando para conseguir um espaço a mais no seu disco. Esses dias atrás eu acabei ficando sem espaço na minha VM onde tenho os bancos de dados de teste, e a primeira ideia que me veio na cabeça foi fazer um shrink de alguns arquivos de log de alguns dos bancos. Porém, para a minha surpresa, ao fazer o procedimento de shrink, o arquivo não diminuía! Após pesquisar um pouco, acabei encontrando este artigo que apontou o motivo de isto estar acontecendo: How to Shrink SQL Server Transaction Logs.
Basicamente, o que precisamos fazer para que o log realmente seja reduzido é configurar o “Recovery Model” do banco como “Simple“. Para fazer isso, vá até as propriedades do banco, e na página “Options“, altere essa propriedade:
Feito isso, clique em OK. Depois disso, vá até a opção de “Shrink files” no seu banco de dados:
Mude o “File Type” para “Log” e veja que o tamanho do arquivo de log será diminuído em 97% nesse caso:
Pronto! Após isso o arquivo de log do SQL Server será reduzido. Veja o antes e depois:
Antes
Depois
Obviamente, não faça isso em um banco de produção! Eu, por exemplo, realizei esse procedimento no meu ambiente de bancos de dados de desenvolvimento para ganhar um pouco de espaço em disco.
Um ótimo final de semana e até a próxima dica!
André Lima
Image by tec_estromberg used under Creative Commons
https://www.flickr.com/photos/92334668@N07/11122773785/
Curiosidade aleatória: Como os alemães vendem abóbora para o Halloween Acessando a lista de contatos em aplicativos para a Windows Store
Valeu pela dica.
Estou iniciando no mundo dos bancos de dados, mas precisamente realizando o treinamento de sql server pelo curso adv, http://www.cursoadv.com.br
Acho muito legal estes post com dicas rápidas, elas ajudam muito a melhorar nosso desempenho. Me ajudou muito a nos estudos.
Parabéns pelo blog.
Olá Diego, muito obrigado pelo comentário! Fico feliz que essa dica tenha te ajudado nos seus estudos!
Caso queira ficar sempre por dentro dos artigos e dicas publicadas aqui no blog, inscreva-se na minha newsletter..
Abraço!
André Lima
Realmente essa pode ser uma dica útil em momentos de crise (por exemplo, falta de espaço em disco para outros arquivos importantes crescerem); porém, mudar o recovery model de FULL para SIMPLE ocasiona na quebra da sequencia do LSN; na prática, isso afeta a política de backup do banco de dados; logo, se de fato for necessário seguir com essa abordagem, após mudar para SIMPLE é EXTREMAMENTE recomendável que o DBA faça um backup completo (FULL) do banco de dados!
Outro ponto importante sobre o shrink: ele pode gerar grande fragmentação interna para o SQL Server, o que em geral poder ser muito ruim para a performance; mas enfim… se é preciso reduzir, que reduza; o importante é só ter a consciência dos possíveis impactos de cada ação :)
Bom trabalho,
Sr. SQuiLo
Olá Sr. SQuiLo! Muito obrigado pelo comentário!
Valeu demais por essa excelente explicação dos perigos de alterar o recovery model para SIMPLE.. Como mencionei no artigo, eu não sou DBA e não entendo direito dessas coisas, então, foi muito bom que você veio aqui nos comentários dar essa aula complementar..
No meu caso eu só estava precisando diminuir o tamanho do log em um servidor de desenvolvimento e na minha máquina local (utilizada somente para testes).. Em um ambiente em produção, sempre é bom deixar esse trabalho para alguém que manje mais dessas paradas..
Um grande abraço!
André Lima
Duas dúvidas.
1) Porque não fazer em um banco de dados em produção? Já que tenho um Job que realiza esse backup diariamente?
2) O meu banco lota o arquivo de log quase que a cada 3 ou quatro dias. Como posso aumentar a capacidade desse arquivo log? Ou existe uma maneira de desligar esse Log de transações?
Olá Eduardo!
Eu não recomendo fazer isso em um banco em produção porque eu não sei as consequências que isso pode trazer.. Sou desenvolvedor e não tenho qualificação para falar sobre essas consequências, por isso, deixei aquele disclaimer ali para evitar que alguém faça alguma besteira com banco de dados em produção..
Infelizmente eu não sei a resposta para a sua segunda pergunta.. Talvez valha a pena entrar em contato com algum DBA ou alguém que seja mais especialista em SQL Server.. Eu conheço o Pedro Galvão Junior que é especialista em SQL Server.. Talvez ele saiba responder essas dúvidas..
Abraço!
André Lima
Usei em produção no banco de dados… estava já sem funcionar e tinha o backup!
então usei e funcionou! obrigado!
Olá Fernando, obrigado pelo comentário!
Fico feliz que o tutorial tenha te ajudado..
Abraço!
André Lima
Bom dia Eduardo Lira.
Sei que faz um tempinho já mas aí vai a resposta para sua indagação.
Não é que não pode fazer na base de produção, você pode. Mesmo que você tenha uma job para o backup imagine que essa job foi executa 08hs da manhã. No final da manhã, devida a um problema de energia ou algo similar, uma queda no servidor, o seu banco de dados corrompeu, entrou em suspect. Sem o arquivo de Log vc não consegue restaurar até o momento do problema e com isso irá perder a manhã toda de trabalho, pois terá que restaurar o backup de 08hs.
Olá Jimerson!
Muito obrigado por ter passado aqui e dado esse complemento ao comentário do Eduardo! É bem melhor quando alguém que realmente entende do assunto vem aqui e explica os detalhes..
Um grande abraço!
André Lima
Ola Eduardo!
O Jimerson tem razão, mais o que pode ser feito par diminuir esta perda, seria colocar sua rotina para roda antes da rotina de backup full, dessa forma você colocaria a base para simple, executaria o shrink no log e depois alteraria para full. Ao terminar sua rotina executasse o backup Full, pronto!
Olá Marcio!
Muito obrigado por mais esse complemento! É muito bacana ver a galera trocando uma ideia aqui nos comentários desse artigo, um complementando as observações do outro.. Valeu!
Abraço!
André Lima
Alterar o banco para SIMPLE em qualquer hipótese NÃO É ACONSELHÁVEL. Como Jimerson explicou, caso haja uma rotina de backup sendo executada principalmente por meio de algum programa, como o Netbackup, a rotina irá falhar, e a recuperação pelo log em caso de desastre será inviável. Algo que ninguém falou e você pode fazer tranquilamente é alterar para BULK-LOGGED, este modo é mais utilizado para gravar registros em lote, mas assim como o FULL, ele não impacta nas rotinas de backup, uma solução.
Utilizo bastante um script que faz shrink de todos os logfiles, inclusive cria um job, irei o postar abaixo.
USE [msdb]
GO
/****** Object: Job [Shrinkfile Log – All Datafiles] Script Date: 08/22/2016 11:47:15 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/22/2016 11:47:15 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Shrinkfile Log – All Datafiles’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
/****** Object: Step [Shrinkfile all datafiles] Script Date: 08/22/2016 11:47:15 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Shrinkfile all datafiles’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=1,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’ DECLARE @TheBigShrink NVARCHAR(MAX)
SELECT @TheBigShrink = N””
SELECT @TheBigShrink = @TheBigShrink + ”USE [” + DB_NAME(dbid)
+ ”]
DBCC SHRINKFILE (N””” + name + ”””, 1)
”
FROM sysaltfiles
WHERE fileid = 2
AND dbid IN ( SELECT database_id FROM sys.databases Where state_desc =”ONLINE” )
EXEC (@TheBigShrink) — uncomment this line when happy with the output.
‘,
@database_name=N’master’,
@output_file_name=N’G:\MSSQL10_50.SISLOC\MSSQL\Log\Shrinkfile_all_datafiles.log’,
@flags=0
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’A cada hora’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160822,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Olá Sergio!
Muito obrigado por complementar os comentários deste artigo com a sua experiência e recomendação.. Como eu mencionei no artigo, eu não sou DBA nem tenho experiência profunda com bancos de dados.. Eu só faço esse tipo de operação em bancos de teste / desenvolvimento para ganhar espaço em disco.. Obviamente não faço isso em bancos de produção (nem sou o responsável por eles).. Só compartilhei essa dica para, quem sabe, ajudar outras pessoas que estejam nessa mesma situação..
Valeu demais por compartilhar o script que você utiliza para fazer o shrink dos arquivos de log!
Abraço!
André Lima
Galera,como ja foi mencionado acima, um dos grandes problemas de mudar o recovery para SIMPLE, é quando você precisar dos LOGS para voltar um banco de dados…
Olá Fernando!
Muito obrigado por reiterar essa colocação.. Como mencionei anteriormente, minha sugestão foi para utilizar essa metodologia em bancos de desenvolvimento.. Em produção eu nunca alteraria uma configuração dessas..
Abraço!
André Lima