Início / dba / Perguntas / 44889
Accepted
MicBehrens
Asked: 2013-06-20 12:08:59 +0800 CST

Modelo de recuperação SIMPLES ou COMPLETO para bancos de dados?

  • 772

Quando devo usar o modelo de recuperação completa e quando devo usar o modelo de recuperação simples para bancos de dados?

Eu sempre usei o modelo de recuperação completa porque é o padrão, mas hoje encontrei este erro:

Provedor Microsoft OLE DB para SQL Server (0x80040E14) O log de transações do banco de dados 'DATABASE NAME' está cheio. Para descobrir por que o espaço no log não pode ser reutilizado, consulte a coluna log_reuse_wait_desc em sys.databases

O banco de dados específico é, na verdade, um dos menores e mais inativos bancos de dados do meu servidor, então não tenho ideia de como o log pode estar cheio nesse banco de dados e não nos outros.

Para reduzir o log e tornar o banco de dados acessível novamente, alterei o modelo de recuperação de FULL para SIMPLE e reduzi o log do arquivo lógico, com o seguinte comando

alter database myDbName SET recovery simple
go
dbcc shrinkfile('LOG FILE LOGICAL NAME', 100)
go

Ajudou, mas agora preciso entender POR QUE ajudou, COMO essa situação começou e COMO evitar isso no futuro?

EDITAR:

Todas as noites, à 1h, estamos fazendo um backup com script de cada banco de dados no servidor. Isso está sendo feito por um script de 31 linhas onde a parte mais importante é

set @Filename = 'D:\backup\' + convert(varchar, getDate(), 112) + ' - ' + @DBName + '.bak'
set @Description = 'Full backup of database ' + @Filename
BACKUP DATABASE @DBName TO DISK = @Filename WITH INIT , NOUNLOAD , NAME = @Description, NOSKIP , STATS = 10, NOFORMAT

O novo modelo de recuperação e a redução de banco de dados entrarão em conflito com esse script?

Não estamos fazendo nenhum outro tipo de backup dos bancos de dados e, portanto, não dos logs de transações, não é?

sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    Quando devo usar o modelo de recuperação completa e quando devo usar o modelo de recuperação simples para bancos de dados?

    Você deve usar o modelo de recuperação completo quando precisar de recuperação pontual do banco de dados. Você deve usar o modelo de recuperação simples quando não precisar de recuperação pontual do banco de dados e quando o último backup completo ou diferencial for suficiente como ponto de recuperação. (Observação: existe outro modelo de recuperação, bulk-logged. Para obter mais informações sobre o modelo de recuperação bulk-logged, consulte esta referência )

    Provedor Microsoft OLE DB para SQL Server (0x80040E14) O log de transações do banco de dados 'DATABASE NAME' está cheio. Para descobrir por que o espaço no log não pode ser reutilizado, consulte a coluna log_reuse_wait_desc em sys.databases

    O motivo pelo qual você recebeu esse erro (provavelmente) é porque você não está fazendo backup do seu log de transações. Quando não for feito backup, ele continuará a aumentar fisicamente o arquivo de log de transações (desde que o crescimento automático esteja habilitado e maxsize permita) porque não pode reutilizar nenhuma das "partes" do log de transações (arquivos de log virtuais). Ele só pode marcar esses VLFs para reutilização e permitir a natureza "wrap-around" do log de transações quando você faz um backup do log de transações (e alguns outros requisitos, como nenhuma transação ativa, alguns aspectos de replicação etc.).

    Para reduzir o log e tornar o banco de dados acessível novamente, alterei o modelo de recuperação de FULL para SIMPLE e reduzi o log do arquivo lógico, com o seguinte comando

    ......

    Ajudou, mas agora preciso entender POR QUE ajudou, COMO essa situação começou e COMO evitar isso no futuro?

    Isso o ajudou porque, ao configurar seu banco de dados para o modelo de recuperação simples, você disse ao SQL Server que não se importa mais com a recuperação pontual e com o requisito de garantir que os arquivos de log virtuais não precisem mais ser preservados e marcados como ativos, agora, um processo de ponto de verificação marca esses VLFs como inativos.

    Trecho/citação retirado desta referência do MSDN :

    No modelo de recuperação simples, a menos que algum fator esteja atrasando o truncamento do log, um ponto de verificação automático trunca a seção não utilizada do log de transações. Por outro lado, nos modelos de recuperação full e bulk-logged, uma vez estabelecida uma cadeia de backup de log, os pontos de verificação automáticos não causam truncamento de log.

    Em seguida, você reduziu o arquivo de banco de dados físico e, como havia espaço livre no log de transações, agora ele conseguiu reduzir fisicamente o arquivo NTFS.

    Leitura que vale a pena dedicar algum tempo:

    1. Modelos de recuperação
    2. Gerenciando logs de transações (Gail Shaw)
    3. Fatores que podem atrasar o truncamento de log

    EDITAR após sua edição :

    O novo modelo de recuperação e a redução de banco de dados entrarão em conflito com esse script?

    Esse BACKUP DATABASEcomando funcionará com os modelos de recuperação. Quanto ao encolhimento de banco de dados de rotina... NÃO FAÇA ISSO!!!! Sério, dimensione seu banco de dados de acordo e, se você utilizar o modelo de recuperação completo, verifique se está fazendo arquivos de log de transações de rotina e frequentes, não apenas para manter o tamanho do log de transações sob controle, mas também para atender aos objetos de ponto de recuperação.

    Não estamos fazendo nenhum outro tipo de backup dos bancos de dados e, portanto, não dos logs de transações, não é?

    Se o seu banco de dados estiver utilizando o modelo de recuperação completo, sim, você deve fazer backups do log de transações. Se seu banco de dados estiver em recuperação simples, você não poderá fisicamente fazer um backup do log de transações.

    Quanto ao modelo de recuperação a ser usado (simples versus completo), não podemos tomar essa decisão por você. Somente você, sua equipe de negócios e seus SLAs podem.

    • 62

relate perguntas