How to release large unused/allocated space

Hi,

I have 6 filegroups that contain over 30 gigs of data. I manage the transaction log space efficiently by using a script and 'shrink database'. But, the allocated unused space of the data files are very large, over 30 gigs.

The configuration is standard for all files:

- Automatically grow file by percent

- Maximum file size - Unrestricted

How do I release the unused space?

Thanks,

- gshaf

Hi Gshaf,

You can issue the DBCC SHRINKDATABASE statement with TRUNCATEONLY. This will shrink the database the maximum amount possible, and release the space back to windows. If you want to control how much space in the db left for growth, you'll need to issue DBCC SHRINKFILE and specify the amount (MB) of space you want left:

DBCC SHRINKFILE(MyDataFileNameHere, 15000)

The above command will attempt to shrink the datafile "MyDataFileNameHere" to 15GB.

Cheers,

Rob

sql