Timeline for SHRINKFILE best practices and experience
Current License: CC BY-SA 3.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 1, 2014 at 15:47 | comment | added | PseudoToad | 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. | |
| Nov 26, 2013 at 19:07 | vote | accept | Csaba Toth | ||
| Jul 30, 2013 at 23:10 | comment | added | Csaba Toth | @AaronBertrand I added as much info as possible to spare some round trips about what a big no-no shrink is. I won't avoid smarter people's advice and I won't even think about using shrink anywhere else except the special case which inspired this post. At the same time I was wondering about others experience and advice. I'm interested in info from who have done such operation, probably I'm not alone. | |
| Jul 30, 2013 at 23:06 | comment | added | Csaba Toth | @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. | |
| Jul 30, 2013 at 20:31 | comment | added | Aaron Bertrand | @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. | |
| Jul 30, 2013 at 20:30 | comment | added | Mark Storey-Smith | @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. | |
| Jul 30, 2013 at 20:29 | comment | added | Csaba Toth | 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. | |
| Jul 30, 2013 at 20:28 | comment | added | Csaba Toth | 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. | |
| Jul 30, 2013 at 20:27 | history | edited | Aaron Bertrand | CC BY-SA 3.0 |
added 179 characters in body
|
| Jul 30, 2013 at 20:18 | history | answered | Aaron Bertrand | CC BY-SA 3.0 |