How To Recover Unused Space From Data File

Question

How To Recover Unused Space From Data File

YSLGuru

SSC-Insane

Points: 21661
More actions
January 10, 2013 at 11:20 am

#268612

PLATFORM
OS: Windows Server 2008R2 64 Bit
SQL SERVER: 2008R2 Ent Edit - 64 bit (10.50.1600.1)
We have a copy of our live DB restored to a test server. The DB consists of a singel data file and a single log file. The data file has a sizeable amount of unused space in it, space that I would like to recover and gove back to the OS. Since this is a testing copy of our DB we don't need a lot of room for it to grow.
Is there anyway to regain unsued space from a data file on SQL Server 2008 R2 other then shrinking the DB/file?
Thanks
Kindest Regards,
Just say No to Facebook!

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

DBCC ShrinkFile or DBCC ShrinkDatabase.

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

YSLGuru SSC-Insane Points: 21661 More actions · Answer 2

GilaMonster (1/10/2013)
DBCC ShrinkFile or DBCC ShrinkDatabase.

I was afraid of that. A 200GB+ DB can take a very long time (on a test/dev server) to shrink.

How about during the restore itself? Is it possible duing the restore to restore the DB so that it have a small amount of room to grow and not the full amount that the live DB does?

Theoretic Example:

If for example my DB is 250GB (249GB DB File & 1GB Log File) and the data file has 49GB of unused space is there a way during the restore to restore it so that the data file is 210 GB instead of the orginal 249GB?

Thanks

Kindest Regards,

Just say No to Facebook!

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

YSLGuru (1/10/2013)
Is it possible duing the restore to restore the DB so that it have a small amount of room to grow and not the full amount that the live DB does?

No.

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

YSLGuru SSC-Insane Points: 21661 More actions · Answer 4

GilaMonster (1/10/2013)
YSLGuru (1/10/2013)
Is it possible duing the restore to restore the DB so that it have a small amount of room to grow and not the full amount that the live DB does?
No.

Oh well. Thanks

Kindest Regards,

Just say No to Facebook!