Início / dba / Perguntas / 285415
Accepted
Vladimir Baranov
Asked: 2021-02-16 02:50:46 +0800 CST

Qual poderia ser o motivo para reduzir o arquivo de log de transações antes e depois do backup do banco de dados?

  • 772

Eu sou um DBA acidental. Sou a única pessoa em uma empresa que tem alguma experiência com SQL Server e tenho que lidar com um sistema herdado de outra empresa depois que ele foi adquirido. As pessoas que originalmente configuraram as coisas não estão mais disponíveis.

Versão do servidor: Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64) 15 de junho de 2019 07:56:34 Copyright (c) Microsoft Corporation Enterprise Edition: licenciamento baseado em núcleo (64 bits) no Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Uma das primeiras coisas que me chamaram a atenção quando olhei para o servidor foi um trabalho do SQL Server Agent que faz um backup do banco de dados principal. O banco de dados está no modo de recuperação Simples. O trabalho tem três etapas:

-- step 1
DBCC SHRINKFILE (N'DWH_Main_log' , 0)

-- step 2
declare @disk varchar(100) = replace(N'G:\DB_Backup\DWH_Main_&d&.bak','&d&', convert(varchar, getdate(), 112))
--print @disk
;
BACKUP DATABASE [DWH_Main] 
    TO  DISK = @disk
    WITH  RETAINDAYS = 2
        , NOFORMAT
        , NOINIT
        ,  NAME = N'DWH_Main-Full Database Backup'
        , SKIP
        , NOREWIND
        , NOUNLOAD
        , COMPRESSION
        ,  STATS = 10
GO

-- step 3
WAITFOR DELAY '00:05'
;
DBCC SHRINKFILE (N'DWH_Main_log' , 0)

O tamanho do arquivo de dados é de cerca de 400 GB, o tamanho do arquivo de log agora é de cerca de 50 GB.

CREATE DATABASE [DWH_Main]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DWH_Main', FILENAME = N'E:\Data\DWH_Main.mdf' , SIZE = 411114496KB , 
  MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'DWH_Main_log', FILENAME = N'F:\Log\DWH_Main_log.ldf' , SIZE = 53941184KB , 
  MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

Eu posso ver MUITOS eventos de crescimento automático do arquivo de log, o que não é surpresa. DBCC LOGINFOretorna 858 linhas, ou seja, 858 fragmentos, quase cada um com 64 MB.

O que devo dizer ao meu chefe sobre esta situação? Não entendo por que alguém gostaria de reduzir o arquivo de log de transações todos os dias após um backup completo, especialmente para um banco de dados em um modo de recuperação simples.

Minha primeira reação foi simplesmente remover os DBCC SHRINKFILEcomandos do script e reconfigurar o tamanho inicial do arquivo de log de transações. Aumente manualmente em pedaços de 8 GB para ~ 64 GB para reduzir a fragmentação, mas talvez esteja faltando alguma coisa?

sql-server sql-server-2016

2 respostas

  1. Best Answer

    Provavelmente quem configurou essa configuração não era muito experiente com o SQL Server.

    Ou, o espaço em disco era extremamente valioso e, mesmo que você apenas recupere algum espaço em disco antes que ele (logo) cresça novamente, os custos e as desvantagens de fazer a redução foram considerados de menor prioridade do que o custo do espaço em disco. Não é provável, mas uma possibilidade teórica.

    Por que um psiquiatra antes e depois do backup completo? Me bate. Um backup completo faz um ponto de verificação, que esvaziará o log (estar em recuperação simples), portanto, fazer a redução após o backup completo seria a coisa razoável a fazer. Se alguém pudesse usar a palavra "razoável" quando estamos falando de redução regular de log.

    Eu removeria o psiquiatra e adicionaria algum monitoramento. Ou seja, pesquise o tamanho do log a cada 5 minutos e veja se/quando ele cresce.

    E, claro, como você afirma, aumente-o para um tamanho razoável. Esse provavelmente deve ser o tamanho alto do arquivo de marca d'água, se você tiver essas informações disponíveis. Possivelmente o tamanho antes do primeiro encolhimento - acontecimentos excepcionais em seu banco de dados não contados ...

    • 3

  2. O que devo dizer ao meu chefe sobre esta situação?

    Diga ao seu chefe que isso está errado e que o encolhimento é uma operação ruim tanto para o arquivo de dados quanto para o arquivo de log.

    Não entendo por que alguém gostaria de reduzir o arquivo de log de transações todos os dias após um backup completo, especialmente para um banco de dados em um modo de recuperação simples.

    Falta de conhecimento sobre como o SQL Server funciona. Não há absolutamente nenhuma necessidade de executar o shrinkfile, deixando todo o ponto de executá-lo antes e depois do backup. A única vez que um arquivo de log pode ser reduzido é quando ele cresce enormemente devido a alguma transação indesejada.

    Minha primeira reação foi simplesmente remover os comandos DBCC SHRINKFILE do script e então reconfigurar o tamanho inicial do arquivo de log de transações

    Você está pensando absolutamente correto. Vá em frente removê-lo

    • 3

relate perguntas