Shrink or not to shrink? VERY Large database.

Question

Shrink or not to shrink? VERY Large database.

SQLH

SSC Journeyman

Points: 76
More actions
June 28, 2008 at 8:02 pm

#72341

Hi,
I have a database that's for reading only. One of the tables that holds 453,161,282 rows of data. The indexes are in their own filegroup, about 8 indexes. Each index about 11GB except for clustered index.
I ran an ALTER INDEX command with the following options:
(FILLFACTOR = 99, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF, ONLINE=ON)
The indexes double in their files size to 22GB. I find this very weird, why would an index file size double on a reindex?
My only guess is because it recreated itself and kept the space the prior index had.
My questions:
1) What command or tool can I use to see the empty space in each index file?
2) If I find that there's 11GB of empty space, Should I shrink? What's the best way to shrink or recover the empty space without causing deframentation?
Thank you,
H

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

hyeng (6/28/2008)
2) If I find that there's 11GB of empty space, Should I shrink? What's the best way to shrink or recover the empty space without causing deframentation?

In general, no. It's just going to grow again, probably next time you reindex. If you shrink the DB, you will be undoing the work you did in defragging the indexes and you will probably leave them more fragmented than when you started.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Gail is correct. You need enough space to reindex, and if you shrink, you might undo all the efforts you've done in reindexing as a shrink might move things around.

Leave that amount of space there. It's not huge amount in today's world and if you need to reindex everything again, you'll need the space.

SQLH SSC Journeyman Points: 76 More actions · Answer 3

Thank you, for you replies. I understand that shrinking the database causes performance problems but this database has a lot of large tables. The current database size is of 900GB, available space is 316GB and both log file are about 1GB.

316GB seems like a waste of a lot of resources for a database that hardly gets new data and would hardly need to be re-indexed for the same reason.

Is there anyway to find and take away this available space?

What are my options? How can I explain to my manager that unused space?

Thanks,

H

barsuk SSChampion Points: 12280 More actions · Answer 4

Shrinking leads to Fragmentation. Also most likely within 3-6 months this space will re-populated by your DB. One of the main reasons to shrink space in your DB ( and prepare for a looong time) is when you need to restore your Prod DB in UAT/Dev environments and you don't have space avialable...