none
Shrink em arquivo MDF RRS feed

  • Pergunta

  • Pessoal,

    Desculpe ressuscitar esse tópico, mas é que eu estou com um problema sério no Banco de Dados no vCenter da empresa que presto suporte. Ele está sempre "estourando" de espaço o arquivo .mdf, e, como solução temporária, eu estou criando volumes na partição em que o arquivo está armazenado no meu Windows e estendendo essa partição. Inicialmente a partição estava com 100 GB, e eu dei mais 100 GB, e o arquivo .mdf utilizou todo este espaço! Eu não conheço nada de SQL Server, então gostaria de saber se tem algum procedimento fácil, alguma receita de bolo para eu fazer um Shrink neste arquivo MDF (o LDF estava assim também, mas eu consegui resolver com base no link http://kb.vmware.com/selfservice/search.do?cmd=displayKC&docType=kc&docTypeID=DT_KB_1_1&externalId=1003980), ou algo que eu possa fazer para diminuir esse arquivo. 

    O SQL Server utlizado é o 2012. 

    Obrigado desde já.

    • Dividir Durval RamosModerator segunda-feira, 25 de maio de 2015 18:38 Motivo: Aumentar possibilidade de solução
    segunda-feira, 25 de maio de 2015 18:31

Respostas

  • Pessoal tudo bem?

    Alguma idéia?

    Obrigado.

    Amigo,

    Antes de mais nada, crie uma rotina para realização de BACKUP (FULL, DIFF e LOG), isto porque o arquivo ".ldf" (Log) não está sendo reduzido porque seu "Recovery Model" está configurado como "FULL" e "algum procedimento" no seu backup "não" está funcionando.

    Após você realizar o "BACKUP FULL" deste banco de dados, verifique se o arquivo de Log não tenha sido reduzido. Se o tamanho persistir, você poderá alterar o "Recovery Model" para "Simple" e então realizar o Shrink como uma "condição emergencial" para liberar seu espaço.

    Se você precisar executar o Shrink em outros arquivos deste banco (.mdf ou .ndf), então recomendo que você efetue esta operação em um "horário alternativo" para evitar lentidão nas manipulações de dados de outros usuários no ambiente de Produção.

    Também será necessário verificar a fragmentação de seus índices e realizar o REBUILD/REORGANIZE de acordo com cada necessidade.

    Segue abaixo um script T-SQL para alterar o Recovery Model e realizar o Shrink no banco de dados:

    -- Alterando o "Modelo de Recuperação" para SIMPLE USE [master] GO ALTER DATABASE SeuBanco SET RECOVERY SIMPLE; GO --Verifique se o Modelo do banco foi alterado SELECT name AS Banco, recovery_model_desc AS Modelo FROM sys.databases WHERE name = 'SeuBanco'; GO USE SeuBanco; GO --Reduz o arquivo ".MDF" deste banco para 4.670Mb DBCC SHRINKFILE (SeuBanco_Data, 4670); GO --Reduz o arquivo ".LDF" deste banco para 1Mb DBCC SHRINKFILE (SeuBanco_Log, 1); GO

    -- Alterando o "Modelo de Recuperação" para FULL USE [master] GO ALTER DATABASE SeuBanco SET RECOVERY FULL; GO


    Você precisa verificar também como está configurado o crescimento deste banco. Utilize sempre o "AutoGrowth" em "Mb". Isto porque quando se configura para o crescimento em "Porcentagem" a tendência é sempre o SQL Server aumentar o espaço mais do que o ideal, podendo também gerar lentidão no I/O desta operação (afetando todos os usuários).

    Veja na imagem abaixo um exemplo de configuração deste crescimento:


    Para maiores informações veja:

    https://technet.microsoft.com/pt-br/library/ms178052(v=sql.105).aspx

    https://msdn.microsoft.com/pt-br/library/ms189272.aspx

    https://msdn.microsoft.com/pt-br/library/ms189493.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"

    • Sugerido como Resposta Lucio Rogerio SPBanned quarta-feira, 10 de junho de 2015 00:52
    • Marcado como Resposta Marcos SJ quinta-feira, 24 de setembro de 2015 13:26
    terça-feira, 26 de maio de 2015 12:25
    Moderador

Todas as Respostas

  • Windows Man,

    Você poderia detalhar um pouco mais sobre o seu ambiente?

    Aqui no Instituto em que trabalho para um projeto específico utilizamos o VCenter rodando em um Windows Server 2012, a versão do VM é a EXSI 5.5, em conjunto com um SQL Server 2008 Express que é o padrão desta versão do VCenter.

    Você esta destacando que o SQL Server esta "estourando" o espaço do seu arquivo de dados, sinceramente algo muito fora do comum para um SQL Server.



    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    segunda-feira, 25 de maio de 2015 18:43
  • Se tiver necessidade de analisar o tamanho das tabelas, veja query para listar ordenado pelas maiores:

    SELECT
        t.NAME AS Entidade,
        p.rows AS Registros,
        SUM(a.total_pages) * 8 AS EspacoTotalKB,
        SUM(a.used_pages) * 8 AS EspacoUsadoKB,
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacoNaoUsadoKB
    FROM     sys.tables t INNER JOIN  sys.indexes i ON t.OBJECT_ID = i.object_id
          INNER JOIN  sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
          INNER JOIN  sys.allocation_units a ON p.partition_id = a.container_id
          LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE
        t.NAME NOT LIKE 'dt%'
        AND t.is_ms_shipped = 0
        AND i.OBJECT_ID > 255
    GROUP BY     t.Name, s.Name, p.Rows ORDER BY Registros DESC;

    segunda-feira, 25 de maio de 2015 19:07
  • Junior Galvão tudo bem?

    Então, no meu ambiente roda o vCenter 5.1 num Windows Server 2012 R2, e com o SQL Server 2012. No caso, foram criadas duas partições neste servidor: uma para armazenar os arquivos MDF, e outra para os arquivos LDF. A partição que tem o arquivo ldf estava estourando espaço no disco. Aí, fiz o Shink conforme o link que mencionei acima (http://kb.vmware.com/selfservice/search.do?cmd=displayKC&docType=kc&docTypeID=DT_KB_1_1&externalId=1003980). Para este arquivo .ldf, funcionou a contento. O espaço, que estava comendo mais de 100 GB, que era a partição destinada a ele, ficou com apenas 1 GB de uso. Agora, com o arquivo mdf, eu não sei se é o mesmo procedimento. E, com certeza concordo com você, é muito anormal isso que está acontecendo. Eu preciso investigar mais a fundo o que acontece no vCenter daqui. Mas, neste momento, o mais importante pra mim agora é recuperar o banco (que está parado inclusive por causa disso), e poder liberar esse espaço desse arquivo .mdf que está enorme.

    Desculpe me estender, espero que tenha ficado compreensível.

    Obrigado

    segunda-feira, 25 de maio de 2015 19:18
  • Pessoal tudo bem?

    Alguma idéia?

    Obrigado.

    terça-feira, 26 de maio de 2015 11:48
  • Pessoal tudo bem?

    Alguma idéia?

    Obrigado.

    Amigo,

    Antes de mais nada, crie uma rotina para realização de BACKUP (FULL, DIFF e LOG), isto porque o arquivo ".ldf" (Log) não está sendo reduzido porque seu "Recovery Model" está configurado como "FULL" e "algum procedimento" no seu backup "não" está funcionando.

    Após você realizar o "BACKUP FULL" deste banco de dados, verifique se o arquivo de Log não tenha sido reduzido. Se o tamanho persistir, você poderá alterar o "Recovery Model" para "Simple" e então realizar o Shrink como uma "condição emergencial" para liberar seu espaço.

    Se você precisar executar o Shrink em outros arquivos deste banco (.mdf ou .ndf), então recomendo que você efetue esta operação em um "horário alternativo" para evitar lentidão nas manipulações de dados de outros usuários no ambiente de Produção.

    Também será necessário verificar a fragmentação de seus índices e realizar o REBUILD/REORGANIZE de acordo com cada necessidade.

    Segue abaixo um script T-SQL para alterar o Recovery Model e realizar o Shrink no banco de dados:

    -- Alterando o "Modelo de Recuperação" para SIMPLE USE [master] GO ALTER DATABASE SeuBanco SET RECOVERY SIMPLE; GO --Verifique se o Modelo do banco foi alterado SELECT name AS Banco, recovery_model_desc AS Modelo FROM sys.databases WHERE name = 'SeuBanco'; GO USE SeuBanco; GO --Reduz o arquivo ".MDF" deste banco para 4.670Mb DBCC SHRINKFILE (SeuBanco_Data, 4670); GO --Reduz o arquivo ".LDF" deste banco para 1Mb DBCC SHRINKFILE (SeuBanco_Log, 1); GO

    -- Alterando o "Modelo de Recuperação" para FULL USE [master] GO ALTER DATABASE SeuBanco SET RECOVERY FULL; GO


    Você precisa verificar também como está configurado o crescimento deste banco. Utilize sempre o "AutoGrowth" em "Mb". Isto porque quando se configura para o crescimento em "Porcentagem" a tendência é sempre o SQL Server aumentar o espaço mais do que o ideal, podendo também gerar lentidão no I/O desta operação (afetando todos os usuários).

    Veja na imagem abaixo um exemplo de configuração deste crescimento:


    Para maiores informações veja:

    https://technet.microsoft.com/pt-br/library/ms178052(v=sql.105).aspx

    https://msdn.microsoft.com/pt-br/library/ms189272.aspx

    https://msdn.microsoft.com/pt-br/library/ms189493.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"

    • Sugerido como Resposta Lucio Rogerio SPBanned quarta-feira, 10 de junho de 2015 00:52
    • Marcado como Resposta Marcos SJ quinta-feira, 24 de setembro de 2015 13:26
    terça-feira, 26 de maio de 2015 12:25
    Moderador
  • Durval bom dia,

    Me desculpe as perguntas, é que eu não entendo nada de nada de sql. Esse script que você colocou pra executar já faz tudo isso que você falou? E, onde eu executo esse script? Tem algum prompt de sql server pra executar esse script? Então, no meu caso, o arquivo .ldf já está ok. O que eu preciso diminuir drasticamente é o arquivo .mdf. Inclusive por conta dele, meu servidor está lentíssimo, e perdendo conectividade. Alguma atividade no banco do vCenter está sendo executado e deixando o servidor em que está o banco lentíssimo, e inclusive caindo o serviço do vCenter Server.

    Obrigado pela ajuda.

    terça-feira, 26 de maio de 2015 12:36
  • Durval bom dia,

    Me desculpe as perguntas, é que eu não entendo nada de nada de sql. Esse script que você colocou pra executar já faz tudo isso que você falou? E, onde eu executo esse script? Tem algum prompt de sql server pra executar esse script? Então, no meu caso, o arquivo .ldf já está ok. O que eu preciso diminuir drasticamente é o arquivo .mdf. Inclusive por conta dele, meu servidor está lentíssimo, e perdendo conectividade. Alguma atividade no banco do vCenter está sendo executado e deixando o servidor em que está o banco lentíssimo, e inclusive caindo o serviço do vCenter Server.

    Obrigado pela ajuda.

    Amigo,

    Por favor, não me leve a mal, mas após estes comentários a melhor sugestão que posso passar para você é procurar um DBA que possa analisar seu ambiente e configurar adequadamente sua instância SQL, realizar as manutenções de rotina e aplicar as correções necessárias.

    Alguns "ajustes" precisam ter um cuidado, e responsabilidade, especial caso contrário não existiriam os DBA´s. 

    Não dá para ensinar (quase) tudo em um tópico de Fórum, espero que compreenda. Ao mesmo tempo, seria imprudência minha tentar orientar você (com tantas dúvidas) em poucas palavras.

    Desculpe pelo inconveniente.

    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"

    terça-feira, 26 de maio de 2015 12:48
    Moderador
  • Durval,

    Eu entendo. E aí tenho um problema: Não tenho DBAs aqui. Quer dizer, até tenho, mas ele não conhece SQL Server, apenas Oracle. Eu estava procurando alguma solução simples, que eu mesmo pudesse fazer, pra resolver esse problema, que está acabando com meu ambiente vmware. Mas, pelo visto, vou ter que ver alguma outra forma...

    Obrigado de qualquer forma.

    terça-feira, 26 de maio de 2015 13:04
  • Durval bom dia,

    Me desculpe as perguntas, é que eu não entendo nada de nada de sql. Esse script que você colocou pra executar já faz tudo isso que você falou? E, onde eu executo esse script? Tem algum prompt de sql server pra executar esse script? Então, no meu caso, o arquivo .ldf já está ok. O que eu preciso diminuir drasticamente é o arquivo .mdf. Inclusive por conta dele, meu servidor está lentíssimo, e perdendo conectividade. Alguma atividade no banco do vCenter está sendo executado e deixando o servidor em que está o banco lentíssimo, e inclusive caindo o serviço do vCenter Server.

    Obrigado pela ajuda.

    Amigo,

    Por favor, não me leve a mal, mas após estes comentários a melhor sugestão que posso passar para você é procurar um DBA que possa analisar seu ambiente e configurar adequadamente sua instância SQL, realizar as manutenções de rotina e aplicar as correções necessárias.

    Alguns "ajustes" precisam ter um cuidado, e responsabilidade, especial caso contrário não existiriam os DBA´s. 

    Não dá para ensinar (quase) tudo em um tópico de Fórum, espero que compreenda. Ao mesmo tempo, seria imprudência minha tentar orientar você (com tantas dúvidas) em poucas palavras.

    Desculpe pelo inconveniente.

    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"

    Durval,

    Concordo plenamente com as suas observações e considerações, além disso, nós como DBAs nem sempre acertamos, imagina quem não tem o mínimo conhecimento para realizar este tipo de procedimento que pode em algum momento ocasionar falhas ou perdas de dados.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 27 de maio de 2015 18:49
  • Windows Man,

    Executa esse script que o Durval te passou no SQL Server Management Studio.

    Forte Abraço!

    Alle Rodrigues

    quarta-feira, 27 de maio de 2015 22:43
  • Bom dia companheiros.

    Consegui resolver meu problema parcialmente. Eu rodei um Shrink na DataBase, apenas clicando com o botão direito na DataBase, apontando para Tasks, depois apontando para Shrink e clicando em DataBase. Consegui reduzir em aprox. 20 GB a Base, mas ela ainda está com mais de 200 GB, quando teria que estar com menos de 50 GB. O mais importante neste momento é que eu consegui novamente o acesso ao vCenter. Agora, creio que para diminuir o Banco, eu deva verificar o vCenter mesmo.

    Agradeço às dicas de todos aqui, e também entendo as preocupações citadas com relação à base de dados. De fato é preciso ter cuidado pra mexer em banco de dados. Inclusive, se possível, precisaria de um bom procedimento pra fazer o backup do banco, para ter aqui em meu ambiente.

    Abraços. 

    terça-feira, 9 de junho de 2015 15:38
  • Windows Man,

    Vamos por partes, ajudar todos nós sempre fazemos isso, ainda mais alguns que estão aqui que são MVPs, isso faz parte das nossas responsabilidades.

    Agora você dizer que precisa de um bom procedimento para realizar backup, as coisas não são bem assim, vou falar por mim, trabalho com SQL Server desde a versão 7, isso lá em 2000, desde então venho todos os dias estudando, estudando, me dedicando, buscando cada vez mais poder crescer na área de Banco de Dados, trabalho desde 1995 na área de Tecnologia. 

    Posso falar como um DBA que já passou por diversas situações profissionais e que hoje esta um pouco mais tranquilo e bem estabelicido profissionalmente, não pense que aqui nos fóruns vamos conseguir ajudar ou até mesmo fazer "serviço de graça" as coisas não podem ser vistas desta forma, é importante dizer que todos somos profissionais e que em muitos momentos e situações as respostas não serão obtidas assim tranquilamente.

    O Durval Ramos fez uma observação para você da necessidade de ter em sua empresa um profissional conhecedor de Banco de Dados, mas parece que você não deu muito valor a este observação, sendo assim, vou dizer, não veja nós DBAs como meros profissionais, e principalmente os fóruns não foram feitos para se obter informação a qualquer custo, ou melhor sem custo, por favor respeito isso.

    Até mais.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 9 de junho de 2015 23:06
  • Boa Tarde Windows Man,

    O Júnior e o Durval tem razão.

    Existem coisas que o barato sai caro. Não adianta ficar fazendo shrink sem analisar porque o banco está crescendo. Quais tabelas... Quando.... etc...

    Como já disseram, se não desejam treinar ninguém na empresa para conhecer o SQL Server, terão que pedir ajuda a outras que presta esse tipo de serviço pontualmente.


    Fabrício França Lima MCITP - SQL Server Database Administrator Trabalho com SQL Server desde 2006 Treinamento DBA ONLINE: http://www.fabriciolima.net/blog/cursos-online/treinamento-tarefas-do-dia-a-dia-de-um-dba-online/

    quarta-feira, 10 de junho de 2015 19:39
  • Windows Man,

    Vamos por partes, ajudar todos nós sempre fazemos isso, ainda mais alguns que estão aqui que são MVPs, isso faz parte das nossas responsabilidades.

    Agora você dizer que precisa de um bom procedimento para realizar backup, as coisas não são bem assim, vou falar por mim, trabalho com SQL Server desde a versão 7, isso lá em 2000, desde então venho todos os dias estudando, estudando, me dedicando, buscando cada vez mais poder crescer na área de Banco de Dados, trabalho desde 1995 na área de Tecnologia. 

    Posso falar como um DBA que já passou por diversas situações profissionais e que hoje esta um pouco mais tranquilo e bem estabelicido profissionalmente, não pense que aqui nos fóruns vamos conseguir ajudar ou até mesmo fazer "serviço de graça" as coisas não podem ser vistas desta forma, é importante dizer que todos somos profissionais e que em muitos momentos e situações as respostas não serão obtidas assim tranquilamente.

    O Durval Ramos fez uma observação para você da necessidade de ter em sua empresa um profissional conhecedor de Banco de Dados, mas parece que você não deu muito valor a este observação, sendo assim, vou dizer, não veja nós DBAs como meros profissionais, e principalmente os fóruns não foram feitos para se obter informação a qualquer custo, ou melhor sem custo, por favor respeito isso.

    Até mais.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    Galvão,

    Não me leve a mal, mas não entendi o porquê desse seu sermão. Foi totalmente desnecessário. Em nenhum momento menosprezei o conhecimento e o trabalho de vocês DBAs. E não desprezei a informação do Durval. Tem um DBA em meu ambiente, mas ele não conhece SQL Server. O que quer que eu faça? Que eu mande ele ir aprender SQL Server em 1 semana porque meu ambiente está fora? Ou sugere que eu intime meu chefe a contratar um DBA de SQL Server?

    Pra finalizar, acho fóruns muito importantes. O Technet então nem se fala. Considero uma ferramenta de trabalho. Agora não sou alienado imaginando que terei um curso de SQL Server aqui. Só perguntei se havia uma forma, que eu compreendesse, de se fazer backup do banco. Se não tem, Ok. Vou verificar outra forma de ter isso.

    O importante é que meu ambiente está no ar.

    Sem mais.

    quarta-feira, 10 de junho de 2015 19:50
  • Windows Man,

    Ok, perfeito, não dei certo, somente fiz uma observação, como também, não estou falando para você sugerir ao seu DBA que vá aprender SQL Server em uma semana pois isso é impossível.

    Fico feliz em saber que você reconhece o trabalho de um DBA, a importância dos fóruns e também que o seu ambiente esta no ar.

    Até mais.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 11 de junho de 2015 19:42
  • Galvão,

    O trabalho de vocês DBAs são tão importantes quanto o de um Administrador de rede, um programador, um Técnico de HelpDesk, etc. A empresa perde muita grana com um banco de dados fora.

    E, novamente, agradeço a atenção prestada.

    quinta-feira, 11 de junho de 2015 19:49