none
[Sql Server] Como faço para diminuir o tamanho da base SQL Server? RRS feed

  • Pergunta

  • Olá,

    Esta é uma grande dúvida que os administradores de banco de dados possuem.

    Já li sobre o tema e ainda não encontrei a tal solução.

    Alguém sabe exatamente o que posso fazer para diminuir o tamanho da base?

    Isto é possível mesmo?

    Obrigado


    K2rto'4 - Analista Sharepoint
    "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善

    domingo, 12 de outubro de 2014 22:00

Respostas

  • Oi k2rto'4,

    Dentre todas as dúvidas existentes sobre SQL Server, se "diminuir o tamanho de uma base" não é a campeã, está no páreo.

    Sim, é possivel. Minha primeira dica é utilizar a pesquisa do fórum e curiar vários tópicos filtrando pelo assunto "shrink".

    Alguns comentários:

    1. Toda base de dados possui pelo menos um arquivo de dados e um arquivo de log;
    2. O "tamanho de uma base" é um termo relativo. No geral, você calcula o tamanho de uma base somando todos os arquivos da mesma, ou seja, considerando também o arquivo de log. Dependendo da análise que está sendo realizada, podemos inferir que "Tamanho Total de uma base" vai levar em consideração apenas os arquivos de dados. Para a explicação a seguir, vou considerar que o tamanho da base inclui os arquivos de log de transação também (eu resolvi detalhar esse ponto antes que algum mal entendido ocorra)
    3. Dito isso, vamos  supor que você tem uma base chamada BDCorporativo onde temos um arquivo de dados (.mdf) que ocupa 300 GB em disco e um arquivo de log (.ldf) que ocupa 3GBs em disco. Então o tamanho total de nossa  para nosso exemplo é de 303GB.
    4. A menor unidade física de uma base é uma página de dados (data page). Essa página tem 8Kb.
    5. Essa mesma base tem 300GB de dados em disco, mas isso não significa que ela possui 300GB de dados. Significa apenas que em disco, existem 300GB de espaço reservados para seu arquivo. A mesma coisa vale para o seu arquivo de log de 3GB: ele pode estar utilizando de fato, por exemplo, 400 MB.

    Considerações iniciais feitas, vamos falar um pouco sobre os dois itens:

    Shrink

    Motivo de polêmica, os comandos dbcc shrinkfile e dbcc shrinkdatabase são opções para diminuir o tamanho da base. Não tem mágica nenhuma em ambos os comandos: ele na realidade não "capa" o arquivo, e sim realoca as páginas de um arquivo internamente e devolve o espaço que outrora estava "vazio" para o sistema operacional. Essa devolução é refletida no tamanho que o arquivo ocupa em disco.

    Bancos de dados tendem a crescer e se isso for necessário, o espaço alocado pelo arquivo de dados (ou log) em disco aumentará de acordo com suas configurações de autogrowth da base. Imagine agora que você fez aquele senhor expurgo de dados na sua base de 300GB e deletou quase 200GB de dados. Várias páginas físicas  foram apagadas e agora temos um espaço "vazio" dentro do arquivo de dados. Se novos registros forem incluídos, gradualmente ocuparão espaço e eventualmente vão reusar espaço que uma hora eram de outros registros.

    O Shrink realiza a organização interna do arquivo (com um algoritmo que muitos acham questionável e eu também, rs) e devolve espaço se possível para o sistema operacional. Aí sim seu arquivo de dados de que tinha 300GB vai ocupar em disco, por exemplo, uns 90 GB depois do shrink.

    E porque o SQL Server não faz isso sozinho ao detectar que tem muito espaço "desperdiçado", ele é "burro"? Não, Shrink é uma operação de IO extremamente custosa principalmente por realizar tratamento em todas as páginas de dados além de gerar fragmentação, tanto lógica como física. Felizmente, shrink em arquivos de log seguem uma lógica um pouco distinta e menos onerosas, e não vou abordá-la aqui pra não alongar ainda mais o assunto.

    O grande ponto é: mesmo com um grande expurgo (que eu acho um bom motivo dependendo do cenário pra realizar um shrink e ainda assim, reforço, depende), a base vai crescer de novo: vai valer a pena, ou melhor, é realmente necessário, devolver espaço pro SO que o SQL conquistou realizando a operação de encolhimento do arquivo?

    PS: Recomendo que você evite o dbcc shrinkdatabase (perigoso) e use com moderação (e cuidado) o dbcc shrinkfile depois de analisar se é realmente necessário. Existem diferenças entre os comandos, e isso já foi debatido aqui no fórum.

    Compressão de Dados (Data Compression)

    Compressão é um recurso Enterprise presente desde o SQL Server 2008 que possibilita compactação do conteúdo de uma página de dados, que conforme vimos, é a menor unidade de alocação de um banco de dados. Não significa que uma página de 8kb vai ser comprimida em 4kb ou menos, a ideia é que o conteúdo da página seja comprimido seguindo uma série de algoritmos  de compressão de dados. O SQL Server oferece em resumo Row Compression e Page Compression. A ideia aqui é reconstruir as páginas com conteúdo comprimido e assim por exemplo, se em determinada página de dados cabiam 30 registros de uma determinada tabela, agora comprimidos cabem 90, 100, 300, etc. Os números impressionam e depende muito de cada tabela e modelagem (metadados, tipos envolvidos das colunas).

    Claro que compressão não vem de graça e o uso de compressão e descompressão exige cálculos adicionais de CPU. Os ganhos de se utilizar compressão são inúmeros, e na maioria dos casos vale a pena deixar o servidor pagar alguns ciclos a mais de CPU. Recomendo a leitura pois o assunto é sensacional mas demanda uma boa análise.

    Posso fazer Shrink depois de fazer Compressão?

    Poder pode, mas será que realmente é necessário (reforço a pergunta novamente, assim como fiz no tópico de shrink)? Na maioria das vezes, não. Vale a pena pensar se realmente devolver espaço para o sistema operacional é uma preocupação justificável.

    -----

    É isso. A explicação ficou longa, mesmo sacrificando muita informação, mas espero que tenha te ajudado.

    Abaixo alguns links auxiliares:

    Shrink Database

    Threads no MSDN sobre Shrink

    Compressão de dados

    []'s


    Se a resposta ajudou, classifique e ajude outros membros da comunidade.



    segunda-feira, 13 de outubro de 2014 05:29
  • K2rto'4,

    Complementando a explicação do Renato, é possível também realizar o Shrink para cada arquivo vinculado ao seu banco de dados, separadamente: ".MDF", ".NDF" ou ".LDF".

    Esta operação pode depreciar a performance de sua instância SQL, principalmente para grandes bancos de dados, então sugiro que você realize esta tarefa em um horário alternativo, onde poucos usuários possam ser afetados por esta execução. Por segurança, realize um backup FULL de seu banco de dados (isto já deve reduzir seu arquivo de Log).

    Então você poderá efetuar o Shrink Database(como indicado pelo Renato) ou por arquivo, veja abaixo um exemplo de execução para cada um destes modelos:

    Shrink Database

    DBCC SHRINKDATABASE (SeuBanco, 5);
    GO

    Shrink File

    --Nome Lógico do Arquivo em seu Banco de dados
    DBCC SHRINKFILE (SeuBanco, 7);
    GO
    
    --Nome Lógico do Arquivo de Log em seu Banco de dados
    DBCC SHRINKFILE (SeuBanco_Log, 1);
    GO


    Para maiores informações veja:

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

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

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    segunda-feira, 13 de outubro de 2014 11:13
    Moderador
  • Obrigado mesmo pelas respostas.

    Mas vamos lá.

    Quero criar um roteiro básico aqui para tentar diminuir o tamanho do banco de dados.

    O que recomendam?

    Eu faria assim:

    1) Fazer um backup

    2) Mudar o Recovery model para Simples

    3) Executar um Shrink Files

    4) Executar um Shrink DataBase

    Isto já ajuda?


    K2rto'4 - Analista Sharepoint
    "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善

    k2rto'4,

    O Shrink Files é uma opção e o Shrink Database é outra, ambos tem a mesma finalidade, porém seguem caminhos diferentes.

    O backup é sempre uma segurança antes de realizar uma tarefa que modifica a estrutura de seu banco de dados, então é um passo recomendado porém não é obrigatório.

    Mudar seu Recovery para SIMPLE envolve uma análise do seu modelo de recuperação de dados, como o Renato já descreveu. Analise a criticidade de sua informação e se o tempo de backup e recuperação é aceitável para permanecer executando este modelo. 

    Não esqueça de marcar como resposta todos os posts que ajudaram na sua solução!

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    segunda-feira, 13 de outubro de 2014 19:29
    Moderador

