Preamble: In general it's a big no-no, but believe me that are rare cases when space is really needed. For example Express Edition is limited to 10GB. Imagine that you discovered that with a data type conversion (a blob column) you can free up significant amount of space. But after that the DB file still has the same size as we know, and the 10GB limit also didn't change magically. So some kind of SHRINK is needed. That was an example.

In my test environment I performed:


That did the trick (I know that that minimizes the free space, in real world I would leave free space). it took many-many hours to finish. As we know it's a single threaded process Strange behaviour DBCC Shrinkfile, "it works as a series of very small system transactions so there is nothing to rollback." - Paul Randal http://www.sqlservercentral.com/Forums/Topic241295-5-1.aspx. We also know that it messes up the index fragmentation big time http://www.mssqltips.com/sqlservertip/2055/issues-with-running-dbcc-shrinkfile-on-your-sql-server-data-files/ and I can confirm that. I didn't experience log file grow though described in http://www.karaszi.com/SQLServer/info_dont_shrink.asp

I issued some INDEX REBUILD and REORGANIZE and those finished within seconds.

My questions:

  1. What's the big deal about shrink if I can just fix the index fragmentation after the shrink within seconds? I don't understand. Here on DBA stackexchange the "shrink" topic (https://dba.stackexchange.com/tags/shrink/info) says "Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space." plus refers to another popular article about it. But index fragmentation can be fixed.
  2. Why I didn't experience any log file growth?
  3. What if REBUILD the index first after the space free-up operation. Can that substitute the first DBCC SHRINKFILE (DBFile, NOTRUNCATE) so I just need to DBCC SHRINKFILE (DBFile, TRUNCATEONLY)? I have a feeling that the two work on different logical level but I have to ask this.

Bottom line: I promise I won't do shrink regularly or anything. But this is a situation where a cap is hit and shrink is needed.

Answer to my 2nd question: I didn't experience transaction log file growth, because I was fiddling in a developer test environment. The transaction log file would grow in a production environment (assuming Full Recovery Model). You should mimic that in your testing environment too. The tr log file growth is actually bigger than the space you can free up inside the database file. In the end you not only want to shrink the database file, but also the transaction log (how and when to do that depends on the recovery model). Production environments I saw had so screwed up index fragmentation that it can be just better. My script reindexes every index which fragmentation level is above 30%.

To handle the growth, you may have to switch from full recovery to simple while you are doing the once-in-a-lifetime conversion. But that will break the backup chain.

Answer to my 3rd question: do the reindex/index rebuild after the DB file shrink, because the shrink messes up index fragmentation.

I haven't experimented how all of this affects query statistics.

Script for reindexing: see Example D of TechNet sys.dm_db_index_physical_stats (Transact-SQL). An article on incremental shrink, which will lead to other valuable content: http://www.mssqltips.com/sqlservertip/3178/incrementally-shrinking-a-large-sql-server-data-file-using-powershell

  • Are you trying to shrink the data file or Log file ?
    – Kin Shah
    Commented Jul 30, 2013 at 19:59
  • 2
    It honestly sounds to me like you're just asking permission to tempt fate. Fine by me - it's your data. I can't give you permission to do something unadvisable but I can also tell that you're more here for an argument than for actual feedback.
    – swasheck
    Commented Jul 31, 2013 at 0:20
  • On what? It seems like you're just having an argument for the sake of having an argument.
    – swasheck
    Commented Jul 31, 2013 at 1:26
  • Deleted my comment because I didn't realize it showed up in my VTC reason
    – swasheck
    Commented Jul 31, 2013 at 1:29
  • 1
    @swasheck never trolls. Agreed on the duplicate, and VtC.
    – Hannah Vernon
    Commented Nov 26, 2013 at 4:46

1 Answer 1


If you want to:

  1. ignore all of the warnings from very smart people with a lot of experience in SQL Server;
  2. assume that because in this case you didn't experience log growth and in this case fixing the fragmentation was quick and didn't cause any further growth, that will always be the case; and,
  3. not confirm that this grow/shrink/grow/shrink cycle has any impact on query times;

Go ahead. Since you say you will only be doing this in rare scenarios, I'm not sure what answer you're looking for here. Do you want a big thumbs-up that you can ignore everything you've read about it and roll the dice?

I will suggest at least one thing: hitting the 10GB limit in Express is going to happen if you have 10GB of data. You avoid that by removing data in the data file (or spreading your data across multiple databases). If you free up a bunch of space inside the data file, that space will be re-used (if you dropped a table, this will be automatic; if you deleted rows or changed column structure, you will need to rebuild - which will also take some space at least temporarily). So if you free up 5GB of data in the file, just because the file is 9GB does not mean that it will try to become 11GB if you add 2GB - it will reuse space inside the file until it can't find any more. It will be a much more expensive operation to shrink the file to 4GB, then force it to grow to accommodate the new data. This is like washing an already clean towel that you're about to use to wipe up a mess...

  • You are talking about cycles, I'm talking about a one-time event. I'm aware that the free space within the file will be reused. I'm expecting some advices about how to perform such operation, what am I missing maybe, etc.
    – Csaba Toth
    Commented Jul 30, 2013 at 20:28
  • Once the 10GB limit is reached, and you are in a replication scenario, you are stuck. Your car is stalled, you cannot synchronize any more.
    – Csaba Toth
    Commented Jul 30, 2013 at 20:29
  • 4
    @CsabaToth I suspect you're missing the point. When you reach 10GB of data (not 10GB of file) you are stuck. Once you have 10GB of data (not file) shrink will have no effect. Commented Jul 30, 2013 at 20:30
  • 5
    @Csaba Then please simplify and focus your question. Do we need a preamble, 40 links to articles most of us are already pretty intimate with, etc. Also, if Express is a subscriber and your publisher exceeds 10GB, then yep, you're stuck. Kill the subscription and fix the source, or move the subscriber to a real edition of SQL Server without the limitation. Commented Jul 30, 2013 at 20:31
  • 2
    To thrown in my 2 cents, I do occasionally shrink a data file but only when a large amount of one off data has been dumped into it then subsequently deleted resulting in a huge amount of free space. I'll agree that leaving the free space there won't cause any direct issues (except for drive space); however, if you need to backup/restore this same database to a smaller test/dev environment you may well be in a position where this is simply not possible. If you are never going to use the space again and are willing to pay the penalty of rebuilding your indexes, I do not see any problem.
    – PseudoToad
    Commented May 1, 2014 at 15:47

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