Início / dba / Perguntas / 255969
Accepted
James Jenkins
Asked: 2019-12-20 07:31:47 +0800 CST

A recuperação pseudo-simples do SQL Server é uma coisa real?

  • 772

"Recuperação pseudo-simples do SQL Server" é termo e cenário, acabei de tomar conhecimento em um comentário (agora excluído) para a nova pergunta SQL Server trunca logs de transações com backups somente de cópia

Eu fui para o post Pseudo-Simple SQL Server Recovery Model 7 de outubro de 2019 por Rajendra Gupta e usando parte do código lá e alguns dos meus próprios fiz alguns testes.

Crie o banco de dados (código de Rajendra)

CREATE DATABASE RecoveryModel;

e Valide se está na íntegra (código de Rajendra)

SELECT name, 
    recovery_model_desc
FROM sys.databases
WHERE name = 'RecoveryModel';

Faça algum trabalho (código de Rajendra, ligeiramente modificado)

Use RecoveryModel
CREATE TABLE test(id INT);
GO 
INSERT INTO test
VALUES(1);
GO 5000

Veja quanto espaço de log é usado (meu código)

select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name

Descobrimos que o log está sendo preenchido. Execute o trabalho novamente e verifique o tamanho, o log cresce, sem surpresa.

Tente executar o t-log (meu código)

BACKUP LOG [RecoveryModel] TO  
DISK = N'E:\SQLBackups\RecoveryModel.trn' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD 
GO

Ele falha com a mensagem:

Msg 4214, Nível 16, Estado 1, Linha 8

BACKUP LOG não pode ser executado porque não há backup de banco de dados atual.

Msg 3013, Nível 16, Estado 1, Linha 8

BACKUP LOG está terminando de forma anormal.

Nada remotamente simples sobre isso, se você tentar fazer backup de um banco de dados em recuperação simples. você recebe a mensagem

Msg 4208, Nível 16, Estado 1, Linha 19

A instrução BACKUP LOG não é permitida enquanto o modelo de recuperação for SIMPLE. Use BACKUP DATABASE ou altere o modelo de recuperação usando ALTER DATABASE.

Execute um backup somente de cópia (meu código)

BACKUP DATABASE [RecoveryModel] TO  
DISK = N'E:\SQLBackups\RecoveryModel.bak' WITH NOFORMAT, INIT, COPY_ONLY,  
NAME = N'RecoveryModel-Full Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

Funciona bem, verifique o espaço de log e não encolheu. Execute a carga de trabalho por mais algum tempo e o espaço de log continua a crescer. Execute um backup t-log e ele continua a falhar.

Execute um backup diferencial (Meu código)

BACKUP DATABASE [RecoveryModel] TO  
DISK = N'E:\SQLBackups\RecoveryModel.dif' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  
NAME = N'RecoveryModel-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

Ele falha assim como o t_log

Msg 3035, Level 16, State 1, Line 13 Não é possível realizar um backup diferencial para o banco de dados "RecoveryModel", porque não existe um backup de banco de dados atual. Execute um backup completo do banco de dados emitindo novamente BACKUP DATABASE, omitindo a opção WITH DIFFERENTIAL.

Então, o que é "Pseudo-Simples" sobre isso? Os logs crescem, os backups t-log e diferentes falham. Você tem um banco de dados em recuperação completa, sem backup completo.

Edit , parece que há algo específico para servidores na minha compilação que está causando resultados diferentes do que todos os outros estão vendo. Eu aceitei a resposta de Josh.

sql-server backup

3 respostas

  1. Best Answer

    Veja este artigo de Paul Randal, especialista em modelos de recuperação de banco de dados: Novo script: esse banco de dados está REALMENTE no modo de recuperação FULL?

    Em particular, esta citação confirma o comportamento de um modelo de recuperação "pseudo simples":

    ...quando você alterna um banco de dados para o modo de recuperação completa, ele realmente se comporta como se estivesse no modo de recuperação simples até que a cadeia de backup de log seja estabelecida (isso é comumente chamado de 'pseudo-simples').

    Kimberly Tripp elabora o tópico aqui: Melhores práticas de manutenção de banco de dados Parte III – Manutenção de log de transações

    O que isso significa é que o SQL Server limpa automaticamente os registros inativos do log de transações quando sabe que não precisa mais deles. Não precisa mais que eles sejam armazenados no log porque ninguém está usando o log

    Não concordo totalmente com sua afirmação " Nada remotamente simples sobre isso " em relação ao backup do log de transações falhando no modelo de recuperação FULL anterior a um backup FULL. Isso é como o modelo SIMPLE (backups de log não são permitidos). A mensagem de erro é diferente porque está tentando informar ao usuário final como resolver o problema.

    Tentei seu teste no SQL Server 2017 e a execução do backup somente cópia aciona consistentemente um interno CHECKPOINTe limpa o log ( PercentFreeaumenta para o arquivo de log).

    Na execução inicial, PercentFreedo arquivo _log é 65.04.
    Executei o backup somente de cópia e PercentFreepulei para 88.68.

    Confirmei com a seguinte sessão de eventos estendidos que um CHECKPOINTfoi executado neste momento também.

    CREATE EVENT SESSION [checkpointage] ON SERVER 
