Neste artigo estaremos interessados em fazer um estudo com relação ao gerenciamento dos logs de transação, os quais são muito úteis em tempos difíceis que podemos ter com o mal funcionamento de nossas bases de dados. Teremos então uma visão geral sendo apresentada nesse momento e posteriormente abordaremos pontos mais precisos com relação a utilização dos logs para que possamos ter a compreensão correta na hora em precisarmos utilizá-los.
Uma abordagem geral sobre os logs de transação
Podemos entender por logs de transação sendo este um arquivo no qual o SQL Server armazena um registro com todas as transações realizadas e dados que foram modificados no banco de dados com o qual o arquivo de log possa estar associado. Isso é bem necessário para que em casos que o SQL Server seja desligado inesperadamente, como uma falha da instância ou mesmo hardware, o log de transação é utilizado para recuperar o banco de dados, com a integridade dos dados. Após o reinício, um banco de dados entra em um processo de recuperação em que o log de transação é lido para garantir que todos os dados sejam válidos, onde os dados comitados serão escritos nos arquivos de dados, já em casos em que quaisquer transações não sejam parciais, estas são desfeitas (realização de rollbacks). Em suma, o log de transação é o meio fundamental pelo qual o SQL Server garante a integridade da base de dados e as propriedades ACID de transações. No papel de DBA, temos algumas tarefas importantes quanto a gestão de logs de transação, sendo estes os seguintes:
- A escolha de um modelo de recuperação correto – O SQL Server oferece três modelos de recuperação de banco de dados, que são: o FULL (como padrão), simples e em massa registrada (Bulked Logged). O DBA, no entanto, deve escolher o modelo adequado de acordo com os requisitos de negócio para o banco de dados e, em seguida, estabelecer procedimentos de manutenção adequados.
- Fazer backups do log de transação – É de vital importância que o DBA realize backups regulares do log de transação. Uma vez capturado em um arquivo de backup, os registros podem ser posteriormente aplicados a um backup de banco de dados completo, a fim de executar uma restauração do banco de dados, e assim recriar o banco de dados como ele existia em um ponto anterior no tempo, como por exemplo, antes de uma falha.
- Monitorar e gerenciar o crescimento de registro - Em um banco de dados o log de transação pode crescer rapidamente. Se não for feito um backup regularmente, ou se as características de crescimento forem incorretas ou inadequadas para o porte, o arquivo de log de transação pode “encher”, levando ao erro "9002" (que é um log de transação completo), o que coloca o SQL Server em um modo de "somente leitura" (ou em modo de "recurso pendente", se isso acontecer durante a recuperação).
- Otimizar a transferência de registro - Além da manutenção básica, como fazer backups, o DBA deve tomar medidas para garantir o desempenho adequado do log de transação. Isto inclui considerações de hardware, bem como evitar situação como a fragmentação de registros, o que pode afetar o desempenho das transações.
Neste artigo, estaremos considerando cada uma destas tarefas de manutenção em detalhes. No nosso primeiro momento, começaremos com uma visão geral de como o SQL Server usa o log de transação, e duas das formas mais significativas de impactos na vida de um DBA, ou seja, a restauração e a recuperação de banco de dados e gerenciamento de espaço em disco.
Logs de transação e SQL Server
No SQL Server, o log de transação é um arquivo físico, identificado convencionalmente, embora não obrigatoriamente, pela extensão FDL. Ele é criado automaticamente na criação de um banco de dados, juntamente com o arquivo de dados principal, comumente identificado pela extensão de MDF, que armazena os objetos de banco de dados e os dados em si. O log de transação, enquanto ele é implementado como um único arquivo físico geralmente, ele também pode ser implementado como sendo um conjunto de arquivos. No entanto, mesmo neste último caso, ainda é tratado pelo SQL Server como um único arquivo de forma sequencial e, como tal, o SQL Server não pode e não escreve em paralelo com vários arquivos de log, e por isso não há vantagem com relação a desempenho que possa ser tido a partir da implementação do log de transação com vários arquivos.
Sempre que é realizada uma alteração com o código T-SQL em um objeto de banco de dados (DDL), ou mesmo, os dados que ele contém, não são só os dados ou objeto que são atualizados no arquivo de dados, mas também os detalhes da mudança que são registrados como um registro de log na transação log. Cada registro de log contém detalhes sobre o ID da transação que fez a mudança, quando essa operação começou e quando ela terminou, quais páginas foram alteradas, as alterações de dados que foram feitas, dentre outras informações relevantes. O que precisamos entender também é que o log de transação não é uma trilha de auditoria. Ele não fornece uma trilha de auditoria das alterações feitas ao banco de dados, não mantém um registro dos comandos que foram executados no banco de dados, assim como os dados alterados como resultado.
Quando uma modificação de dados é feita, as páginas de dados relevantes são lidas a partir do cache de dados, ou serão recuperados primeiro do disco caso eles não estejam no cache. Os dados são modificados no cache de dados, e os registros de log para descrever os efeitos da operação são criados no cache de log. Quando uma transação é confirmada, os registros de log são gravados no log de transação, no disco. No entanto, os dados que foram alterados não podem ser gravados no disco até que ocorra o checkpoint do banco de dados. Qualquer página em cache que foi modificada desde a sua leitura a partir do disco de modo que o valor dos dados em cache seja diferente do que está no disco é chamada de “página suja”. Estas páginas sujas podem conter os dados que foram comprometidos para o arquivo de log de transação, mas ainda não para o arquivo de dados e os dados modificados por operações abertas, ou seja, aqueles que ainda não tenham sido cometidos (ou revertida (roolback)).
Os checkpoints da base de dados realizam um scaneamento do cache e liberam todas as “páginas sujas” do disco, neste momento, as modificações são refletidas no arquivo de dados físico, bem como no arquivo de log. Isso acontece mesmo nos casos em que a transação ainda está aberta. Durante um checkpoint, as páginas sujas relacionadas para abrir as transações são liberadas para o disco, o SQL Server sempre assegura que os registros de log referentes a essas transações abertas sejam liberados a partir do cache de log para o arquivo de log de transação antes que as páginas sujas sejam liberadas para os arquivos de dados. Outro processo que verifica o cache de dados, é o LazyWriter, que também pode escrever páginas de dados no disco, de fora de um checkpoint, se forçado a fazê-lo por pressões de memória.
O ponto importante que devemos observar aqui é que o gerenciador de log de buffer sempre garante que as descrições de mudança (registros de log) sejam escritas no log de transação, no disco, antes de as páginas de dados serem escritas para os arquivos de dados físicos. Este mecanismo é denominado log (ou registro) de write-ahead. É essencialmente o mecanismo pelo qual o SQL Server garante durabilidade em suas transações.
Devido ao fato de sempre escrever as alterações no arquivo de log em primeiro lugar, o SQL Server tem a base de um mecanismo que possa garantir que os efeitos de todas as transações confirmadas sejam então refletidos nos arquivos de dados, e que qualquer modificação de dados no disco que se originar de transações incompletas, ou seja, aqueles para os quais nem um COMMIT ou um ROLLBACK foram emitidos em última análise, não serão refletidos nos arquivos de dados.
Se um banco de dados quebra, por exemplo, depois de uma transação (T1) ser comprometida, mas antes de os dados afetados serem escritos para o arquivo de dados, em seguida, durante a reinicialização, o processo de recuperação de banco de dados é iniciado, que tenta conciliar o conteúdo das operações de arquivo de log e os arquivos de dados. Ele irá ler o arquivo de log de transação e garantir que todas as operações que compõem a transação T1, registrada no arquivo de log, seja refeito para que sejam refletidas nos arquivos de dados.
Da mesma forma, após uma quebra do banco de dados, o processo de recuperação irá "reverter" (desfazer) quaisquer alterações de dados no banco de dados que estão associados a transações não confirmadas, lendo as operações relevantes do arquivo de log e executar a operação inversa física sobre os dados. Desta forma, o SQL Server pode retornar o banco de dados para um estado consistente. De modo mais geral, o processo de reversão (desfazer) ocorre da seguinte forma:
- Um comando ROLLBACK é emitido para uma transação explícita
- Um erro ocorre e o XACT_ABORT será ligado
- Se o banco de dados detectar que a comunicação foi interrompida entre o banco de dados e o cliente que iniciou as transações.
Em tais circunstâncias, os registros referentes a uma transação interrompida, ou aquele para o qual o comando ROLLBACK é explicitamente emitido, são lidas e as mudanças são revertidas. Dessa forma, o SQL Server garante que todas as ações associadas a uma operação de sucesso, como uma unidade, ou que todos eles falham. Como tal, o log de transação representa um dos meios fundamentais pelos quais o SQL Server garante a consistência e integridade dos dados durante a operação normal do dia a dia.
No entanto, o log de transação desempenha um outro papel, o papel vital na medida em que fornece o mecanismo pelo qual o banco de dados pode ser restaurado para um ponto anterior no tempo, em caso de uma quebra. Com planejamento e gestão adequados, podemos usar backups destes arquivos de log para restaurar todos os nossos dados onde eles se tornaram danificados ou inutilizáveis.
Log de transação do banco de dados e restauração
Como dito anteriormente, um arquivo de log de transação armazena uma série de registros de logs, sequencial de acordo com quando a transação começou, que dessa forma, fornecem um registro histórico das modificações e operações que tenham sido emitidas contra esse banco de dados. Cada registro de log contém detalhes sobre o ID da transação que fez a mudança, quando essa operação começou e terminou, quais páginas foram alteradas, as alterações de dados que foram feitas, e assim por diante. Os registros de log no arquivo de log de transação são organizados em várias seções, que são chamadas de Virtual Log Files (VLF).
O Mecanismo de log write-ahead do SQL Server garante que a descrição de uma modificação (ou seja, o registro de log) seja gravada em um VLF antes que os dados modificados sejam escritos para o arquivo de dados. Assim, um registro de log pode conter detalhes de uma transação fechada (ou seja, comitada) ou uma transação aberta (não comitada), e em cada caso, os dados modificados pela transação podem ou não podem ter sido escritos para os arquivos de dados, dependendo de onde ocorreu um checkpoint ou não. O processo de verificação de banco de dados controla a quantidade de trabalho que o SQL Server precisa fazer durante uma operação de recuperação de banco de dados. Se o SQL Server teve que dar continuidade as mudanças para um enorme número de transações comitadas relacionadas com páginas sujas, então o processo de recuperação poderá ser muito mais demorado.
Qualquer registro de log referente a uma transação aberta pode ser necessário para uma operação de reversão, durante a recuperação, e sempre vai ser uma parte do que é chamado de um VLF ativo e devido a isso, sempre será retido no arquivo de log. Um registro de log referente a uma transação fechada também será parte de um VLF ativa, até atingir o ponto em que não há registros de log em todo o VLF que esteja associado com uma transação aberta, onde o VLF passa a ser inativo.
Os registros de log contidos nessas VLF inativas essencialmente fornecem um "histórico" das transações de banco de dados previamente preenchidas, e o que acontece com essas VLFs inativas varia de acordo com o modelo de recuperação do banco de dados. Como exemplo, podemos citar que se estivermos usando a forma total do modelo de recuperação de banco de dados (ou BULK Logged), então o log de transação mantém os registros de log em VLF inativos, até que um backup do log seja tomado, o que trataremos posteriormente.
Ao fazer o backup do log de transação, podemos então capturar em um arquivo de backup todos os registros de log no log no momento da execução, incluindo os dessas VLFs inativas. Esses backups do log podem ser usados para restaurar o banco de dados para um ponto no tempo anterior; o que para nós seria muito melhor se ocorresse próximo a um ponto no tempo muito próximo do ponto em que as quebras ocorreram. No caso de uma quebra, os arquivos de backup de log podem ser aplicados a uma cópia restaurada de um arquivo de backup de banco de dados completo, e todas as transações que ocorreram após o backup completo serão executadas, durante a recuperação do banco de dados, para recuperar o banco de dados e restaurar os dados para um determinado ponto no tempo, e assim minimizarmos qualquer perda de dados. Naturalmente, isso pressupõe que não tenhamos apenas tomado esses backups de log, mas também que os transferimos para locais seguros. Se os seus arquivos de backup do log estão na mesma unidade que o arquivo de registro, consequentemente onde tem as falhas no disco, então podemos perder todos os nossos backups, e estaremos perdidos!
Quando um banco de dados está no modelo de recuperação simples, os registros de log existentes nos VLF ativos são mantidos, uma vez que podem ser necessários para uma operação de reversão (Rollback). No entanto, os VLF inativos serão truncados quando um checkpoint ocorrer, o que significa que os registros de log nessas VLF podem ser imediatamente substituídos por novos registros de log. É por isso que um banco de dados operando em recuperação simples é referido como estando em modo de auto truncado. Neste modo, nenhuma descrição de fatos passados é mantida no registro e por isso não pode ser capturada em um backup do log e usada como parte do processo de restauração.
Controlando o tamanho do arquivo de log
De acordo com o ponto anterior, podemos ter certeza então de que precisamos manter sempre um backup atualizado e seguro para casos de emergência. No entanto, há uma segunda razão importante a se tomar com relação a esses backups do log quando estiverem operando em sua totalidade (ou em BULK_LOGGED) que é o controle do tamanho do log. Lembrem-se que um registro de log é gravado no arquivo de log para cada transação que modifica os dados ou objetos em um banco de dados SQL Server. Em um sistema, com muitas transações simultâneas, ou aqueles que escrevem um monte de dados, o log de transação pode crescer em tamanho muito rapidamente.
Quando trabalhamos em modo full (ou BULK_LOGGED), a captura em um arquivo de backup de uma cópia dos registros de log em VLF inativas, é a única ação que vai fazer essas VLFs elegíveis para um truncamento, o que significa que o espaço ocupado pelos registros de log se tornam disponíveis para reutilização.
Algo que é necessário saber com relação ao truncamento e ao tamanho dos logs de transação é que há um mal entendido comum que diz que truncar o arquivo de log significa que os registros de log são excluídos e o arquivo diminui de tamanho... Ele não é assim. O truncamento de um arquivo de log é simplesmente o ato de marcar o espaço como disponível para reutilização. Portanto, uma das razões por que é vital para execução de backups do log de transação regulares ao se trabalhar em modo full (ou BULK_LOGGED) é manter o controle com relação ao tamanho do log. Agora vamos a um exemplo prático para começarmos a entender o processo de logs de transação.
Um breve exemplo de fazer o backup do log de transação
A fim de apresentarmos uma forma breve com relação a alguns dos conceitos que discutimos neste artigo, será feito um exemplo muito simples de como fazer backup do log de transação para um banco de dados operando em modo de recuperação total. Iremos então criar uma nova base de dados chamada de TesteDB, utilizando o SQL Server 2014, mas podem utilizar outras versões, em seguida, obteremos o tamanho do arquivo de log inicial usando o comando SQLPERF (LOGSPACE). O código será disposto como o apresentado pela Listagem 1.
Listagem 1. Tamanho do arquivo de Log inicial para a tabela de testes criada.
USE master ;
IF EXISTS ( SELECT name
FROM sys.databases
WHERE name = 'TesteDB' )
DROP DATABASE TesteDB ;
CREATE DATABASE TesteDB ON
(
NAME = TesteDB_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TesteDB.mdf'
) LOG ON
(
NAME = TesteDB_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TesteDB.ldf'
) ;
DBCC SQLPERF(LOGSPACE);Como vocês podem ver, o arquivo de log tem aproximadamente 1 MB de tamanho, e cerca de 30% completo, como apresentado pela Figura 1. Não esqueçam de mudar os filenames para o caminho existente em sua máquina, pois caso contrário não irá funcionar. As características iniciais de tamanho e crescimento dos bancos de dados do usuário criados em uma instância são determinadas pelas propriedades do banco de dados do modelo, como é o modelo de recuperação padrão que cada banco de dados irá utilizar (no nosso caso, o FULL).
Figura 1. Tamanho do arquivo de log TesteDB.
Agora que temos nosso arquivo criado, vamos fazer um backup do arquivo de dados para TesteDB, como mostrado pela Listagem 2, para isso, criaremos primeiro o diretório "backups" na unidade C. Notem aqui que esta operação de backup garante que o banco de dados realmente está operando em modo de recuperação total.
Listagem 2. Criação da pasta de backups para a realização do backup.
-- backup completo da base de dados
BACKUP DATABASE TesteDB
TO DISK ='C:\Backups\TesteDB.bak'
WITH INIT;
GOAté o momento não há nenhuma mudança no tamanho dos dados ou no arquivo de log, como resultado desta operação de backup ou mesmo na porcentagem de espaço de log utilizado, o que talvez não seja surpresa dado que não há tabelas de usuário ou dados no banco de dados ainda. Vamos então criar uma tabela chamada LogTeste no banco de dados, preenchê-lo com 500 mil registros, e reavaliar o tamanho do arquivo de log, como mostrado na Listagem 3. Não há a necessidade de nos preocuparmos no momento com os detalhes do código.
Listagem 3. Criação do script para geração de registros.
USE TesteDB ;
GO
IF OBJECT_ID('dbo.LogTeste', 'U') IS NOT NULL
DROP TABLE dbo.LogTeste;
-- "ID" possui um range def 1 até 500000 de numeros unicos
-- "Inteiros" possui um range def 1 até 50000 de numeros não unicos
-- "texto";"AA"-"ZZ" string de 2 caracteres
-- "dinheiro"; 0.0000 to 99.9999 valores monetários
-- "Date" ; >=01/01/2000 and <01/01/2010 datas.
SELECT TOP 500000
ID = IDENTITY( INT,1,1 ),
Inteiros = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,
texto = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
+ CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,
dinheiro = CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS MONEY) ,
Date = CAST(RAND(CHECKSUM(NEWID())) * 3653.0 + 36524.0 AS DATETIME)
INTO dbo.LogTeste
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2 ;
DBCC SQLPERF(LOGSPACE);Observem aqui que o tamanho do arquivo de log cresceu consideravelmente, como apresentado pela Figura 2, para quase 20MB e o registro é de 99% completo (os números podem ser ligeiramente diferentes em seu sistema). Se fôssemos inserir mais dados, ele teria que crescer em tamanho novamente para acomodar mais registros de log.
Figura 2. Registro de log alterado.
Caso tenham interesse, rodem novamente o script apresentado pela Listagem 2, para fazer o backup do arquivo de dados novamente, e isso não fará diferença para o tamanho do arquivo de log, ou a porcentagem de espaço usado no arquivo. Agora, porém, vamos fazer um backup do arquivo de log de transação e verificar novamente os valores, como mostrado pela Listagem 4.
Listagem 4. Backup dos arquivos.
BACKUP Log TesteDB
TO DISK ='C:\Backups\TesteDB_log.bak'
WITH INIT;
GO
DBCC SQLPERF(LOGSPACE);O arquivo de log ainda é o mesmo tamanho físico, mas por fazer o backup do arquivo, o SQL Server é capaz de truncar o log, tornando o espaço nas VLF "inativos" no arquivo de log disponível para reutilização; mais registros de log podem ser adicionados sem a necessidade de crescer fisicamente o arquivo. Além disso, é claro, nós capturamos os registros de log em um arquivo de backup e assim seriamos capazes de usá-los como parte do processo de recuperação de banco de dados, devemos então precisar restaurar o banco de dados TesteDB para um estado anterior.
Com isso finalizamos o nosso artigo, onde explanamos um pouco com relação ao gerenciamento de logs de transação, apresentando além de uma parte teórica, um exemplo prático para um melhor conhecimento com relação a como o SQL Server faz para manter a consistência e a integridade dos dados, através de um mecanismo de log write-ahead. Nós também apresentamos como um DBA pode capturar o conteúdo do arquivo de log de transação em um arquivo de backup, que pode ser reutilizado para restaurar o banco de dados como parte de um processo de recuperação. Por fim, destacou-se a importância de backups para controlar o tamanho do log de transação.
