Carlos@Geekbunker

Recovery Models

Uma vez eu fiz um breve resumo de como os recovery models do SQL Server funcionam e suas implicações no arquivo de transaction log. Este resumo foi utilizado como resposta numa dúvida na lista de discussão de SQL Server MSSQL-L.
De tempos em tempos a mesma dúvida surge e para economizar tempo e facilitar a leitura resolvei transformar a resposta num post do blog.

Você tem duas opções com relação ao Arquivo de Transação (Transaction
Log)

1) Utilizar o banco em Recovery Model Simple (truncate log on
checkpoint). Deste modo o arquivo de transação é "reciclado" de tempos
em tempos e você não precisa limpa-lo.
Utilize este modo quando você pode conviver com o último full backup.

2) Utilize o banco em recovery model full ou bulk/insert. Nestes modos
você precisa executar o comando BACKUP LOG a cada X tempos para que o
arquivo de log seja limpo. O SQL faz duas operações importantes no
BACKUP LOG: ele copia os dados do arquivo de transação para um arquivo
de backup e "limpa" o arquivo de backup, liberando espaço para novas
transações. Utilize o modo full quando você precisa utilizar point in
time recovery. Utilize o Bulk/insert quando você pode conviver como
backup do último transaction log.

O comando BACKUP LOG somente pode ser executado em bancos de dados que
estejam com o recovery model em full ou bulk/insert.

O comando BACKUP DATABASE NÃO limpa o arquivo de transaction LOG. Você
deve utilizar o comando BACKUP LOG para limpar o arquivo de transaction
log.

Mais um ponto:
Muitos usam o termo LIMPAR o Transaction log. Este não é um termo
correto e não representa corretamente o procedimento do transaction log.
Explico melhor: o arquivo de LOG (transaction log) é dividido internamente em Virtual Log Files. Imagine então que o transaction log possui 100 VLFs. Uma transação típica com o banco em modo full ocorreria da seguinte forma:

1) a transação ocupa os VLF 1 até 10 durante a sua execução.
2) O SQL executa um processo chamado checkpoint e verefica se as
transações que estão no arquivo de log ja estão também escritas no
arquivo de dados. Se as transações estiverem em ambos os arquivos o SQL
marca os VLFs da transação como candidados a serem liberados. Neste caso
os VLFs 1 a 10 são marcados como candidatos a serem liberados.
3) uma nova transação ocorre e ocupa os VLF de 11 a 90. Ela ocupa estes
VLFs porque os VLFs 1 a 10 não estão liberados, somente marcados como
candidatos a serem liberados.
4) um backup log ocorre e SQL Server copia as VLF 1 a 10 (transações que estão no log que ja sofreram um checkpoint) para um arquivo
de backup. Os VLFs que pertencerem as transações que estiveram marcadas
como candidatas a liberadas (ja estão "comitadas") são marcados como
livres para uso. No nosso caso as transações 1 a 10. As transações 11 a 90 ainda não sofreram o processo de checkpoint por isso ficam no arquivo de transaction log.
5) Uma nova transação ocorre e precisa de 20 VLF, esta transação então
ocupa os VLFs 90 a 100 e os VLFs 1 a 10. Ela ocupou 10 VLFs que estavam
livre e mais 10 VLFs que estam marcados como livres.

O que aconteceria se o backup log não tivesse ocorrido?
a) Se o arquivo de log pudesse crescer fisicamente ele iria
crescer fisicamente
b) Se o arquivo de log não pudesse crescer ele daria um erro de
LOG FULL

No caso de um banco em modo simple ocorre o seguinte:

1) a transação ocupa os VLF 1 até 10 durante a sua execução.
2) O SQL executa um processo chamado checkpoint e verefica se as
transações que estão no arquivo de log ja estão também escritas no
arquivo de dados. Se as transações estiverem em ambos os arquivos o SQL
marca os VLFs da transação como livres. Neste caso os VLFs 1 a 10 são
marcados como livres.
3) uma nova transação ocorre e ocupa os VLFs 11 a 90.
4) Uma nova transação ocorre e precisa de 20 VLFs. O transaction possui
disponível 10 VLFs (91 a 100) e mais 10 VLFs (1 a 10 marcados como
livres).
5) O SQL então utiliza os 10Vls (91 a 100) e "recicla" os VLs 1 a 10 para serem utilizados pela transação.

A diferença entre os recovery models está no último passo, pois no banco full o backup log "guardou" a transação que ocupou os 10Vls no arquivo de backup. No modo simple o SQL "perdeu" estas transações pois o espaço foi reciclado.

É por isso que recomenda-se que bancos de produção utilizem modo full que permite voltar o banco de dados a qualquer momento.
Para ambientes de desenvolvimento ou que podem conviver com backup full recomenda-se a utilização do modo simple.

Algumas referências:

INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE

Truncating the Transaction Log no Books On Line

Posted by Carlos at janeiro 23, 2004 07:06 PM