Reclaiming Free Space in Database

Question

Reclaiming Free Space in Database

wak_no1

SSCrazy

Points: 2400
More actions
July 11, 2014 at 8:07 am

#287807

Hi
Having just archived quite a bit of data from the main Production DB, I now have around 15% free, reclaimable space sat in the data file.
I'm reluctant to run DBCC SHRINKFILE as that apparently causes a lot of Index fragmentation which will cause issues for performance - how else can the space be allocated back to the OS?

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

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

J.Faehrmann SSC Eights! Points: 957 More actions · Answer 1

Good that you are reluctant. If you don't really need to shrink, don't shrink! 15% does not sound like much. 🙂

If you really need to shrink and the database will never again grow that big, you can do so and try to clean up fragmentation mess afterwards.

There is no other way I know of.

sql-lover SSCoach Points: 18530 More actions · Answer 2

wak_no1 (7/11/2014)
Hi
Having just archive quite a bit of data from the main Production DB, I now have around 15% free, reclaimable space sat in the data file.
I reluctant to run DBCC SHRINKFILE as that apparently causes a lot of Index fragmentation which will cause issues for performance - how else can the space be allocated back to the OS?

You do not mention how big the database is, so we don't know how much space 15% is. 15% of a 2TB database is important space. 15% of a 500MB database, not so much.

Having said that, I personally leave between 10 and 15% internal free space in my databases and I'm ok with that. This allows organic growth and avoid unexpected expansion, which degrades performance.

Regarding your question, you can't reclaim that space other way, you need to shrink it. If the database is too big, you can shrink in small chunks and once done, you defrag Indexes. All that should be performed during off peak hours or during a maintenance window.

wak_no1 SSCrazy Points: 2400 More actions · Answer 3

Thanks for the replies.

The question was more around if there was any other way to release the unused space back to the OS apart from DBCC SHRINKFILE, as there isn't, that's fine. I can leave the free space for growth.

victor.girling 17919 SSC Veteran Points: 207 More actions · Answer 4

You could add a new filegroup set the size you require. Then rebuild the index on the new filegroup (ON <<new filegroup>>) using ONLINE = ON, DROP_EXISTING = ON. Then remove the old filegroup.

If the index is big enough to fill your log file on rebuild then set recovery to BULK_LOGGED. Read up first (if you don't know) on what that means in terms of recovery.