none
Crescimento do Arquivo Log e Dados SQL Server 2008 RRS feed

  • Pergunta

  • Pessoal, realizei a pouco tempo uma migração de versão do SQL Server e percebi a seguinte mudança. A base anteriormente era de 38GB, após a migração e passado 2 dias a base cresceu pra 107GB e log pra 60GB. Apenas foi configurado na base as rotinas de backups e de manutenção, da seguinte forma:

    Passo 1 >> Reorganização dos Índices;

    Passo 2 >> Atualização das Estatísticas;

    Passo 3 >> Backup Full.

    No decorrer do dia é feito o Backup Diferencial a cada 4 horas e de Log a cada 1 hora. Inclusive executei o comando:

    SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
    FROM sys.database_files;

    Para identificar quanto havia de espaço reservado a ser retornado ao SO' e assim reduzir meu arquivo de dados e log, no qual aumentaram muito depois da migração. Os resultados foram:

    Base_Data    2344.562500 MB (2GB)
    Base_Log    21541.460938 MB (22GB)

    Achei pouco para o que era antes. Com os resultados executei os comandos:

    USE [Base]
    GO
    DBCC SHRINKFILE (1, 0, TRUNCATEONLY)
    DBCC SHRINKFILE (2, 0, TRUNCATEONLY)

    Somente reduziu o de log para 22GB.

    Tipo de Recuperação da Base: Full.




    terça-feira, 25 de junho de 2013 12:55

Respostas

  • Olá Marcus, executei o seguinte comando:

    USE Base;
    GO
    EXEC sp_spaceused
    GO

    Para analisar os resultados usado pela base no disco antes de atualizar as contagens páginas e linhas da mesma base e veja o retorno:

    Database_name  Database_Size  Unallocated_space
    Base  186874.50 MB  1573.11 MB
    Reserved Data Index_size Unused
    105887504 KB 55043000 KB 49802256 KB 1042248KB

    Após executar o comando:

    USE Base
    GO
    DBCC UPDATEUSAGE (0);
    GO

    O resultado da sp_spaceused foi:

    Database_name  Database_Size  Unallocated_space
    Base 186874.50 MB  2258.30 MB
    Reserved Data Index_size Unused
    105185864 KB 55065896 KB 49774040 KB 345928KB

    Não teve redução nos arquivos ldf e mdf.
    terça-feira, 2 de julho de 2013 17:25

