How to reclaim the space without shrinking the database

Question

How to reclaim the space without shrinking the database

Narine M

SSCrazy

Points: 2672
More actions
July 28, 2016 at 3:03 pm

#312137

Hi all,
In our production database we have deleted a bulk of rows in a table, total size is 300 GB and after deleting the rows we got 200 GB free. Now want to reclaim the space to OS without shrinking the database.
We tried to shrink the data files but it takes more time, i.e for 5 GB it takes around 5 hours. Its hard to reclaim the space.
Is there any possibilities to reclaim the space to OS. Kindly help on this.
Thanks in advance
NM

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

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

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 1

How are you doing the shrink? You could try DBCC SHRINKFILE with TRUNCATEONLY. That will release space after the last allocated extent. It should be faster. The problem is if you have a lot of data deleted and the pages are not reorganized you might not get the space savings you realize.

This is probably something that will either need to be scheduled during a maintenance window with authorized downtime or look for another solution, like growing storage of the volume data files are stored on.

Joie Andrew
"Since 1982"

Narine M SSCrazy Points: 2672 More actions · Answer 2

Thank you joie, already tried with the DBCC command only.It is taking hours to reclaim.

Is there any other way other than shrinking???(It is a production database).

Stuart Davies SSCoach Points: 19505 More actions · Answer 3

Why do you need to reclaim the space? If it has grown to the current size, there is a good chance it will grow again. It would be a better option in my mind, to explore how to get more disk space than shrinking the database.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

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

naren.ece2012 (7/28/2016)
Is there any other way other than shrinking???(It is a production database).

No. Reclaiming space = shrinking data files.

You could just leave it though. Free space in the data file doesn't cause any harm.

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