Como gerenciar o tamanho do arquivo de log em um banco de dados grande e de uso pesado

Se você nunca prevê fazer uma restauração point-in-time, então você só precisa fazer 2 coisas:

1. Altere o banco de dados para o modo de recuperação SIMPLE
2. Faça grandes mudanças em pequenos lotes .

No modo de recuperação SIMPLE, o arquivo de log é utilizado apenas para transações em processo . Quando a transação for concluída, o espaço no log tran será marcado para reutilização e a próxima transação apenas reutilizará o espaço*.

(* Isso não é estritamente verdade, existem "pontos de verificação" executados ocasionalmente que realmente liberam o espaço para reutilização, mas na maioria das vezes funciona sem intervenção; apenas DBAs meticulosos provavelmente se importarão com os detalhes internos aqui.)

Portanto, mantenha suas transações pequenas:

• Se você precisar deletar todas as linhas de uma tabela, não faça um DELETEde 100m de linhas (isso vai explodir o log), faça um TRUNCATEem vez disso, ou apenas DROPa tabela e recrie-a.
• Se você não puder fazer isso (você precisa excluir apenas algumas linhas), faça isso em lotes menores
• Ao fazer grandes importações de dados, aprenda sobre importações em massa ou um SSIS Fast Loads que carrega dados rapidamente e os confirma de vez em quando
• A mesma coisa para atualizações, não atualize todas as linhas de uma tabela massiva, use WHEREcláusulas mais seletivas para limitar suas atualizações a ~ 100k linhas por vez. Aprenda a usar cursores no SQL

Os detalhes exatos dependerão do seu banco de dados real e da atividade real, é claro, mas um banco de dados com 1 TB de dados não deve ter um arquivo de log tran maior que, digamos, 100 Gb, dependendo do tamanho de seus lotes.

Com relação à redução do arquivo existente, você pode usar o script a seguir para ver o espaço livre/usado em seus dados e arquivos de log:

SELECT DB_NAME() as dbname, type_desc, name as logical_name, 
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;

Se o espaço usado no log for alto, você provavelmente ainda terá transações abertas. Procure a ferramenta de terceiros sp_WhoIsActivepara explorar sua atividade atual/transação aberta.

Quando estiver pronto para encolher, escolha um tamanho de destino razoável e execute um SHRINKFILE:

DBCC SHRINKFILE (NAME = 'mylogfile', SIZE = 100000)

(Esse tamanho está em MB).

Se o arquivo de log ainda crescer, volte para sp_WhoIsActivever quais transações estão sendo executadas por tanto tempo e descubra como fazê-las em lotes.

Só quero emendar mais uma coisa.

Pode ser importante lembrar ao autor original o fato de que "Não (transação) arquivo de log, por favor" é simplesmente impossível de alcançar - ao contrário de outros mecanismos de log de alguns serviços de rede simples que podem ser completamente desabilitados (servidores da web, por exemplo), logs de transações nunca podem ser descartados, mesmo no modelo de recuperação Simples. Impor um limite de tamanho no arquivo de log de transações não é recomendado, pois isso o levará ao erro 9002 quando você executar acidentalmente alguns lotes enormes. Em vez disso, seria melhor ficar de olho no tamanho do lote que você executa no banco de dados SQL Server, contendo assim o arquivo de log de transações em um tamanho aceitável.

Aqui está para se livrar do log de 1 TB para Non-DBA ou New-DBA. Supondo que você tenha o SSMS instalado.

Clique com o botão direito do mouse no banco de dados tem um arquivo de log enorme.

Por favor, escolha Arquivos -> Tipo de arquivo: Log, você verá 'Espaço livre disponível:'. Em ação Reduzir, Reorganizar páginas antes de liberar espaço não utilizado, Reduzir arquivo para ? MB(mínimo é 0 MB). Depois de especificar o valor, clique em OK.