3

This question already has an answer here:

Everything I've heard (including from Microsoft) says you should never shrink a database. Yet a software vendor (Symantec) is telling us to do DBCC SHRINKFILE on the SQL 2012 databases and logs related to a product of theirs called Enterprise Vault.

Should I follow the recommendation of Symantec and shrink or the recommendation of Microsoft and everyone else to not shrink?

marked as duplicate by RolandoMySQLDBA, mustaccio, Vérace, Derek Downey, Marian Aug 17 '15 at 18:12

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

5

What you are saying is right. The database should not be shrinked. However there are some exceptions.

Let's say you move a lot of historical data out of your DB and it goes down from 100GB to 10GB. You may want to reduce it back to 10 or 20 or 50GB.

The thing to remember when you decide to go on with a shrink is that data (clustered and non clustered indexes) will get (possibly highly) fragmented. By data in indexes, I mean that rows won't be ordered by the indexed columns and stored continuously on continuous pages anymore. The server will have to jump through pages to read continuous rows (=> more IO).

Therefore, after a shrink you should defragment or rebuild your indexes. Choose for each index if it should be rebuilt or defragmented based on their fragmentation percentages. You probably already have a maintenance plan taking care of this so you just have to run it again right after the shrink.

If your need their support, you might have to make them happy... The guy asking you to do it probably know nothing about SQL Server and is just following a written procedure. In that case, you may proceed with the shrink and then update the indexes.

Note that I don't know much about Enterprise Vault.

  • As a note, while rebuilding indexes is the correct thing to do after a shrink, this will cause your database to grow. – Bob Klimes Aug 14 '15 at 16:52
  • @Julien You wrote data would get fragmented but this is not technically correct. Shrinking causes logical fragmentation which means the order of clustering key does not matches the order in which data pages are arranged there is no data fragmentation as such – Shanky Aug 15 '15 at 4:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.