Início / dba / Perguntas / 25656
Accepted
Petter Brodin
Asked: 2012-10-10 00:51:57 +0800 CST

Tamanho do log virtual do SQL Server

  • 772

Sou um DBA acidental, sendo um desenvolvedor que herdou alguns servidores de banco de dados (2005 e 2008) de alguém que sabia pouco sobre administração de banco de dados e aparentemente tinha ainda menos interesse em aprender mais sobre o assunto.

Estou aprendendo à medida que trabalho e, no momento, estou tentando descobrir os arquivos de log de transações.

Todos os nossos bancos de dados foram configurados com o modelo de recuperação simples e autoshrink. Entendo que usar o autoshrink geralmente é uma ideia horrível, mas entendo que isso foi feito para impedir que os logs de transações fiquem fora de controle. (O autoshrink realmente encolhe o(s) arquivo(s) de log ou apenas o banco de dados?)

Eu encontrei isso sobre o SQL Server 2012 e queria saber se é verdade sobre 2005 e/ou 2008 e exatamente o que significa: "Quando um banco de dados usa o modelo de recuperação simples, o Mecanismo de Banco de Dados trunca o log de transações após um ponto de verificação. [. ..] O Mecanismo de Banco de Dados aciona um ponto de verificação automático no modelo de recuperação simples quando o log virtual fica 70% cheio." Onde o tamanho do log virtual é especificado?

Quero desabilitar a redução automática em todos os bancos de dados, mas antes de fazer isso, preciso saber que os arquivos de log não ficarão fora de controle rapidamente.

Qualquer ajuda seria muito apreciada.

sql-server transaction-log

3 respostas

  1. Best Answer

    Um único arquivo de log de transação tem um tamanho físico (que você vê no disco) e também é dividido dentro do arquivo físico em seções lógicas chamadas arquivos de log virtual (VLFs).

    O crescimento automático e a redução automática operam no arquivo de log de transações físicas .

    O truncamento do log de transações (também chamado de "limpeza de log") opera nas seções lógicas do log de transações (VLFs) e não afeta o tamanho do arquivo físico. Esta parte é freqüentemente objeto de confusão.

    Um arquivo de log deve sempre crescer para acomodar uma grande transação; desativar a redução automática deixará o arquivo de log com o tamanho máximo necessário, em vez de diminuir fisicamente seu tamanho.

    Se você não tiver grandes transações, será seguro desativar a redução automática; os arquivos de log não crescerão sem limite, como aconteceria se o banco de dados estivesse em FULLou BULK_LOGGEDe você não estivesse fazendo backups de log de transações.

    Esse comportamento é o mesmo para SQL Server 2005+.

    • 6

  2. Como você referiu em sua pergunta, no SQL 2005 e 2008 após o ponto de verificação, o arquivo de log de transações também será truncado.

    Minha sugestão seria definir o modelo de recuperação como completo e criar um trabalho para fazer um backup do arquivo de log de transações. Este trabalho pode ser agendado em seu banco de dados e irá truncar o log de transações após fazer o backup. Ele automaticamente truncará o arquivo de log para você. Por favor, dê uma olhada nos links abaixo:

    SQL Server 2005: http://technet.microsoft.com/en-us/library/ms189085(v=sql.90).aspx

    SQL Server 2008: http://technet.microsoft.com/en-us/library/ms189085(v=sql.100).aspx

    • 2

  3. Então, aqui está o que descobri depois de ler as outras respostas aqui e fazer algumas pesquisas por conta própria:

    P: "O autoshrink realmente reduz o(s) arquivo(s) de log ou apenas o banco de dados?" R: Pelo que entendi: sim, tem. O encolhimento automático é definido no nível do banco de dados e afeta todos os arquivos (visto se você clicar com o botão direito do mouse no banco de dados -> propriedades -> arquivos ou se executar a consulta 1). O crescimento automático, no entanto, funciona por nível de arquivo.

    P: "Onde está especificado o tamanho do log virtual?" R: Veja a resposta de Jon Seigel e o link que Remus postou. Para ver o tamanho do log físico e lógico, use a consulta 2

    Um problema é que, se o banco de dados tiver o modo de recuperação completo ativado, crescido para um tamanho grande e, em seguida, tiver o modo de recuperação alterado para simples, um ponto de verificação não será acionado porque o VLF cresceu automaticamente. É possível tentar resolver isso (consulte a resposta de Remus para possíveis problemas com cabeçalho/final dos arquivos de log) executando a consulta 3, que reduzirá o arquivo de log ao tamanho que tinha quando foi originalmente criado.

    Consultas:

    1)

    SELECT name, physical_name AS current_file_location, DB_NAME(database_id) AS dbname
FROM sys.master_files
WHERE DB_NAME(database_id) = 'mydb'

    2)

    DECLARE @tmpt TABLE(
    dbname VARCHAR(255),
    logsize DECIMAL,
    logspaceused DECIMAL,
    stat INT
)

INSERT INTO @tmpt
    EXEC ('DBCC SQLPERF(LOGSPACE)')

SELECT * FROM @tmpt WHERE dbname LIKE 'mydb' ORDER BY logspaceused DESC

    3)

    checkpoint
DBCC SHRINKFILE('logfile_name')
    • 2

relate perguntas