Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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:

DBCC SHRINKFILE (DBFile, NOTRUNCATE)
DBCC SHRINKFILE (DBFile, TRUNCATEONLY)

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 (http://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

share|improve this question
    
Are you trying to shrink the data file or Log file ? –  Kin Jul 30 '13 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 Jul 31 '13 at 0:20
    
On what? It seems like you're just having an argument for the sake of having an argument. –  swasheck Jul 31 '13 at 1:26
    
Deleted my comment because I didn't realize it showed up in my VTC reason –  swasheck Jul 31 '13 at 1:29
1  
@swasheck never trolls. Agreed on the duplicate, and VtC. –  Max Vernon Nov 26 '13 at 4:46
show 1 more comment

marked as duplicate by Mike Walsh, RolandoMySQLDBA, Paul White, Kin, Max Vernon Nov 26 '13 at 4:46

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.

1 Answer

up vote 11 down vote accepted

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

share|improve this answer
    
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 Jul 30 '13 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 Jul 30 '13 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. –  Mark Storey-Smith Jul 30 '13 at 20:30
3  
@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. –  Aaron Bertrand Jul 30 '13 at 20:31
    
@MarkStorey-Smith I figured that I can free up a ton of space with lossless conversion of blob data, which actually causes the bloat itself. Now it's a good question if you can synchronize that down to the client once one of them capped the limit. Just one of them capped but the others are before, so there's a chance something can be done at least to save the remaining ones. This would be another topic about merge replication and data size limit. –  Csaba Toth Jul 30 '13 at 23:06
show 1 more comment

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