OK, a primeira coisa a entender é porque o arquivo de log é o tamanho que é.
Você diz que os dados são 500MB, tem certeza? Isso é um arquivo de dados muito pequeno em comparação com o arquivo de log. Se isso for verdade, o LOG é o tamanho que ele é POR UMA RAZÃO.
Então você tem que descobrir o porquê .. você está executando dados no banco de dados? Qualquer processo ETL / DTS executando dados, reconstruindo índices, etc, e grandes transações em execução? Você verificou que não há transações não confirmadas? (Selecione @@ trancount)
Você pode verificar o status atual do Log marcando log_reuse_wait e log_reuse_wait_desc.
SELECIONE [log_reuse_wait_desc]
FROM [mestre]. [Sys]. [Bancos de dados]
WHERE [name] = N'Company ';
A parte inativa do log não pode ser truncada até que todos os seus registros de log tenham sido capturados em um backup de log. Isso é necessário para manter a cadeia de log uma série de registros de log com uma seqüência ininterrupta de números de seqüência de log (LSNs). O log é truncado quando você faz backup do log de transações, supondo que as seguintes condições existam:
1.Um ponto de verificação ocorreu desde o último backup do log. Um ponto de verificação é essencial, mas não é suficiente para truncar o log no modelo de recuperação completa ou no modelo de recuperação de log em massa. Depois de um ponto de verificação, o log permanece intacto pelo menos até o próximo backup do log de transações.
2.Nenhum outro fator está impedindo a transação de log.
Geralmente, com backups regulares, o espaço de log é liberado regularmente para uso futuro. No entanto, vários fatores, como uma transação de longa duração, podem impedir temporariamente o truncamento de log.
A instrução BACKUP LOG não especifica WITH COPY_ONLY.
Fonte: link
Se o arquivo de log precisar desse tamanho e você diminuí-lo, ele só crescerá novamente e isso por si só é um processo caro, pois o banco de dados está gravando o log e precisa que o arquivo cresça a menos que você tenha instantâneo inicialização do arquivo ativada para a conta de serviço do sql server, o processo aguardará o crescimento do arquivo e, em seguida, ele será gravado. Se isso ocorrer em partes do GB, você terá problemas maiores para resolver.
Portanto, você precisa observar a atividade do Banco de Dados, se estiver executando apenas um backup de log por dia, por exemplo, isso pode não ser suficiente para a atividade do banco de dados. Na minha organização, faço backups a cada 15 minutos a cada 1 hora e, em alguns casos, em Db particulares a cada 5 minutos. isso permite que o Log não cresça muito (os logs foram pré-configurados em alguns servidores) também estamos executando RTO / RPO apertado.
agora, como joeqwerty declarou, a menos que você tenha um SLA apertado na caixa em relação aos pontos de recuperação, pode não valer a pena ser executado em FULL ou BULK-Logged, desperdiçando seu tempo, se tudo o que você fizer é executar um backup COMPLETO Diariamente e a empresa não se preocupa com a perda de dados, não lute contra isso, trabalhe com isso.
Se tudo o que importa é o tamanho do log, e não a recuperação do ponto de transcrição, faça o backup do log, reduza-o e mude para o Simple.
Você diz não sugerir recuperação simples, mas não acredito que entenda completamente o que está fazendo com a recuperação COMPLETA.
Se o tamanho do arquivo diminuído não for grande o suficiente, com base em quantas transações seu banco de dados tiver e com que frequência você fará o backup dos registros, ele crescerá novamente. Pode ser necessário executar backups de log com mais frequência para evitar isso.