Shrink em arquivo MDF

Question

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á.

Answer 1

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]

Answer 2

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;

Answer 3

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

Answer 4

Pessoal tudo bem?

Alguma idéia?

Obrigado.

Answer 5

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"

Answer 6

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.

Answer 7

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"

Answer 8

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.

Answer 9

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]

Answer 10

Windows Man,

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

Forte Abraço!

Alle Rodrigues

Answer 11

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. 

Answer 12

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]

Answer 13

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/

Answer 14

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.

Answer 15

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]

Answer 16

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.