Antes de marcar imediatamente como duplicado , li o livro de Mike Walsh Por que o log de transações continua crescendo ou fica sem espaço? , mas acho que não deu uma resposta para a minha situação. Examinei uma dúzia de perguntas semelhantes, mas as relevantes diziam apenas "duplicado" e apontavam para a pergunta de Mike.
Detalhes: Eu tenho um monte de bancos de dados de ~ 500 MB no SQL Server 2008 R2, todos no modo de recuperação SIMPLE (não é minha escolha), backups completos noturnos, com arquivos de dados de ~ 200 MB e arquivos de log de ~ 300 MB. O log não cresce para 300 MB imediatamente, mas lentamente ao longo de alguns meses. Não há transações abertas em nenhum deles, pelo menos de acordo com sp_who2 e o monitor de atividade. Se eu clicar com o botão direito do mouse no banco de dados e selecionar propriedades, ele me informará que há ~ 50 MB livres. Particularmente logo após um backup, todo o log não deveria estar livre? No modo SIMPLE o log não deveria estar livre enquanto não houver uma transação aberta?
log_reuse_wait_descde sys.databasesdiz diz "NADA", que com base na pergunta e resposta referenciada acima diz que não deve esperar nada para reutilizar o espaço.
Se eu fizer 'DBCC SHRINKFILE', o arquivo de log será reduzido para 1 MB, então ele estará disposto a recuperar o espaço. Posso configurar algo que reduza os logs semanalmente e evite que as coisas fiquem fora de controle, mas estou confuso quanto ao motivo pelo qual o SQL Server me obrigaria a fazer isso.
Eu posso entender se houve alguma transação maluca que precisou de 300 MB para registrá-la, mas não estamos fazendo nada extremo, apenas OLTP básico. Da pergunta/resposta de Mike:
Modelo de Recuperação Simples - Então, com a introdução acima, é mais fácil falar primeiro sobre o modelo de Recuperação Simples. Neste modelo, você está dizendo ao SQL Server - estou bem com você usando seu arquivo de log de transações para recuperação de falha e reinicialização (você realmente não tem escolha.. Procure as propriedades ACID e isso deve fazer sentido rapidamente), mas uma vez que você não precisar dele para esse propósito de recuperação de falha/reinicialização, vá em frente e reutilize o arquivo de log.
O SQL Server escuta essa solicitação no Simple Recovery e mantém apenas as informações necessárias para fazer a recuperação de falha/reinicialização. Quando o SQL Server tiver certeza de que pode se recuperar porque os dados estão protegidos no arquivo de dados (mais ou menos), os dados que foram protegidos não são mais necessários no log e são marcados para truncamento - o que significa que são reutilizados.
Ele continua dizendo que o espaço de log deve ser reutilizado, mas com esse crescimento lento ao longo dos meses, não parece que seja.
o que estou perdendo? Algo está impedindo o SQL Server de reconhecer os dados como "protegidos" e liberar o log?
(editar) The After Action Report - AKA um pouco de conhecimento é perigoso
Depois de descobrir que esta é uma "pergunta popular", parecia que eu devia uma explicação sobre o que aconteceu há 7 meses e o que aprendi para salvar algumas outras pessoas de algum sofrimento.
Em primeiro lugar, o espaço disponível que você vê no SSMS quando visualiza as propriedades em um banco de dados é o espaço disponível no arquivo de dados. Você pode visualizar isso executando o seguinte em um banco de dados e descobrirá que o espaço disponível relatado pelo SSMS é a diferença entre o FileSizeMB e o UsedSpaceMB:
SELECT
DB.name,
MF.physical_name,
MF.type_desc AS FileType,
MF.size * 8 / 1024 AS FileSizeMB,
fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB,
mf.name LogicalName
FROM
sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.name = 'yourdatabasename'
Isso confirmou que, em circunstâncias normais, estávamos usando muito pouco espaço de log (20 MB ou menos), mas isso leva ao segundo item ...
Em segundo lugar, minha percepção do crescimento das toras foi lenta ao longo do tempo. No entanto, na realidade, os logs estavam crescendo rapidamente nas noites em que o cara responsável pela aplicação de patches para este aplicativo de terceiros estava aplicando patches. O patch foi feito como uma única transação, portanto, dependendo do patch, os dados de 200 MB precisavam de 300 MB de log. A chave para rastrear isso foi a consulta de Aaron Bertrand em https://sqlblog.org/2007/01/11/reviewing-autogrow-events-from-the-default-trace
DECLARE @path NVARCHAR(260);
SELECT
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT
DatabaseName,
[FileName],
SPID,
Duration,
StartTime,
EndTime,
FileType = CASE EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
EventClass IN (92,93)
ORDER BY
StartTime DESC;
Isso mostrou que o log estava crescendo em certas noites, quando o cliente não estava usando o banco de dados. Isso levou à conversa com o cara que aplicava os patches e a resposta para o mistério.
Obrigado novamente pelas pessoas que forneceram ajuda para me obter a resposta.