INF: Como diminuir o registo de transacções do SQL Server

Traduções de Artigos Traduções de Artigos
Artigo: 256650 - Ver produtos para os quais este artigo se aplica.
Este artigo foi publicado anteriormente em PT256650
Expandir tudo | Reduzir tudo

Nesta página

Sumário

Existem algumas razões comuns pelas quais um registo de transacções poderá não diminuir quando utiliza o comando DBCC SHRINKFILE ou DBCC SHRINKDATABASE. Os tópicos em SQL Server Books Online "DBCC SHRINKFILE" e "DBCC SHRINKDATABASE" fornecem informações detalhadas, das quais se apresenta a seguir um breve resumo.

Mais Informação

  • No Microsoft SQL Server 7.0, os comandos SHRINKFILE e SHRINKDATABASE definem um tamanho de destino para a diminuição. Cada ficheiro de registo é marcado por estes comandos, mas trata-se, na realidade, de uma cópia de segurança do registo ou de uma truncagem do registo que tenta diminuir os ficheiros. Assim sendo, depois de utilizar o comando SHRINKFILE ou SHRINKDATABASE deverá também emitir um comando que trunque o registo antes que este seja diminuído.
  • Não é possível diminuir um registo para um tamanho mais pequeno do que o permitido por estes critérios:

    • Para diminuir um registo para um tamanho mais pequeno do que o original, terá de diminuir ficheiros individuais com o comando DBCC SHRINKFILE. Não é possível utilizar o comando DBCC SHRINKDATABASE para diminuir um registo para um tamanho mais pequeno do que o tamanho original ou explicitamente definido. O tamanho original é definido como o tamanho do registo devido ao comando CREATE DATABASE e a quaisquer comandos ALTER DATABASE explícitos. O tamanho original não inclui o aumento automático do registo.

    • O ficheiro de registo físico nunca poderá ser mais pequeno do que a quantidade de espaço actualmente utilizado no ficheiro de registos. É possível utilizar o comando DBCC SQLPERF (LOGSPACE) para monitorizar a quantidade de espaço utilizada.

    • O tamanho actual do registo da base de dados modelo é o tamanho mínimo para qualquer registo de base de dados nesse servidor. Por predefinição, o registo da base de dados modelo tem menos de 1 MB.

    • Uma vez que um registo pode ser diminuído apenas até um limite VLF (virtual log file - ficheiro de registo virtual), não é possível diminuir um ficheiro de registo para um tamanho mais pequeno do que o de um VLF, mesmo que o espaço não esteja a ser utilizado. Do mesmo modo, se parte de um VLF estiver a ser utilizada, não será possível diminuir o espaço desse VLF. Para mais informações, consulte os tópicos "Virtual Log Files" e "Transaction Log Physical Architecture" em SQL Server Books Online.


  • O registo de transacções é um registo de reinício cíclico (wrap-around). Isto significa que em qualquer altura poderão existir VLF com espaço "livre" ou "reutilizável" no princípio, no meio e/ou no fim do registo. Para diminuir o registo, deverá existir espaço "livre" no fim do registo e não apenas espaço livre em qualquer ponto do registo. Também só é possível diminuir VLF completos. Para diminuir o registo de transacções, os VLF existentes no fim do ficheiro de registo devem estar inactivos e truncados. Para obter informações mais detalhadas, consulte o tópico "Truncating the Transaction Log" em SQL Server Books Online.