Todas as Respostas

  • Oi k2rto'4,

    Dentre todas as dúvidas existentes sobre SQL Server, se "diminuir o tamanho de uma base" não é a campeã, está no páreo.

    Sim, é possivel. Minha primeira dica é utilizar a pesquisa do fórum e curiar vários tópicos filtrando pelo assunto "shrink".

    Alguns comentários:

    1. Toda base de dados possui pelo menos um arquivo de dados e um arquivo de log;
    2. O "tamanho de uma base" é um termo relativo. No geral, você calcula o tamanho de uma base somando todos os arquivos da mesma, ou seja, considerando também o arquivo de log. Dependendo da análise que está sendo realizada, podemos inferir que "Tamanho Total de uma base" vai levar em consideração apenas os arquivos de dados. Para a explicação a seguir, vou considerar que o tamanho da base inclui os arquivos de log de transação também (eu resolvi detalhar esse ponto antes que algum mal entendido ocorra)
    3. Dito isso, vamos  supor que você tem uma base chamada BDCorporativo onde temos um arquivo de dados (.mdf) que ocupa 300 GB em disco e um arquivo de log (.ldf) que ocupa 3GBs em disco. Então o tamanho total de nossa  para nosso exemplo é de 303GB.
    4. A menor unidade física de uma base é uma página de dados (data page). Essa página tem 8Kb.
    5. Essa mesma base tem 300GB de dados em disco, mas isso não significa que ela possui 300GB de dados. Significa apenas que em disco, existem 300GB de espaço reservados para seu arquivo. A mesma coisa vale para o seu arquivo de log de 3GB: ele pode estar utilizando de fato, por exemplo, 400 MB.

    Considerações iniciais feitas, vamos falar um pouco sobre os dois itens:

    Shrink

    Motivo de polêmica, os comandos dbcc shrinkfile e dbcc shrinkdatabase são opções para diminuir o tamanho da base. Não tem mágica nenhuma em ambos os comandos: ele na realidade não "capa" o arquivo, e sim realoca as páginas de um arquivo internamente e devolve o espaço que outrora estava "vazio" para o sistema operacional. Essa devolução é refletida no tamanho que o arquivo ocupa em disco.

    Bancos de dados tendem a crescer e se isso for necessário, o espaço alocado pelo arquivo de dados (ou log) em disco aumentará de acordo com suas configurações de autogrowth da base. Imagine agora que você fez aquele senhor expurgo de dados na sua base de 300GB e deletou quase 200GB de dados. Várias páginas físicas  foram apagadas e agora temos um espaço "vazio" dentro do arquivo de dados. Se novos registros forem incluídos, gradualmente ocuparão espaço e eventualmente vão reusar espaço que uma hora eram de outros registros.

    O Shrink realiza a organização interna do arquivo (com um algoritmo que muitos acham questionável e eu também, rs) e devolve espaço se possível para o sistema operacional. Aí sim seu arquivo de dados de que tinha 300GB vai ocupar em disco, por exemplo, uns 90 GB depois do shrink.

    E porque o SQL Server não faz isso sozinho ao detectar que tem muito espaço "desperdiçado", ele é "burro"? Não, Shrink é uma operação de IO extremamente custosa principalmente por realizar tratamento em todas as páginas de dados além de gerar fragmentação, tanto lógica como física. Felizmente, shrink em arquivos de log seguem uma lógica um pouco distinta e menos onerosas, e não vou abordá-la aqui pra não alongar ainda mais o assunto.

    O grande ponto é: mesmo com um grande expurgo (que eu acho um bom motivo dependendo do cenário pra realizar um shrink e ainda assim, reforço, depende), a base vai crescer de novo: vai valer a pena, ou melhor, é realmente necessário, devolver espaço pro SO que o SQL conquistou realizando a operação de encolhimento do arquivo?

    PS: Recomendo que você evite o dbcc shrinkdatabase (perigoso) e use com moderação (e cuidado) o dbcc shrinkfile depois de analisar se é realmente necessário. Existem diferenças entre os comandos, e isso já foi debatido aqui no fórum.

    Compressão de Dados (Data Compression)

    Compressão é um recurso Enterprise presente desde o SQL Server 2008 que possibilita compactação do conteúdo de uma página de dados, que conforme vimos, é a menor unidade de alocação de um banco de dados. Não significa que uma página de 8kb vai ser comprimida em 4kb ou menos, a ideia é que o conteúdo da página seja comprimido seguindo uma série de algoritmos  de compressão de dados. O SQL Server oferece em resumo Row Compression e Page Compression. A ideia aqui é reconstruir as páginas com conteúdo comprimido e assim por exemplo, se em determinada página de dados cabiam 30 registros de uma determinada tabela, agora comprimidos cabem 90, 100, 300, etc. Os números impressionam e depende muito de cada tabela e modelagem (metadados, tipos envolvidos das colunas).

    Claro que compressão não vem de graça e o uso de compressão e descompressão exige cálculos adicionais de CPU. Os ganhos de se utilizar compressão são inúmeros, e na maioria dos casos vale a pena deixar o servidor pagar alguns ciclos a mais de CPU. Recomendo a leitura pois o assunto é sensacional mas demanda uma boa análise.

    Posso fazer Shrink depois de fazer Compressão?

    Poder pode, mas será que realmente é necessário (reforço a pergunta novamente, assim como fiz no tópico de shrink)? Na maioria das vezes, não. Vale a pena pensar se realmente devolver espaço para o sistema operacional é uma preocupação justificável.

    -----

    É isso. A explicação ficou longa, mesmo sacrificando muita informação, mas espero que tenha te ajudado.

    Abaixo alguns links auxiliares:

    Shrink Database

    Threads no MSDN sobre Shrink

    Compressão de dados

    []'s


    Se a resposta ajudou, classifique e ajude outros membros da comunidade.



    segunda-feira, 13 de outubro de 2014 05:29
  • K2rto'4,

    Complementando a explicação do Renato, é possível também realizar o Shrink para cada arquivo vinculado ao seu banco de dados, separadamente: ".MDF", ".NDF" ou ".LDF".

    Esta operação pode depreciar a performance de sua instância SQL, principalmente para grandes bancos de dados, então sugiro que você realize esta tarefa em um horário alternativo, onde poucos usuários possam ser afetados por esta execução. Por segurança, realize um backup FULL de seu banco de dados (isto já deve reduzir seu arquivo de Log).

    Então você poderá efetuar o Shrink Database(como indicado pelo Renato) ou por arquivo, veja abaixo um exemplo de execução para cada um destes modelos:

    Shrink Database

    DBCC SHRINKDATABASE (SeuBanco, 5);
    GO

    Shrink File

    --Nome Lógico do Arquivo em seu Banco de dados
    DBCC SHRINKFILE (SeuBanco, 7);
    GO
    
    --Nome Lógico do Arquivo de Log em seu Banco de dados
    DBCC SHRINKFILE (SeuBanco_Log, 1);
    GO


    Para maiores informações veja:

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

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

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    segunda-feira, 13 de outubro de 2014 11:13
    Moderador
  • Bom dia Renato Siqueira e Durval Ramos,

    Então só conseguirei ter algum espaço significativo liberado em disco se eu deletar alguns vários arquivos da base ne?

    Certo?

    Obrigado pelas boas respostas!!!

    Abs


    K2rto'4 - Analista Sharepoint
    "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善

    segunda-feira, 13 de outubro de 2014 12:24
  • Bom dia Renato Siqueira e Durval Ramos,

    Então só conseguirei ter algum espaço significativo liberado em disco se eu deletar alguns vários arquivos da base ne?

    Certo?

    Obrigado pelas boas respostas!!!

    Abs


    K2rto'4 - Analista Sharepoint
    "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善

    K2rto'4,

    Não! Calma vou apresentar um caso que mostra que não é apenas questão de tabelas não utilizadas ou dados que podem ser expurgados.

    Um exemplo comum que acontece é que, com o tempo, após muita manipulação de dados os índices de uma ou mais tabelas passam a se fragmentar e vão expandindo o espaço físico de seu banco.

    No momento que você executa um REBUILD ou REORGANIZE destes índices, os dados são ajustados e ordenados para melhorar suas consultas e isso diminui o tamanho do seu índice, porém o espaço físico permanece o mesmo.

    Quando você executa o Shrink, este espaço que não é mais utilizado passa a ser liberado e o arquivo físico de seu Banco de dados diminui.

    No caso dos arquivos de Log (.ldf) é comum o aumento do arquivo, mesmo após a realização de um CHECKPOINT ou BACKUP FULL, então o Shrink é um método que ajuda a manter a estrutura com uma expansão controlada (neste caso pelo DBA ou responsável).

    Espero ter esclarecido que não é necessário excluir estrutura ou dados para reduzir um arquivo referente ao seu banco de dados.

    Não esqueça de marcar como resposta todos os posts que ajudaram na solução !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    segunda-feira, 13 de outubro de 2014 12:36
    Moderador
  • K2rto'4,

    Nem sempre o expurgo é a única solução pra devolver espaço para o sistema operacional, mas na maioria dos casos, o que passar disso ou não vale a pena, ou recuperar espaço é uma emergência ou é perda de tempo... O Durval indicou uma bom exemplo, que é o rebuild, comando que refaz os índices. Apenas uma observação: o rebuild seguido de shrink, dependendo dos índices envolvidos e do espaço ocupado no arquivo de dados pode não ser efetivo, em alguns casos, inclusive pode até aumentar o tamanho do arquivo para o sistema operacional.

    A grande questão é a seguinte: nem sempre vale a pena devolver espaço para o SO. Imagine que você tem um .mdf de 300GB, com 200GB  ocupados internamente, com índices que nunca foram desfragmentados. Você resolve fazer o rebuild e internamente, agora você tem 100GB ocupados e 200 GB livres. Supondo que você utilize shrinkfile (como disse antes, evite o shrinkdatabase) e encolha o arquivo para, digamos, 110GB, teríamos no arquivo pouco espaço e tão logo a base irá precisar crescer novamente. Além de não ser uma solução efetiva, ainda ganha fragmentação de brinde.

    Para complementar leia este  tópico e dê uma atenção a mais na resposta do Luiz Mercante (que foi certeira).


    []'s



    Se a resposta ajudou, classifique e ajude outros membros da comunidade.





    • Editado Renato Siqueira segunda-feira, 13 de outubro de 2014 14:24 +informação
    segunda-feira, 13 de outubro de 2014 13:08
  • Olá,

    Eu sei que a configuração de log deve ser a mínima possível e que com um shrink, o arquivo de log vai pro espaço fácil fácil.

    Neste caso basta deixar o Recovery model: Simple?

    Abs


    K2rto'4 - Analista Sharepoint
    "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善


    • Editado TI DEV segunda-feira, 13 de outubro de 2014 16:55
    segunda-feira, 13 de outubro de 2014 16:54
  • Oi,

    Você diz pro expurgo?

    DELETE é fully-logged em qualquer modelo de recuperação.

    Se a base já estiver em SIMPLE, a parte inativa do log é liberada a cada checkpoint e por isso é mais difícil que o arquivo se expanda (a não ser que haja uma transação muito grande que prenda o log e o faça aumentar de tamanho).

    Se não estiver em SIMPLE, ou seja, no modo FULL ou BULK-LOGGED, ao trocar para SIMPLE você quebra a cadeia de LSN e por isso, daquele momento em diante, você quebra a cadeia de backups, tendo que refazê-la posteriormente. É questão de planejar mesmo...Independente do modelo de recuperação, recomendo, que, caso realize expurgos, que o faça em DELETES parciais (divida o delete em várias partes) e em transações separadas, para evitar problemas de transações longas o que pode causar crescimento do arquivo, dependendo da quantidade de dados...Se for o caso e se for possível, vá tirando backups de log junto com o DELETE se exceder o t-log for uma preocupação. Lembrando que a janela de execução pra quaisquer das tarefas acima, seja shrink, delete, rebuild, deve ser escolhida sabiamente... 

    Tem uma thread aqui no fórum interessante sobre crescimento de log aqui 

    []'s


    Se a resposta ajudou, classifique e ajude outros membros da comunidade.


    • Editado Renato Siqueira segunda-feira, 13 de outubro de 2014 17:42 +informações
    segunda-feira, 13 de outubro de 2014 17:39
  • Obrigado mesmo pelas respostas.

    Mas vamos lá.

    Quero criar um roteiro básico aqui para tentar diminuir o tamanho do banco de dados.

    O que recomendam?

    Eu faria assim:

    1) Fazer um backup

    2) Mudar o Recovery model para Simples

    3) Executar um Shrink Files

    4) Executar um Shrink DataBase

    Isto já ajuda?


    K2rto'4 - Analista Sharepoint
    "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善

    segunda-feira, 13 de outubro de 2014 19:08
  • Obrigado mesmo pelas respostas.

    Mas vamos lá.

    Quero criar um roteiro básico aqui para tentar diminuir o tamanho do banco de dados.

    O que recomendam?

    Eu faria assim:

    1) Fazer um backup

    2) Mudar o Recovery model para Simples

    3) Executar um Shrink Files

    4) Executar um Shrink DataBase

    Isto já ajuda?


    K2rto'4 - Analista Sharepoint
    "Hoje melhor do que ontem, amanhã melhor do que hoje!" 改 善

    k2rto'4,

    O Shrink Files é uma opção e o Shrink Database é outra, ambos tem a mesma finalidade, porém seguem caminhos diferentes.

    O backup é sempre uma segurança antes de realizar uma tarefa que modifica a estrutura de seu banco de dados, então é um passo recomendado porém não é obrigatório.

    Mudar seu Recovery para SIMPLE envolve uma análise do seu modelo de recuperação de dados, como o Renato já descreveu. Analise a criticidade de sua informação e se o tempo de backup e recuperação é aceitável para permanecer executando este modelo. 

    Não esqueça de marcar como resposta todos os posts que ajudaram na sua solução!

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    segunda-feira, 13 de outubro de 2014 19:29
    Moderador