none
Diminuir Tamanho Arquivo .mdf RRS feed

 > 

  • Pergunta

  • Question
    Entrar para Votar
    1
    Entrar para Votar

     

    Pessoal, já pesquisei bastante, testei alguns comandos mas não obtive sucesso.

     

    Tenho uma base de 32GB que eu precisava reduzir seu tamanho, pois nossas fitas de backup estão estourando.

     

    Nesta base estamos deletando alguns milhoes de registros antigos, para posteriormente tentar fazer algo para diminuir seu tamanho em disco.

     

    Todos os dias fazemos backup full, e antes do backup fazemos as seguintes rotinas

     

    CHECKPOINT

    GO

    EXEC SP_SPACEUSED

    GO

    BACKUP LOG Meu_Banco WITH TRUNCATE_ONLY

    GO

    DBCC SHRINKFILE(Meu_Arquivo_Log', 100)

    GO

    DBCC SHRINKDATABASE('Meu_Banco', TRUNCATEONLY)

    GO

    DBCC SHRINKDATABASE ('Meu_Banco', 3)

    GO

    EXEC SP_SPACEUSED

     

     

    Após as pesquisas li sobre fazer o DBCC SHRINKFILE do Meu Arquivo .mdf tbm. Aí eu tentei

     

    DBCC SHRINKFILE(Meu_Arquivo_Data', 20000) -- Pra tentar deixar com 20Giga por exemplo. Mas não funcionou. Não diminuiu nada.

     

    Alguem poderia me aconselhar no que fazer para tentar reduzir este banco, já que falei que Logicamente, estamos deletando alguns milhoes de registros...

     

    Grato,

    Danilo

     

    quinta-feira, 10 de julho de 2008 20:37
    |

Respostas

  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Amigo,

    Nesse caso o arquivo "mdf" diminuirá se houve espaço.

    Geralmente algumas aplicações inseri informações em tabelas temporárias, drop table e delete. Isso são espaços em brancos.

    Fazendo o comando somente irá diminuir os espaços sobrando.

    Ex: Truncate table "tabela" todas as tabelas do banco, o arquivo mdf permanece no mesmo tamanho.

    segunda-feira, 12 de dezembro de 2016 21:25
    |

Todas as Respostas

  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Danilo,

     

    Após a executar estes comandos obrigatóriamente o tamanho físico dos arquivos .mdf e .ldf deveram ser reduzidos, mas o tamanho lógico, sendo aquele retornado pela sp_spaceused, pode não sofre mudanças.

     

    O que você poderia fazer é atualizar as estatísticas o seu banco de dados através desta system stored procedure sp_updatestats ou pelo comando UPDATE STATISTICS.

     

    Realize todos os passos que você esta executando, após executar o último DBCC ShrinkDatabase, utilize um das opções que indiquei e verifique o resultado.

     

     

    sexta-feira, 11 de julho de 2008 10:48
    |
    Moderador
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Júnior, infelizmente o tamanho do banco não modificou-se.

     

    Executei o DBCC ShrinkFile no meu DataFile e não diminui. Atualizei as estatísticas, tudo como você disse.

     

    No Arquivo de Log Beleza, diminuiu certinho, mas no arquivo de Data não.

     

    Como lhe falei, ontem fiz uma rotina que excluiu mais de 8 milhoes de registros antigos. Minha esperança era que hoje após os "Shrinks" ele tivesse diminuído. Mas não foi isso que aconteceu...

     

    Será que alguma configuração específica pode não deixar o arquivo diminuir de tamanho?

     

    Danilo

    sexta-feira, 11 de julho de 2008 11:37
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Danilo,

     

    Verifique se a opção AutoShrink esta ativa.

     

    Você excluir milhões de registros, sendo assim, esta eliminação dos registros não representa que a área que o mesmo estava sendo utilizada será desconsiderada. Neste caso aconselho a reindexar toda a sua base de dados, fazendo que o SQL Server descarte estas áreas que estão em branco.

     

    Utilize o comando DBCC DBReindex.

    sexta-feira, 11 de julho de 2008 11:44
    |
    Moderador
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Boa Tarde Júnior.

     

    Respondendo sua pergunta. O banco está com AutoShrink como True

     

    Executei o DBCC DBReindex nas duas maiores tabelas afetadas neste banco de dados, com o comando abaixo:

     

    DBCC DBReindex('Tabela_1','')

    DBCC DBReindex('Tabela_2','')

     

    Mas não diminuiu...

     

    Ontem antes do backup, após fazer toda rotina já descrita(backup log, shrink, etc) ele tinha 32 Gb. Agora ele está com 36 Gb

     

    Essas operações de DBReindex, Update Statistics pode fazer ele crescer tanto, pois verifiquei quantos registros foram incluídos hoje neste banco. Cerca de 250000 registros apenas hoje até as 13:00.

     

    Imagino eu que com o AutoShrink como True, a rotina de exclusão que eu te falei deveria fazer o banco diminuir, visto que ontem eu excluí mais do incluí(8 milhoes de exclusoes contra 200 mil inclusoes. OBS: estas exclusões e inclusõess são em cima das mesmas tabelas).

     

    Se necessitar de mais informações sobre as opções de configuração do banco, só me pedir.
    sexta-feira, 11 de julho de 2008 16:28
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Danilo,

     

    O AutoShrink é uma opção utilizada para verificar se o banco de dados deverá ser diminuído, mas isso também pode sofrer impactos de como esta configurado a forma de crescimento do banco de dados.

     

    Mesmo após todo este procedimentos o banco de dados continua com o mesmo tamanho?

     

    Este tamanho que você se refere é do arquivo de log?

    sexta-feira, 11 de julho de 2008 17:17
    |
    Moderador
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Sim, Junior. Posso executar os Shrinks no logfile e no datafile agora que não diminui o arquivo mdf. Se eu executá-los o arquivo de log diminui, mas o arquivo de data não.

     

    Neste momento, sem fazer os referidos comandos:

    O Arquivo de Log está com 100MB agora.

    O Arquivo de Data está com 36GB agora

     

    Você comentou sobre a forma de crescimento. Creio que está padrão do Banco de Dados, pois não mexi nisto.

    A propriedade AutoGrowth está:

    - Enable

    - File Growth em Percent com valor 10

    - Maximum File Size está Unrestricted File Growth

     

    Me diga uma coisa. Para este banco de 32GB quais seriam os parametros para o meu propósto(diminuir o tamanho), pois hoje eu executo ele assim:

     

    DBCC ShrinkFile('Meu_Arquivo_Data',20000)

     

    De repente estou fazendo algo errado ainda neste comando.

     

    Danilo

    sexta-feira, 11 de julho de 2008 17:44
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Danilo,

     

    Muito estranho.

     

    Bom, aconselho acessar o Books On-Line do SQL Server sobre Shrink Database ou Shrink File, com certeza você vai encontrar muita informação.

    sexta-feira, 11 de julho de 2008 18:04
    |
    Moderador
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Junior... vou ler mais o Books On-Line... testar mais as opções que você me deu...

     

    Obrigado

     

    terça-feira, 15 de julho de 2008 20:35
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Ola Danilo,

     

    Ja enfrentei este problema.

    A solução adotada foi um pouco radical. Mas funcionou.

    Fiz um DTS (Era SQL2000) de todas as tabelas e objetos para uma nova base e recriei as estruturas de indices e chaves estrangeiras.  Desta forma eu conesegui liberar todo espaço e de quebra o desempenho melhorou bastante.

    Se tiver espaço em disco tente um copy database. Se der certo apague a base original e mude a nome da nova base.

     

    Sds.

     

     

    quinta-feira, 18 de setembro de 2008 19:33
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Danilo somente comentando tem uma forma também que fazemos para diminuir o tamanho de sua base, é o de dar um detach e posterior um attach, com isto reduzindo o seu tamanho, após fazer as instruções que foram comentadas acima.

     

     

    quarta-feira, 5 de novembro de 2008 13:10
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Olá Danilo, só um detalhe que vc não deve esquecer é que:

    se vc estiver usando SHRINKFILE, então, esteja usando o database, já com o SHRINKDATABASE não precisa disso.

    exemplo:

     

    Code Snippet

    USE SEU_DATABASE

    GO

    DBCC SHRINKFILE........

     

     

    USE MASTER

    GO

    DBCC SHRINKDATABASE.......

     

     

     

    quinta-feira, 8 de janeiro de 2009 04:02
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Olá Danilo,

     

    Você já tentou esse código:

     

     

    Code Snippet

    USE NOME_DA_BASE -- usar [ ] para bases com -

    backup log NOME_DA_BASE with truncate_only

    DBCC SHRINKDATABASE (NOME_DA_BASE, 0)

    USE NOME_DA_BASE
    GO
    /* Reduz Log */
    SET NOCOUNT ON
    DECLARE @LogicalFileName sysname,
            @MaxMinutes INT,
            @NewSize INT,
            @Database_Name varchar(255)

    SELECT @Database_Name = 'NOME_DA_BASE'

    SELECT name FROM SYSFILES WHERE GROUPID = 0

        SELECT  @LogicalFileName = name
          FROM SYSFILES WHERE GROUPID = 0           -- Use sp_helpfile to identify the logical file name that you want to shrink.
       
        SELECT  @MaxMinutes = 1,                -- Limit on time allowed to wrap log.
                @NewSize = 200                       -- in MB
       
        -- Setup / initialize
        DECLARE @OriginalSize int
        SELECT @OriginalSize = size -- in 8K pages
          FROM sysfiles
          WHERE name = Rtrim(@LogicalFileName)
        SELECT 'Original Size of ' + db_name() + ' LOG is ' +
                CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
                CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
          FROM sysfiles
          WHERE name = @LogicalFileName
        CREATE TABLE DummyTrans
          (DummyColumn char (8000) not null)
       
       
        -- Wrap log and truncate it.
        DECLARE @Counter   INT,
                @StartTime DATETIME,
                @TruncLog  VARCHAR(255)
        SELECT  @StartTime = GETDATE(),
                @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
        -- Try an initial shrink.
        select @LogicalFileName = rtrim(@LogicalFileName)
        DBCC SHRINKFILE (@LogicalFileName, @NewSize)
        EXEC (@TruncLog)
        -- Wrap the log if necessary.
        WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
              AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  -- the log has not shrunk   
              AND (@OriginalSize * 8 /1024) > @NewSize  -- The value passed in for new size is smaller than the current size.
          BEGIN -- Outer loop.
            SELECT @Counter = 0
            WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
              BEGIN -- update
                INSERT DummyTrans VALUES ('Fill Log')  -- Because it is a char field it inserts 8000 bytes.
                DELETE DummyTrans
                SELECT @Counter = @Counter + 1
              END   -- update
            EXEC (@TruncLog)  -- See if a trunc of the log shrinks it.
          END   -- outer loop
        SELECT 'Final Size of ' + db_name() + ' LOG is ' +
                CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
                CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
          FROM sysfiles
          WHERE name = @LogicalFileName
       
    DROP TABLE DummyTrans
    PRINT '*** Perform a full database backup ***'
    SET NOCOUNT OFF

     

     

    Onde está escrito nome_da_base coloque o nome da sua base de dados
    terça-feira, 27 de janeiro de 2009 17:50
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar
    Ricardo,

    Esse script tem o objetivo em diminuir o .mdf?
    Pergunto pq estou com a minha em 40GB e preciso o quanto antes.

    Abs.
    sexta-feira, 24 de julho de 2009 12:33
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar
    Diogenes,

    Este script basicamente realiza a diminuição física e lógica do banco.
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    sábado, 25 de julho de 2009 01:36
    |
    Moderador
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Senhores,

    É necessário que se veja o total que está sendo alocado nos indices criados. Isto pois para que se crie uma boa consulta, é necessário que se crie alguns indices na base. Porem estes indices chegam a ocupar muito espaço no banco de dados. Desta forma, devemos dropar os indices e depois crialos novamente. É necessário que se verifique se os indices estão realmente sendo criados de forma correta e se realmente são necessários para aquela consulta. Feito isso a base deverá diminuir consideravelmente. Na próxima eu disponibilizo uma procedure para gerar estes valores, agora estou com pouco tempo.

    Abs.

    quinta-feira, 27 de maio de 2010 17:56
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Amigo,

    Nesse caso o arquivo "mdf" diminuirá se houve espaço.

    Geralmente algumas aplicações inseri informações em tabelas temporárias, drop table e delete. Isso são espaços em brancos.

    Fazendo o comando somente irá diminuir os espaços sobrando.

    Ex: Truncate table "tabela" todas as tabelas do banco, o arquivo mdf permanece no mesmo tamanho.

    segunda-feira, 12 de dezembro de 2016 21:25
    |