Sql-server – Should I shrink this SQL Server data file

shrinksql server

I have read many articles describing how shrinking data files causes terrible things to happen floods, famine, fragmentation, and general mayhem.

Here is the cliff notes of my situation:

  1. SSAS company with a central logging database for all client errors.
  2. Recent code roll created a new bug exponentially increasing the size of the database.
  3. Bug is fixed and the useless records have been removed, approximately 70 million.
  4. DB used space is now down to a normal level leaving 200+ gig of white space.

I am unable to find any posts or articles describing what I will gain from reclaiming this space.

Other than backup times increasing, are there ANY negative effects from a significant amount of unused space in a data file?

Please note the following in case it may help give a more informed answer:

  1. The drive is not in danger of filling up
  2. Space is not needed else where on the SAN
  3. Backup times have double in duration (both full and differential)
  4. Our DR SAN replication has slowed slightly.

Best Answer

Presuming you are sure the database is not going to need the 200+ GB of extra space it is currently consuming, I would recommend performing a shrink without reorganizing pages, using TRUNCATEONLY:

DBCC SHRINKFILE (N'<file name>' , 0, TRUNCATEONLY);

TRUNCATEONLY simply truncates the last part of the file without moving rows inside the data file. This is important since it will prevent logical fragmentation of tables inside the data file. If you perform the shrink without specifying TRUNCATEONLY SQL Server will move pages around inside the file to get the smallest possible file size, thereby creating anywhere from "some" to "a lot" of fragmentation, which may adversely affect performance. Read more about this at the Books Online page for DBCC SHRINKFILE. Thanks to @Zane for the suggestion to include these details in my answer.

Once you have performed the shrink, you may want to ensure the file growth parameters are set correctly. For instance, ensure the file growth is not percentage based; use a number of MBs that makes sense for your expected growth rate.