Seguem-se alguns aspectos que não devem ser esquecidos:
  • Crie sempre cópias de segurança da base de dados do sistema e da base de dados do utilizador antes e depois de implementar alterações que afectem o sistema. DBCC SHRINKFILE e DBCC SHRINKDATABASE não são operações registadas e, ao executá-las, estará a invalidar outras cópias de segurança do registo de transacções. É necessário criar uma cópia de segurança completa da base de dados após a execução dos comandos DBCC SHRINKFILE ou DBCC SHRINKDATABASE.

  • Certifique-se de que não estão programadas cópias de segurança para a hora a que deverá ocorrer a diminuição.

  • Certifique-se de que não existem transacções antigas, de execução demorada ou não replicadas. Para o fazer, utilize um código semelhante a:
    DBCC OPENTRAN (nome_da_base_de_dados)
  • Execute o comando DBCC SHRINKFILE ou DBCC SHRINKDATABASE para assinalar um ponto de diminuição. As permissões de DBCC SHRINKFILE e DBCC SHRINKDATABASE são predefinidas para membros da função de servidor fixa sysadmin ou da função de base de dados fixa db_owner e não são transferíveis. Para obter informações sobre as diferenças entre estes dois comandos, consulte os seguintes tópicos em SQL Books Online (tome nota dos diferentes parâmetros):

    DBCC SHRINKFILE     (nome_do_ficheiro, tamanho_de_destino)
    DBCC SHRINKDATABASE (nome_da_base_de_dados, percentagem_de_destino)
  • Crie algumas transacções fictícias para efectuar o reinício cíclico do registo e, em seguida, emita o comando BACKUP para truncar o registo. A instrução BACKUP é o processo que tenta realmente diminuir o registo para o tamanho de destino assinalado.

    Segue-se um exemplo de como criar uma transacção fictícia que reinicia o registo de um único ficheiro de registos lógico, fazendo com que seja truncado e permitindo a diminuição. Modifique o exemplo de acordo com as necessidades do seu ambiente.
    SET NOCOUNT ON
    DECLARE @LogicalFileName sysname,
            @MaxMinutes INT,
            @NewSize INT
    
    -- Incluir critérios aqui.
    USE     Nome_da_base_de_dados           -- Este é o nome da base de dados para a qual o registo 
                                               será diminuído.
    SELECT  @LogicalFileName = 'Seu_reg',   -- Utilize sp_helpfile para identificar o nome do ficheiro 
                                               lógico que pretende diminuir.
            @MaxMinutes = 10,               -- Tempo limite permitido para reiniciar o registo.
            @NewSize = 100                  -- em MB
    
    -- Configuração / inicialização
    DECLARE @OriginalSize int
    SELECT @OriginalSize = size -- em páginas de in 8K
      FROM sysfiles
      WHERE name = @LogicalFileName
    SELECT 'Tamanho original de ' + db_name() + ' LOG é ' + 
            CONVERT(VARCHAR(30),@OriginalSize) + ' páginas de 8K ou ' + 
            CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
      FROM sysfiles
      WHERE name = @LogicalFileName
    CREATE TABLE DummyTrans
      (DummyColumn char (8000) not null)
    
    
    -- Reinicie o registo e execute a truncagem.
    DECLARE @Counter   INT,
            @StartTime DATETIME,
            @TruncLog  VARCHAR(255)
    SELECT  @StartTime = GETDATE(),
            @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
    -- Tente uma diminuição inicial.
    DBCC SHRINKFILE (@LogicalFileName, @NewSize)
    EXEC (@TruncLog)
    -- Reinicie o registo, se necessário.
    WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- o tempo não expirou
          AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  -- o registo 
                                                                                            não foi 
                                                                                            diminuído    
          AND (@OriginalSize * 8 /1024) > @NewSize  -- O valor indicado para o novo tamanho é inferior
                                                       ao tamanho actual.
      BEGIN -- Ciclo exterior.
        SELECT @Counter = 0
        WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
          BEGIN -- update
            INSERT DummyTrans VALUES ('Preencher registo')  -- Uma vez que se trata de um campo de 
                                                               caracteres, insere 8000 bytes.
            DELETE DummyTrans
            SELECT @Counter = @Counter + 1
          END   -- actualizar EXEC
        EXEC (@TruncLog)  -- Verificar se uma truncagem do registo o diminui.
      END   -- Ciclo exterior
    SELECT 'Tamanho final de ' + db_name() + ' LOG é ' +
            CONVERT(VARCHAR(30),size) + ' páginas de 8K ou ' + 
            CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
      FROM sysfiles 
      WHERE name = @LogicalFileName
    DROP TABLE DummyTrans
    PRINT '*** Cópia de segurança total da base de dados ***'
    SET NOCOUNT OFF
    Verifique se o registo diminuiu relativamente ao tamanho original. Repita os passos anteriores, se necessário. Se o registo não estiver a diminuir, consulte de novo o resumo no início do artigo para verificar se está a ter algum dos problemas comuns relacionados com a diminuição do registo.
Após a diminuição do registo:
  1. Efectue uma cópia de segurança completa da base de dados principal.
  2. Efectue uma cópia de segurança completa da base de dados do utilizador. Esta acção é necessária, já que o comando SHRINK não está registado e invalida as futuras cópias de segurança de registos de transacção, a menos que seja concluída uma cópia de segurança da totalidade da base de dados.
Para determinar por que motivo o registo aumentou tanto de tamanho, é possível verificar se existem transacções abertas, transacções de execução demorada, transacções não replicadas ou transacções que envolvam grande quantidade de dados.

REFERÊNCIAS

Para obter informações adicionais, clique nos números de artigo existentes abaixo para visualizar os artigos na Microsoft Knowledge Base:
110139 INF: Causes of SQL Transaction Log Filling Up
62866 INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
66057 PRB: Running Out of Log Space When Running Large Bulk Loads
80629 PRB: Transaction Log Partially Truncated
SQL Server Books Online; topics: "Transaction Log Physical Architecture"; "Optimizing Transaction Log Performance"

Propriedades

Artigo: 256650 - Última revisão: 14 de maio de 2011 - Revisão: 3.0
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 7.0 Standard Edition
Palavras-chave: 
kbsqlmanagementtools kbinfo kbsqlserv kbsqlserv700 KB256650

Submeter comentários

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com