Todas as Respostas

  • AMIGO O QUE SERIA 1,0 E 2,0 ?
    terça-feira, 25 de junho de 2013 18:35
  • São as referências do IDs dos arquivos físicos mdf e ldf.
    terça-feira, 25 de junho de 2013 23:30
  • Jeferson,

    De qual versão do SQL Server você realizou a migração?

    Você alterou o Nível de Compatibilidade do Banco de Dados para esta nova versão?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sexta-feira, 28 de junho de 2013 16:30
    Moderador
  • O que essa consutla retorna ?

    select log_reuse_wait_desc from sys.databases where name = 'NOME_BASE'


    []'s | Rodrigo Ribeiro Gomes | MCTS/MCITP Dev/DBA

    segunda-feira, 1 de julho de 2013 13:55
  • Realizei a migração do SQL Server 2000 para 2008. No SQL Server 2008 que recebeu a nova base, o nível de compatibilidade permanece com valor 8.0, por questões da aplicatção só permite rodar satisfatoriamente com esse nível. 

    select log_reuse_wait_desc from sys.databases where name = 'Base'

    Resultado:

    NOTHING

    segunda-feira, 1 de julho de 2013 19:29
  • Jeferson,

    Após a migração do SQL 2000 você executou o DBCC UPDATEUSAGE? Em migrações do SQL 2000 sempre recomendam rodar esse comando na instancia para atualizar os metadados.

    http://msdn.microsoft.com/pt-br/library/ms188414.aspx

    Se a resposta foi útil, classifique-a


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    segunda-feira, 1 de julho de 2013 22:34
  • Olá Marcus, executei o seguinte comando:

    USE Base;
    GO
    EXEC sp_spaceused
    GO

    Para analisar os resultados usado pela base no disco antes de atualizar as contagens páginas e linhas da mesma base e veja o retorno:

    Database_name  Database_Size  Unallocated_space
    Base  186874.50 MB  1573.11 MB
    Reserved Data Index_size Unused
    105887504 KB 55043000 KB 49802256 KB 1042248KB

    Após executar o comando:

    USE Base
    GO
    DBCC UPDATEUSAGE (0);
    GO

    O resultado da sp_spaceused foi:

    Database_name  Database_Size  Unallocated_space
    Base 186874.50 MB  2258.30 MB
    Reserved Data Index_size Unused
    105185864 KB 55065896 KB 49774040 KB 345928KB

    Não teve redução nos arquivos ldf e mdf.
    terça-feira, 2 de julho de 2013 17:25
  • Olá Jeferson,

    Você pode ver que teve algumas mudanças, certo?

    Mas, você acredita que ainda está errado, correto? Acredito que no mais eu realizaria uma verificação das principais tabelas e ver se todos os dados estão intactos. Fora isso como foi o seu processo de migração? backup/restore? Porque nesse caso, eu nao acreditaria que o SQL Server estivesse errado ou mostrando os valores incorretos após o DBCC UPDATEUSAGE.

    Se a resposta foi util, classifique-a


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    terça-feira, 2 de julho de 2013 17:36
  • Entendo Marcos, mas qual a explicação para uma base que no SQL Server 2000 era em torno de 38GB e passou pra mais que o dobro no SQL Server 2008, isso após dois dias de ter sido colocado em produção?

    No processo de migração foi usado backup/restore e posteriormente no mesmo momento a após a migração foi dado um rebuild nos índices das tabelas.

    Tenho a mesma a base um pouco desatualizada no SQL Server 2008, no qual uso para testes e a mesma está com 38GB normalmente. E o processo usado foi o mesmo, backup/restore. 

    terça-feira, 2 de julho de 2013 18:11
  • Jerferson,

    Você sabe me dizer qual é o FillFactor que esta configurado para suas tabelas e índices?

    Utilize o bloco de código abaixo para obter esta informação:

    select sys.tables.name as tabela, 
               sys.indexes.name as indice, 
               sys.indexes.type_desc as tipo, 
               sys.indexes.fill_factor, 
               sys.indexes.is_padded as padded
            from sys.indexes inner join  sys.tables
                                         on sys.indexes.object_id = sys.tables.object_id
    where sys.indexes.is_disabled =0 
    and sys.indexes.type <> 0
    order by tabela, tipo 

    Este tipo de crescimento é normal de acontecer quando o banco de dados sobre uma alteração na forma de armazenamento interna das tabelas e índices.

    Você informou que o nível de compatibilidade foi mantido como 80 por questões de compatibilidade com aplicação!!!

    Mas sinceramente o que a sua aplicação exige para manter este nível?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]


    quarta-feira, 3 de julho de 2013 19:01
    Moderador
  • Galvão, já estava usando o comando:

     SELECT
        @@servername As Server, convert(varchar(25),db_name()) as DatabaseName,
    convert(varchar(50),OBJECT_NAME (a.object_id)) as Tabela, 
    a.index_id,
    convert(varchar(50),b.name) as IndexName, 
    avg_fragmentation_in_percent, index_type_desc, GETDATE() as Data,
    page_Count,fill_factor
    FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
    JOIN sys.indexes AS b   
    ON a.object_id = b.object_id AND a.index_id = b.index_id
    WHERE b.index_id <> 0 and avg_fragmentation_in_percent <> 0

    Com isso peguei os resultados e joguei numa planilha pra analisar os resultados das colunas avg_fragmentation_in_percent, page_count e fill_factor. Relacionando-as através da seguinte lógica:

    1 - avg_fragmentation_in_percent com percentual acima de 15% = fragmentada;

    2 - quantidade de páginas contabilizadas em page_count = alta;

    3 - valor do fill_factor = definir valor baixo.

    Acho que a lógica seria essa, me corrija se estiver errado.

    Encontrei muitos índices que considerei estarem configurados erroneamente, tipo:

    Index_Name = Chave_PK;

    avg_fragmentation_in_percent com percentual = 16,6666667;

    Tipo_Index = Clustered Index;

    Count_Pages = 6;

    Fill_Factor = 20.

    Achei esse valor de fillfactor impróprio pra o índice, pois o mesmo apresenta um baixo acesso de modificações e atualizações e nisso o valor deveria ser o mais próximo possível do 100. Confere?

    Levando essa lógica em questão na análise dos demais é possível ganhar mais espaço em disco, configurando os fillfactor de outra forma?



    quinta-feira, 4 de julho de 2013 12:24