June 11, 2014 at 8:31 am
Hi All,
I have a production database which contains a table with binary data (called binary). I need to use this database to create a copy for the test environment. The database has 2 data files in the same primary group. One is 14GB and the other is 312GB.
At some point in the past the binary table reduced in size by renaming it to binary_old and creating a new table with the required records. The original table was then dropped. This was done in order to remove a corruption. This has caused a big increase in size.
What I want to achieve is a single file with a reduction in size (The database is 50% free space)
I have truncated the binary table and have attempted to shrink the database but I have not been successful.
My first attempt was to shrink the second 14GB file from 10GB to 9.5GB. This resulted in the file growing to 14GB rather than shrinking it. It has no free space within it. Can you please advise on how I can reduce this database in size.
Mark
June 11, 2014 at 11:52 am
You can't shrink file that has no empty space. Shrinking just releases empty space from files to OS.
If you want to get rid of a smaller file:
1. Script out all indexes that in Primary FG.
2. Create another FG with its file.
3. Recreate indexes on another FG.
4. If you have heaps in primary, create clustered indexes on another FG.
5. Shrink 14 GB file.
6. Shrink it like this: DBCC shrinkfile('name', emptyfile)
7. Drop 14 GB file.
8. Recreate all indexes on Primary.
9. For those tables that were originally heaps, drop clustered indexes.
June 11, 2014 at 11:54 am
You need to run this...
DBCC Shrinkfile(filename2, emptyfile)
... where filename2 is the secondary database file that you created.
This command will basically move whatever data you still have there to the other file. Once completed, then you can run a regular ALTER DATABASE command to remove filename2, the one that you just emptied.
June 11, 2014 at 12:28 pm
SQL Guy 1 (6/11/2014)
You can't shrink file that has no empty space. Shrinking just releases empty space from files to OS.If you want to get rid of a smaller file:
1. Script out all indexes that in Primary FG.
2. Create another FG with its file.
3. Recreate indexes on another FG.
4. If you have heaps in primary, create clustered indexes on another FG.
5. Shrink 14 GB file.
6. Shrink it like this: DBCC shrinkfile('name', emptyfile)
7. Drop 14 GB file.
8. Recreate all indexes on Primary.
9. For those tables that were originally heaps, drop clustered indexes.
I do not think that it is necessary to move all Indexes in order to empty the smaller file. In other words, jumping into step #6 should be enough.
June 11, 2014 at 1:32 pm
It will be much longer. Shrink with emptyfile pushes data page-by-page (or extent-by-extent) from one file to another, while rebuilding whole index is faster. Compare when you alter index with rebuild or reorg.
And another point is that the user will not see intermediate result for a long time, so he may conclude that shrinking will never finish and make decision to cancel all process.
June 11, 2014 at 1:44 pm
SQL Guy 1 (6/11/2014)
It will be much longer. Shrink with emptyfile pushes data page-by-page (or extent-by-extent) from one file to another, while rebuilding whole index is faster. Compare when you alter index with rebuild or reorg.And another point is that the user will not see intermediate result for a long time, so he may conclude that shrinking will never finish and make decision to cancel all process.
I disagree. Just dropping and recreating the Indexes will incur in so much I/O and locking that will make the app. unusable.
In my opinion, performance should be similar but dropping the Indexes and moving objects are extra steps that may not be needed.
OP,
Can you please run DBCC SHOWFILESTATS?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply