How to shrink data file in sql server 2005

Question

How to shrink data file in sql server 2005

solomon.jernas

Hall of Fame

Points: 3680
More actions
August 10, 2012 at 12:06 am

#262548

I want shirnk mdf file , it is around 450 GB . What command exactly use?
Please any one suggest to me
It is production database..
Thanks
Jerry

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

First off I will jump in and say that shrinking files is an expensive and performance impacting operation, as you will need to rebuild all indexes after shrinking the file.

It should only be done in times of extreme disk preasure and where you cannot add additional storage to resolve disk space problems.

But

First off find out if there is free space in the file to actually shrink it.

SELECT

a.FILEID,

[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)),

[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, 'SpaceUsed') / 128.000, 2)),

[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128.000, 2)),

a.NAME, a.FILENAME

FROM

dbo.sysfiles a

Then calculate the space needed to rebuild your biggest index.

If the space that will be reclaimed is bigger than the space required to rebuild your index, then you can shrink, using the DBCC SHRINKFILE command.

Then rebuild all your indexes.

durai nagarajan SSCoach Points: 15775 More actions · Answer 2

As antony said it will scater your data and indxes has to be rebuild and most important it will grw again on most of the cases.

Kindly dont think that shrink will do a zip the data and give free space.

See the free space in the DB and act accodingly.

Regards
Durai Nagarajan

solomon.jernas Hall of Fame Points: 3680 More actions · Answer 3

I checked, it has 160 GB free space availlable. How much need to shrink file?

durai nagarajan SSCoach Points: 15775 More actions · Answer 4

now what is the autogrowth option set to?

Regards
Durai Nagarajan

solomon.jernas Hall of Fame Points: 3680 More actions · Answer 5

solomon.jernas

Hall of Fame

Points: 3680

August 10, 2012 at 4:38 am

#1524312

512 MB ,Unrestricted growth...

durai nagarajan SSCoach Points: 15775 More actions · Answer 6

with 160 GB free space have you recently deleted/ moved any data from tables.

with big DB kindly shrink and reindex the db and update stastics during the non production time.

if you have any log shipping scheduled you have to start the full back up and restore and then start the log shipping process again. you have to take full backup again to have a proper log backup flow also to happen.

ensure the above things.

Regards
Durai Nagarajan

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 7

Its not recommended to shrink the files as its cause fragmentation as already posted by experts earlier

You can use DBCC SHRINKFILE to achieve the same

Monte Kottman Ten Centuries Points: 1385 More actions · Answer 8

I think there is a bit of confusion between Shrink Database and Shrink Files.

Shrink Database reorganizes the tables, indexes et al., and is VERY BAD for organization as well as consuming system resources while it does.

Shrink File truncates the available free space and does not cause a bottleneck.

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

Monte Kottman (8/10/2012)
I think there is a bit of confusion between Shrink Database and Shrink Files.
Shrink Database reorganizes the tables, indexes et al., and is VERY BAD for organization as well as consuming system resources while it does.
Shrink File truncates the available free space and does not cause a bottleneck.

That's not correct I'm afraid.

All ShrinkDatabase does is run shrinkfile on all files. Shrinkdatabase is worse because it shrinks log and data to the same degree, but that doesn't mean shrinkfile is harmless and doesn't cause fragmentation. Whether the commands cause fragmentation depend on the options they're called with.

If the truncateonly option is used (on data files only, it's not valid on log files) then that's safe, it doesn't move pages around and hence doesn't cause fragmentation. If that option is not there and a target size is specified, then shrinkfile/shrinkDB moves pages around, causing fragmentation.

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

durai nagarajan SSCoach Points: 15775 More actions · Answer 10

GilaMonster (8/10/2012)
If the truncateonly option is used (on data files only, it's not valid on log files) then that's safe

.

good explaination, i was confused whether this will be work properly or not , thanks.

Regards
Durai Nagarajan