<p>I have ms sql databases that grow very large. Upon examination I find that there is a bunch of unused space in certain tables. I don't do many physical deletes, so I don't think that its just deleted records. DBCC SHRINK doesn't make the file smaller. But, if I dump the table to a new, empty database, the size goes down about 80%. Instead of the 7gb I have in this table in the current database, I end up with about 1.5gb in the fresh database. Its as if sql server is allocating too much memory. Anyone encountered this before? I'd like to be able to shrink the table by removing unused allocated space without having to create a whole new database.</p>

<p>Additional information:</p>

<p>Full recovery model used. I'll try rebuilding the indexes, i think its been a while. ldf's are shrunk daily using some wacky stored proc that truncates them.</p>

[{'answer_id': 46453, 'author': 'Darren Kopp', 'author_id': 77, 'author_profile': 'https://Stackoverflow.com/users/77', 'pm_score': 0, 'selected': False, 'text': "<p>In the options, you can specify how much you want to grow by. By default i believe it's 10%, so given a 200MB database, when you fill your last page, it will allocate another 20MB of page space. At 7GB it would allocate 700MB.</p>\n\n<p>I don't know exactly where you can modify it after you create a db, but i know it has it when you create the db. a little google work will most likely reveal the answer to you.</p>\n\n<p>NOTE: my answer is not how to fix it, but maybe how to prevent / explain why you might see all this unallocated space.</p>\n"}, {'answer_id': 46462, 'author': 'Craig', 'author_id': 2894, 'author_profile': 'https://Stackoverflow.com/users/2894', 'pm_score': 2, 'selected': False, 'text': '<p>I have found that if you do not take care to backup your transistion log file (the LDF) you will get something like this behavior.  I can not stress enough the importance of having good backup "hygiene".  Not only will it save your bacon if something goes wrong but I will also help maintain a nice tight database.</p>\n'}, {'answer_id': 46469, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 1, 'selected': False, 'text': "<blockquote>\n  <p>I don't do many physical deletes</p>\n</blockquote>\n\n<p>what about updates of the table, what is the fragmentation level. run DBCC SHOWCONTIG, then rebuild the index if it is highly fragmented. After that do a BACKUP LOG  WITH  TRUNCATE_ONLY followed by a SHRINK command</p>\n"}, {'answer_id': 116017, 'author': 'Rob', 'author_id': 6201, 'author_profile': 'https://Stackoverflow.com/users/6201', 'pm_score': 0, 'selected': False, 'text': "<p>This has worked for me in the past</p>\n\n<pre><code>USE [DBNAME]\nGO\n\nDBCC SHRINKFILE (N'FILENAME' , 0, TRUNCATEONLY)\n\nGO\n</code></pre>\n"}, {'answer_id': 116369, 'author': 'Paul G', 'author_id': 162, 'author_profile': 'https://Stackoverflow.com/users/162', 'pm_score': 0, 'selected': False, 'text': "<p>I had a similar problem once and I believe that I found that reindex/shrinking didn't reclaim all of the unused space if there was no clustered index on a given table.</p>\n"}, {'answer_id': 152738, 'author': 'Josef', 'author_id': 5581, 'author_profile': 'https://Stackoverflow.com/users/5581', 'pm_score': 0, 'selected': False, 'text': "<p>It is possible that the table was built with padding turned on for the index. The reason that people build a padded index is to prevent page splits.</p>\n\n<p>Right click on the table in SQL Manager and select SCRIPT TABLE. Then look to see if <code>PAD_INDEX=OFF</code>. If <code>PAD_INDEX</code> is in use, that's probably where the table is taking up space.</p>\n"}]
['https://Stackoverflow.com/questions/46439', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/300/']