resizing .ndf files to a smaller size than initial configuration

Question

Post reply

resizing .ndf files to a smaller size than initial configuration

kes 38417

Old Hand

Points: 368
More actions
March 9, 2011 at 2:35 am

#147426

Is there a way to resize .ndf files, i have 4 .ndf files in Misc and the system was set up with the growth for these file set to 10GB and they are now at 32 GB each, there is no data within these files but they will not shrink below this amount as this is the initial setup size.
I have tried "Alter Database" but this does not work due to the size being lower than its initial setup.
Any help would be appreciated

Viewing 7 posts - 1 through 7 (of 7 total)

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

Preethi S Raj SSCommitted Points: 1523 More actions · Answer 1

Did you try

DBCC SHRINKFILE (N'<logical file name of the .ndf file>' , <size in MB>) ?

<size in MB> - Specify the size you would like to shrink the file to. (cannot be less than 1 MB)

Example -

DBCC SHRINKFILE (N'Test1' , 1)

kes 38417 Old Hand Points: 368 More actions · Answer 2

Yeah have tried "Shrink file" but the initial conf was set to 32 GB so it wont shrink below 32GB

Preethi S Raj SSCommitted Points: 1523 More actions · Answer 3

I don't think that is true. For test purposes, create a database with Initial size (.ndf file size) as 10 MB and try to shrink it to 1 MB using DBCC shrinkfile.

Preethi S Raj SSCommitted Points: 1523 More actions · Answer 4

Also, check the unallocated space

by executing the statement

sp_SPACEUSED

Depending on this value, set the shrinkfile size

kes 38417 Old Hand Points: 368 More actions · Answer 5

kes 38417

Old Hand

Points: 368

March 9, 2011 at 6:26 am

#1296452

Hi

This is the script i use

dave_lumley SSC Enthusiast Points: 174 More actions · Answer 6

Here is what I used:

it may be worth running a checkpoint command / backup prior to running this so sql know the transaction log can be reduced

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE {db_to_shrink}

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE ({dbfile_to_shrink}, {target_file_size_mb});

GO

-- Reset the database recovery model.

ALTER DATABASE {db_to_shrink}

SET RECOVERY FULL;

GO