Sou um novo DBA e estou gerenciando uma instância do SQL Server 2012 que tem bastante atividade. Estou executando no modo de recuperação total porque precisamos de uma recuperação pontual.
No momento, estou fazendo um backup completo dos bancos de dados e logs todos os dias às 5h. Alguns dos arquivos de log aumentaram para 300 GB e, mesmo depois de fazer um backup, eles não diminuem de tamanho. Posso reduzi-los de tamanho executando algo semelhante a:
BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';
DBCC ShrinkFile([db1_log], 0);
BACKUP LOG db1 TO DISK = '\\server\share\db1_log2.trn';
DBCC ShrinkFile([db1_log], 0);
BACKUP LOG db1 TO DISK = '\\server\share\db1_log3.trn';
DBCC ShrinkFile([db1_log], 0);
Quando verifico os LSNs dos arquivos de backup, vejo algo como:
RESTORE headeronly FROM DISK = N'\\server\share\db1_log1.trn'
FirstLSN: 15781000014686200001
SecondLSN: 15802000000665000001
RESTORE headeronly FROM DISK = N'\\server\share\db1_log2.trn'
FirstLSN: 15802000000665000001
SecondLSN: 15805000000004100001
RESTORE headeronly FROM DISK = N'\\server\share\db1_log3.trn'
FirstLSN: 15805000000004100001
SecondLSN: 15808000000004200001
Não acredito que estou quebrando minha cadeia de log encolhendo os arquivos de log. Lendo sobre isso, acredito que estou prejudicando meu desempenho porque esses arquivos de log encolhidos precisam crescer novamente.
Perguntas:
- Por que o arquivo de log não diminui após meus backups? É porque há transações não confirmadas?
- A princípio, pensei que deveria reduzir os arquivos de log após cada backup às 5h. Depois de ler como isso é ruim para o desempenho, agora acredito que preciso fazer backups de log regulares a cada duas horas durante o dia. Isso está correto?
- Meu backup completo normal do banco de dados/logs acontece todos os dias às 5h e às vezes leva 3 horas. Se eu agendar os backups de log para acontecer a cada hora, o que acontecerá quando o backup de log colidir com o backup das 5h?
O arquivo de log NTFS real não "encolhe" de um backup de log de transação, mas os VLFs (arquivos de log virtual) dentro do log de transação são marcados para reutilização (porque agora eles são copiados e persistidos na mídia) permitindo o wrap-around de uso do log de transação para ocorrer. Se você não estiver fazendo backup do log de transações ou não com frequência suficiente, não haverá VLFs disponíveis e isso fará com que o log de transações cresça (desde que o crescimento automático esteja definido) para acomodar entradas de log de transações adicionais.
A redução rotineira e programada de arquivos não é uma boa ideia. Somente quando você precisar recuperar o espaço necessário, você deve considerar um arquivo
DBCC SHINKFILE. Além disso, quando você aumenta continuamente seu log de transações, pode estar atrapalhando outras coisas, como a recuperação do banco de dados. Com muitos VLFs no log de transações (um problema comum quando o log de transações é aumentado apenas por um pequeno incremento de armazenamento), a quantidade de tempo para recuperar o banco de dados pode ser maior do que o desejado.Não vai acontecer nada, essa é uma operação totalmente legal. Veja este gráfico abaixo do MSDN . Onde houver um ponto preto, essas duas operações não podem ocorrer ao mesmo tempo. Como você pode ver, um backup de banco de dados e um log de transações são permitidos simultaneamente.
A conclusão aqui é que você deve fazer backup do log de transações com mais frequência. O crescimento do arquivo NTFS não é o único problema que você pode encontrar ao não fazer backup do log de transações com mais frequência. Se você tiver uma falha de armazenamento e seu log de transações for perdido, você só poderá restaurar para o ponto no tempo de seu último backup de log de transações. Se o log de transações for perdido, você não poderá fazer backup do final do log e restaurar para o ponto no tempo da falha. No seu caso, você pode perder 24 horas de dados. Mas se você fizer backup de seus logs de transações a cada, digamos, 30 minutos, sua perda máxima de dados seria de 30 minutos. Nesse caso, se o log de transações sumir e você tiver o backup completo e a cadeia de log intacta, poderá restaurar o último backup de log.
Documentação do TechNet sobre truncamento de log de transações
O principal problema com o qual você está lidando é que você está fazendo backup de seus logs uma vez por dia. O comportamento do mecanismo é que os registros de log (espaço usado) dentro de um arquivo de log só serão removidos após um backup de log bem-sucedido. Este espaço é recuperado quando ocorre um ponto de verificação, mas se seu banco de dados estiver em recuperação Full/Bulk Logged, os registros de log só serão removidos se tiverem sido copiados com sucesso.
Os backups de log devem ser usados em conjunto com os backups completos e devem ser executados em um intervalo regular entre os backups completos. Esse intervalo pode ser qualquer período, embora eu normalmente execute backups de log a cada 15 minutos. Seu intervalo depende do objetivo do ponto de recuperação (RPO) e da quantidade de dados que você pode perder no caso de uma recuperação.
Se você estiver executando backups de log regulares, não precisará realizar reduções de arquivo regulares porque estará gerenciando o espaço do arquivo de log antes que ele seja forçado a crescer.
Eu tenho o mesmo problema que você antes. Meu arquivo de log sempre aumenta, em vez disso, eu uso o backup completo do banco de dados todas as noites. Então aqui está a minha solução:
faça backup do seu arquivo de log atual.
Defina seu banco de dados para recuperação simples
Reduza seu arquivo de log para 1 MB ou menos (você decide)
Defina seu banco de dados de volta para Full Recovery.
Espero que seja ajuda
Phong Tran