Log Sequence Number (LSN) e a tabela BackupSet (Backup/Restore – Parte 1 de ?)

Article
03/29/2007

Backups... Essa maravilha moderna onde todo mundo possui uma tarefa defina, que é executada religiosamente na freqüência definida pela necessidade do negócio. Aí chega aquele dia lindo, ensolarado, em que o DBA precisa recuperar um banco de dados até momentos antes daquele problema que aconteceu no dia anterior e acabou de ser descoberto.

O DBA segue seu processo e pega o guia de recuperação de desastres (se você não tem um, faça!) e começa a restaurar o banco de dados: backup completo, diferencial (se existir), log, log, e então o pior acontece. Você não consegue recuperar o próximo arquivo de log porque existe uma falha na seqüência dos arquivos.

Nesse momento você está sem saída, pois o SQL Server não irá permitir que os arquivos seguintes sejam recuperados, então esse é o tipo de problema em que a única solução é evitá-lo! Antes de detalharmos como evitar esse problema, vamos entender melhor a relação entre o backup de log e o Log Sequence Number (LSN).

Quando o banco de dados está com o recovery model full ou Bulk-logged, todas as transações processadas pelo SQL Server são registradas no log de transação (arquivos .ldf), e cada instrução executada recebe um número de seqüência (LSN). Por questões de simplicidade, as diferenças entre os modos de recuperação não serão discutidas nesse artigo.

Para que seja possível restaurar o banco de dados e todos os backups de log, a continuidade do LSN deve ser garantida entre cada arquivo, pois só assim o SQL Server sabe que está recuperando as transações na seqüência certa e garantindo assim sua integridade e consistência.

Como não existe nada melhor para entender os detalhes de uma ferramenta do que testá-la, abra o script anexado neste post e vá acompanhando os passos na medida em que são executados. Por questões de legibilidade, somente incluirei aqui os passos mais importantes.

Execute os passos 0, 1 e 2: neste momento temos dados inseridos na tabela e registrados no log de transação.

Para o SQL Server saber quais transações já foram escritas em suas respectivas páginas, utilizado no processo de recovery, ele marca no cabeçalho de cada página qual foi o último LSN que gerou uma alteração em seu conteúdo. Assim quando o SQL Server estiver no processo de recovey, ele saberá que não precisa atualizar a página se o LSN de seu cabeçalho foi maior do que o LSN que ele está lendo.

Vamos ver como está o LSN da nossa página de dados.

select db_id('InsideLSN01') -- DBID = 31

select id, first, root, rowcnt from sysindexes where [id] = object_id('SaldoConta')

id first root rowcnt

----------- -------------- -------------- --------------------

1977058079 0x1E0000000100 0x1C0000000100 9

0x1E = 30 (decimal)

DBCC TRACEON(3604)

DBCC PAGE(9,1,30,3)

PAGE: (1:30)

------------

BUFFER:

-------

BUF @0x02412B80

---------------

bpage = 0x444CC000 bhash = 0x00000000 bpageno = (1:30)

bdbid = 31 breferences = 1 bstat = 0xb

bspin = 0 bnext = 0x00000000

PAGE HEADER:

------------

Page @0x444CC000

----------------

m_pageId = (1:30) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000

m_objId = 1977058079 m_indexId = 0 m_prevPage = (0:0)

m_nextPage = (0:0) pminlen = 224 m_slotCnt = 9

m_freeCnt = 6035 m_freeData = 2139 m_reservedCnt = 0

m_lsn = (6:85:2) m_xactReserved = 0 m_xdesId = (0:0)

m_ghostRecCnt = 0 m_tornBits = 0

Allocation Status

-----------------

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Aqui vemos que o LSN da última alteração dessa página é o m_lsn = (6:85:2). Note que o LSN pode variar entre as diferentes execuções do script.

Todo e qualquer backup efetuado no SQL Server armazena um histórico na tabela msdb..BackupSet. Quando o passo 4 for executado (lembre-se de definir um diretório para seu backup), podemos ver as informações do backup completo analisando a tabela BackupSet.

select backup_set_id, database_name, first_lsn, last_lsn, checkpoint_lsn, database_backup_lsn,database_creation_date, type

from MSDB..backupset as BSW

where database_name = 'InsideLSN01'

order by backup_set_id asc

