Início / dba / Perguntas / 78625
Accepted
Gabe
Asked: 2014-10-08 11:55:04 +0800 CST

Visualizando quanto log de transações é usado no Modelo de Recuperação Simples

  • 772

Temos um banco de dados DW no modelo de recuperação simples. Todas as noites, carregamos dados de diferentes fontes e executamos alguns procedimentos. Eu gostaria de ver quanto o log de transações é realmente usado durante esse carregamento noturno que leva horas para ser executado.

Atualmente, o log aumentou para mais de 50 GB devido a uma consulta de limpeza única que executamos recentemente. O espaço usado no log está próximo de 0% devido ao Modelo de Recuperação Simples. Gostaríamos de reduzir o arquivo de log para um tamanho mais razoável. Suspeito que o carregamento noturno use apenas 5 GB, mas existe uma maneira de descobrir isso? Pensei em pesquisar o banco de dados a cada minuto ou cinco e capturar o espaço usado e não utilizado em uma tabela de registro por algumas noites. Existe uma maneira melhor?

sql-server-2012 transaction-log

1 respostas

  1. Best Answer

    Parece uma maneira apropriada de fazer isso.

    Crie uma tabela de registro:

    CREATE TABLE dbo.LogSpace
(
  dt DATETIME NOT NULL DEFAULT SYSDATETIME(),
  dbname SYSNAME, 
  log_size_mb DECIMAL(22,7),
  space_used_percent DECIMAL(8,5),
  [status] BIT
);

    Faça isso antes e depois da carga:

    INSERT dbo.LogSpace(dbname, log_size_mb, space_used_percent, [status])
EXEC sp_executesql N'DBCC SQLPERF(LogSpace) WITH NO_INFOMSGS;';

    Opcionalmente, remova todas as linhas não relacionadas a este banco de dados específico:

    DELETE dbo.LogSpace WHERE dbname <> N'yourdb';

    Em seguida, você pode comparar o tamanho/espaço antes e depois usado para qualquer data ou para todas as datas que você coletou.

    ;WITH x AS
(
  SELECT dbname, dt,
  duration = DATEDIFF(SECOND, LAG(dt) OVER 
    (PARTITION BY dbname ORDER BY dt), dt),
  [current] = space_used_percent, 
  previous = LAG(space_used_percent) OVER
    (PARTITION BY dbname ORDER BY dt),
  rn = ROW_NUMBER() OVER 
    (PARTITION BY dbname ORDER BY dt),
  log_size_mb
 FROM dbo.LogSpace
)
SELECT * FROM x WHERE rn % 2 = 0;

    Lembre-se de que os pontos de verificação que ocorrem durante o processo podem, na verdade, fazer com que o espaço do log seja reutilizado; Lembro-me de fazer alguns testes de desempenho recentemente e, depois de certas operações, o space_used_percentproblema realmente caiu. Portanto, você pode querer obter o máximo observado em alguns dias (e talvez executá-lo com mais frequência - nesse caso, você deseja uma consulta ligeiramente diferente, que não suponha que pares de linhas consecutivas estejam relacionados a qualquer atividade específica), em vez de apenas confiando em como acabou após a carga.

    Certifique-se também de que as configurações de crescimento automático para o arquivo de log sejam razoáveis ​​- você não deseja 1 MB ou 10%, mas também não deseja 10 GB. Como um evento de crescimento automático para um arquivo de log (a) fará com que todas as transações esperem e (b) não se beneficie da inicialização instantânea do arquivo, você deseja um bom equilíbrio entre quantas vezes o arquivo de log deve crescer durante uma operação anormal como seus dados limpeza e quanto tempo leva para qualquer evento de crescimento individual acontecer. Se esse evento foi recente o suficiente, você pode revisar esses eventos no rastreamento padrão para ver quanto tempo demorou.

    • 3

relate perguntas