Best method to shrink a table?

Question

Post reply

Best method to shrink a table?

Vik Nat

Hall of Fame

Points: 3930

June 14, 2004 at 8:46 am

#108388

Hello All,

I just did some mass archiving and now I have some tables that need to be shrunk as the the free space did not go down. This is the result of sp_spaceused for the largest tables. My DB currently is around 40GB and looks like I should be able to trim it down by around 12GB.

Table_name	rows	reserved	data	index size	unused
table 1	1169601	9271440 KB	5469768 KB	3056600 KB	745072 KB
table 2	2005118	13308384 KB	10676376 KB	272464 KB	2359544 KB
table 3	10198130	6988680 KB	1579712 KB	282072 KB	5126896 KB
table 4	865020	1673592 KB	168504 KB	983552 KB	521536 KB
table 5	465179	976472 KB	29336 KB	748040 KB	199096 KB
table 6	7903055	6387744 KB	2605760 KB	568088 KB	3213896 KB
table 7	2100412	1600408 KB	359208 KB	979144 KB	262056 KB
table 8	1924215	527320 KB	129792 KB	180192 KB	217336 KB

So what is the best way to reclaim the unused space? Would reindexing the tables and then shrinking the db do the job?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Chiu SSCrazy Points: 2619 More actions · Answer 1

I don't know there is a way to shrink individual table. You may need shrink the DB.

Vik Nat Hall of Fame Points: 3930 More actions · Answer 2

So whats the best way to tackle this - would a reindexing followed by DB shrink free up this space or would it be better idea to drop and rebuild the indexes and then run the DB shrink.

Thanks

John Chiu SSCrazy Points: 2619 More actions · Answer 3

I'll run defrag first (include index) then DB shrink.

Vik Nat Hall of Fame Points: 3930 More actions · Answer 4

Ok thanks John. So Shall I run DBCC INDEXDEFRAG on all the above tables for all indexes, clustered and non-clustered followed by a DB shrink? Will that be enough to reclaim the unused space?

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

If you can run dbcc dbreindex, run it to your tables and then shrink the database. One of difference is that DBCC INDEXDEFRAG is online operation and DBCC DBREINDEX will block the user transactions if the transaction happens to access those tables.

Vik Nat Hall of Fame Points: 3930 More actions · Answer 6

Thanks Allen, I know that as I have been using dbcc dbreindex for years on SQL 7. We just upgraded to SQL 2000 and I learnt of Indexdefrag and it also appears to be better as I have a tough time maintaining the trans log when I run dbcc dbreindex.

So my plan is to run the dbcc indexdefrag on these tables followed by a db shrink.

Prashant Dhas SSC Rookie Points: 44 More actions · Answer 7

Dear Friends

DBCC DBREINDEX, INDEXDEFRAG, commands are using indexing the table to physically order the table. This will not reclaim the space which you need.

Prashant

Vik Nat Hall of Fame Points: 3930 More actions · Answer 8

I have reclaimed a lot of space using INDEXDEFRAG followed by a DB shrink as the INDEXDEFRAG will defrag the indexes and remove all unused pages. After this I run a DB shrink. What in your opinion is the best way to reclaim this space?

Thanks in advance,

Vik.

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 9

As you said, the INDEXDEFRAG does allow you to claim more space in the shrink db process. If you want to maximize your shrink and have a good maintenance window:

1. On extremely fragmented tables, dropping the primary key and recreating it will allow you to shrink the furthest as it automatically reorders the indexes. DBCC DBREINDEX will give you the second-best results on space and reordering of the indexes. Both are extremely expensive though and will cause some problems if you have a lot of users on. The INDEXDEFRAG is online and will not cause users problems. It does not reorder the indexes as efficiently though, especially on large tables with extemely fragmented indexes.

2. You should look at writing a script to shrink individual files so you have more control. Also, many times you can shrink a file multiple times to reclaim even more space.

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

Vik Nat Hall of Fame Points: 3930 More actions · Answer 10

Thank you all for your replies. I copied over the Database from Prod to test and ran the indexdefrag followed by the shrink. I did reclaim a lot of space. But when I run the sp_spaceused, I see that the unused space has increased a lot for the larger tables. I have also rebuild all the indexes of all the tables using DBCC DBREINDEX, but the results were the same as after I ran the INDEXDEFRAG. Any thoughts?

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 11

After running this, did you run DBCC SHRINKDB('database')?

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.