backup_set_id database_name first_lsn last_lsn checkpoint_lsn database_backup_lsn database_creation_date type

------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------------------- ----

157 InsideLSN01 6000000008800001 6000000009200001 6000000008800003 0 2007-03-29 14:22:46.000 D

Analisando a saída da consulta podemos notar as seguintes informações:

- Durante o backup, transações com os LSN de 6000000008800001 a 6000000009200001 foram registrados.

* 6000000008700001 é o exato momento onde o backup começou.

* 6000000009100001 é o exato momento onde o backup terminou.

- O backup é do tipo full (type = D)

- O LSN do último checkpoint registrado é o 6000000008700003 (esse é o exato momento em que o checkpoint foi iniciado).

- database_backup_lsn = 0 indica que não existe nenhum backup full anterior a este backup.

Todas as transações acontecidas antes de 6000000008700001 já estão registradas em disco, então o backup full não precisa registrar os LSN anteriores a esse valor. Ele somente registra as transações que aconteram durante o backup, pois essa é uma atividade online e ao fim do backup todas as transações que aconteceram devem estar registradas (Last LSN).

Após executarmos o passo 6, sabemos que a página 30 (que contém o primeiro registro) foi atualizada pelo update que foi executado. Então se analisarmos o cabeçalho da página, veremos um novo LSN: m_lsn = (6:190:2).

DBCC TRACEON(3604)

DBCC PAGE(31,1,30,3)

PAGE: (1:30)

------------

BUFFER:

-------

BUF @0x02412B80

---------------

bpage = 0x444CC000 bhash = 0x00000000 bpageno = (1:30)

bdbid = 31 breferences = 1 bstat = 0xb

bspin = 0 bnext = 0x00000000

PAGE HEADER:

------------

Page @0x444CC000

----------------

m_pageId = (1:30) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0

m_objId = 1977058079 m_indexId = 0 m_prevPage = (0:0)

m_nextPage = (1:31) pminlen = 224 m_slotCnt = 35

m_freeCnt = 81 m_freeData = 8041 m_reservedCnt = 0

m_lsn = (6:190:2) m_xactReserved = 0 m_xdesId = (0:0)

m_ghostRecCnt = 0 m_tornBits = 141

Allocation Status

-----------------

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Após o primerio backup de log (passo 8), a tabela BackupSet trás as seguintes informações sobre os backups (passo 9):

backup_set_id database_name first_lsn last_lsn checkpoint_lsn database_backup_lsn database_creation_date type

157 InsideLSN01 6000000008800001 6000000009200001 6000000008800003 0 2007-03-29 14:22:46.000 D

158 InsideLSN01 6000000008800001 6000000019100001 6000000008800003 6000000008800003 2007-03-29 14:22:46.000 L

Analisando o conteúdo do backup de log, temos:

- O first_lsn deste backup está igual ao first_lsn do backup full. Este comportamento está certo, já que backup full não marca nenhuma parte do log como inativa, então o backup de log regsitra tudo a partir daquele ponto.

* Vale notar que esse é um erro comum, onde o DBA faz regularmente backup full e fica espantado quando o log de transação fica cheio. Backup full não marca partes do log de transação como inativas!

- O last_lsn é 6000000019100001 e incluí o lsn da alteração da página 30 (191 > 190).

- É um backup de log (type = L)

- O database_backup_lsn referencia o LSN do checkpoint no último backup full. É dessa forma que o SQL Server sabe qual é o backup full inical para aquela sequência de log. Se você tentar restaurar o log do backup_set_id 158 depois de um backup full que não seja o 157, o SQL Server não vai permitir. Essa verificação é feita pelo LSN.

Continuando a execução do script, após inserirmos novos registros e um novo backup de log (passos 10 e 11), a tabela backup set irá apresetnar as seguintes informações (passo 12):

backup_set_id database_name first_lsn last_lsn checkpoint_lsn database_backup_lsn database_creation_date type

157 InsideLSN01 6000000008800001 6000000009200001 6000000008800003 0 2007-03-29 14:22:46.000 D

158 InsideLSN01 6000000008800001 6000000019100001 6000000008800003 6000000008800003 2007-03-29 14:22:46.000 L

159 InsideLSN01 6000000019100001 6000000039600001 6000000008800003 6000000008800003 2007-03-29 14:22:46.000 L

Aqui podemos ver o encadeamento dos backups de log. Veja que o first_lsn do backup 158 (6000000019100001) é igual ao last_lsn do backup 157 (6000000019100001). Durante o processo de restore, essa sequência deve ser mantida,senão o SQL server não permite que os backups sejam restaurados. Agora vamos quebrar essa sequência...

Após a inserção de novos registros (passo 13), onde o log de transação continuou registrando os inserts, executamos um truncate de log no passo 14. Neste momento nós acabamos de destruir a sequência do LSN, pois o truncate de log faz praticamente a mesma coisa que o backup log, com a ligeira diferença de jogar os dados no limbo ao invés de armazená-los em um arquivo.

Para demonstrar a falha na sequência, continuamos inserindo registros (passo 15) e fazemos um outro backup de log (passo 16). Vale notar a mensagem que o SQL Server mostra: “This log backup cannot be used to roll forward a preceding database backup”. Já dizia o ditado popular, quem avisa amigo é...

Analisando a saída da tabela BackupSet temos:

backup_set_id database_name first_lsn last_lsn checkpoint_lsn database_backup_lsn database_creation_date type

157 InsideLSN01 6000000008800001 6000000009200001 6000000008800003 0 2007-03-29 14:22:46.000 D

158 InsideLSN01 6000000008800001 6000000019100001 6000000008800003 6000000008800003 2007-03-29 14:22:46.000 L

159 InsideLSN01 6000000019100001 6000000039600001 6000000008800003 6000000008800003 2007-03-29 14:22:46.000 L

160 InsideLSN01 7000000009200001 7000000024700001 7000000009200001 6000000008800003 2007-03-29 14:22:46.000 L

Aqui podemos ver a quebra do encadeamento dos backups de log. Veja que o first_lsn do backup 160 (6000000039600001) é diferente ao last_lsn do backup 159 (7000000009200001). A diferença entre os números 6 e 7 que iniciam o LSN será explicada em um outro artigo, neste momento apenas considere que a sequência foi quebrada.

Agora vamos supor que devido a uma operação errada que foi executada, esse banco de dados tenha que ser restaurado.

Executamos os passos 18 e 19 para apagarmos o banco de dados corrente e recuperarmos: o backup full, o primeiro e segundo backups de log, ambos com NO RECOVERY porque ainda continuaremos a recuperar outros backups de log. Ao tentarmos recuperar o terceiro backup de log,

RESTORE LOG InsideLSN01

FROM DISK = 'C:\Temp\InsideLSN01Log03.bkp'

WITH RECOVERY

Msg 4305, Level 16, State 0, Line 1

The log in this backup set begins at LSN 7000000009200001, which is too late to apply to the database. An earlier log backup that includes LSN 6000000039600001 can be restored.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Este backup de log não pode ser restaurado porque quebra a sequência do LSN. Então não nos resta nada a fazer além de deixar o banco de dados como está. Executando os passos 21 e 22 podemos ver que a base está operacional e com exatos 199 registros na tabela SaldoConta.

Até o momento vimos como funciona o LSN e os backups de log, além de mostrarmos o problema que um truncate no log de transação pode trazer. Continuarei com mais artigos sobre backup e restore para entrarmos em algumas peculiaridades desse assunto. Mandem suas idéias e dúvidas.

Agora, para eu não me divertir sozinho, fica aqui um desafio para quem quiser se brincar com o SQL Server e T-SQL:

** Crie um script que mostra se existem falhas na sequência dos backups de log, a partir do último backup full, baseado nas informações da tabela backupset. Já sei que existem uma série de peculiaridades que talvez tornem essa verificação bem difícil ou até inviável, mas isso vamos descobrir ao longo dos artigos.

Importante: esses scripts foram testados no SQL Server 2000, pois são baseados em um caso que trabalhei. Depois abordaremos o assunto tratando sobre o SQL Server 2005.

[]s

Luciano Caixeta Moreira

Luciano.moreira@microsoft.com

=============================================================

This posting is provided "AS IS" with no warranties, and confers no rights

=============================================================

Log Sequence Number e a tabela BackupSet (Backup_Restore - Parte 1).zip

Log Sequence Number (LSN) e a tabela BackupSet (Backup/Restore – Parte 1 de ?)

Additional resources