ADD EVENT sqlserver.checkpoint_begin(
    ACTION(sqlserver.database_id)),
ADD EVENT sqlserver.checkpoint_end(
    ACTION(sqlserver.database_id))
ADD TARGET package0.event_file(SET filename=N'checkpointage')
WITH (STARTUP_STATE=OFF)
GO
    • 5

  2. Pseudo-simples significa que um banco de dados no modelo de recuperação completa se comportará como se estivesse no modelo de recuperação simples até que o primeiro backup completo seja feito (o último número de sequência de log do qual foi feito backup é registrado).

    Em outras palavras, um banco de dados que está no modelo de recuperação completa requer uma cadeia de backup válida, e uma cadeia de backup válida requer um backup completo para dar o pontapé inicial. Em seguida, você pode realizar backups de log e diferenciais em paralelo ao conteúdo do seu coração, cada um deles baseado nesse backup completo inicial.

    Como você viu nos scripts executados, não havia nenhuma cadeia de backup estabelecida até que você executasse seu backup completo (somente cópia). Depois que isso foi executado, você iniciou uma cadeia de backup, mas, como fez um backup somente de cópia, confundiu as coisas.

    Os backups diferenciais são um atalho para reduzir o número de backups de log que você precisa restaurar para um momento específico. Eles dependem de um backup completo que não COPY ONLY seja , porque o backup somente cópia não redefine o bitmap diferencial usado para rastrear quais extensões foram modificadas desde o último backup completo.

    Se você tivesse feito um backup completo padrão (sem COPY ONLY), seu diferencial teria sido bem-sucedido e o banco de dados não estaria mais no estado pseudo-simples. Ficou ainda mais confuso pelo fato de que um backup diferencial também teria falhado em um banco de dados no modelo de recuperação simples sem um backup completo (sem cópia) para iniciar a cadeia.

    • 2

  3. Eu ( OP na pergunta ) não sabia por que "Pseudo-Simple SQL Server Recover" estava funcionando conforme definido em várias postagens, mas não estava vendo no meu sistema com esses testes.

    Na fase de pesquisa, coloquei o banco de dados SIMPLEe recriei o teste, o tamanho do arquivo de log usado cresceu da mesma forma que em FULL, isso sugeriu algo com Durabilidade de Transação de Controle , mas testes posteriores descartaram isso.

    Depois de mais pesquisas e testes, descobri! ( Obrigado a um colega de trabalho que me ajudou a resolver o problema. )

    • Estamos usando 50 MB como tamanho inicial para 'model', o que significa que o RecoveryModel inicia o mesmo. ( O padrão SQL 2017 é 8 MB )
    • Isso estava criando 4 VLFs com cerca de 12,5 MB cada
    • A carga de trabalho que estou usando no teste acima cria cerca de 2 MB de dados nos logs a cada execução.
    • Eu estava executando a carga de trabalho de teste de 3 a 5 vezes (6 a 10 MB de logs), antes de fazer o COPY ONLYbackup.
    • O espaço utilizado pelo VLF foi crescendo, mas como nunca encheu o primeiro VLF, ficou no status 2, e nada foi lançado.
    • Quando o teste parecia dar resultados inesperados, larguei o banco de dados e iniciei um novo teste.
    • Em um teste de acompanhamento, executei a carga de trabalho para criar ~ 15 MB de logs, depois executei COPY ONLYe o espaço usado caiu para 2 MB, com o primeiro VLF retornando ao status 0 e o segundo VLF mantendo os últimos 2 MB
    • Isso pode ser validado usando o código abaixo, além do acima, para modificar e testar você mesmo.

    .

    USE [RecoveryModel]
GO
DBCC LOGINFO

    Informações relacionadas falando sobre VLFs: Muitos VLFs - Como faço para truncá-los?

    • 2

relate perguntas