SHRINKFILE

Question

Post reply

SHRINKFILE

sudhakara

SSCrazy Eights

Points: 9425
More actions
August 18, 2010 at 7:28 am

#140322

Hi,
Can anyone explain how DBCC SHRINKFILE(2, EMPTYFILE) works in sql server 2000 ?
Thanks in Advance.

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

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

have you googled about shrinkfile/shrinkdatabase and not able to find your answer there?

I think in forum we genrally post the live example error not the information or knowledge upgrade article.

----------
Ashish

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 2

Is there something confusing about this: http://msdn.microsoft.com/en-us/library/aa258824%28v=SQL.80%29.aspx

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 3

Hi,

Yes. I got the information from MSDN. i knew that it will move the data's from spcified file in command to another mdf or ndf files.

what i want know is how internally it works while shrinking ?

Thanks

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 4

I believe that data is moved to other files in the filegroup, round robin style, and then the file is emptied.

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 5

Steve Jones - Editor (8/18/2010)
I believe that data is moved to other files in the filegroup, round robin style, and then the file is emptied.

Hi,

Thanks for your reply. My database size is 187 GB. I was trying to shrink the database files but i could not shrink the database size after taking the backup of the databases. In the database its shows the unallocated space as 103GB. Will shrinkfile with emptyfile option help to reduce the size of the database ?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

sudhakara (8/18/2010)
Steve Jones - Editor (8/18/2010)
I believe that data is moved to other files in the filegroup, round robin style, and then the file is emptied.
Thanks for your reply. My database size is 187 GB. I was trying to shrink the database files but i could not shrink the database size after taking the backup of the databases. In the database its shows the unallocated space as 103GB. Will shrinkfile with emptyfile option help to reduce the size of the database ?

I would use EMPTYFILE option only if I'm planning to get rid of the specific datafile, how many datafiles do you have in that particular filegroup?

Remember that once empty SQL Server would not allow to put data anymore in the particular datafile.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 7

sudhakara

SSCrazy Eights

Points: 9425

August 18, 2010 at 11:34 pm

#1208895

Hi,

I have two data file's in the same file group.

crazy4sql SSCoach Points: 19590 More actions · Answer 8

if you doing the logfile shrink then you can get additional information by :-

dbbc loginfo(dbname).

Status 2 means in use and 0 means free to reuse or available for shrink.

----------
Ashish

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

@sudhakara,

If you are running short of space on disk , then you should go for shrink. Shrinking data file should be done with very intelligently, for genuine cause as it will induce data fragmantation and you need to work on index rebuild which is again need disk space.

see below comments

•Say you have a database with 50GB used space (data and indexes pages), and the shrink will shuffle 40 GB towards the beginning of the file. The log file will for this shrink operation need 40GB, likely auto-grow to that size (if you don't have 40GB free space in the log file already). The following log backup wll be 40GB in size, plus the "regular" log records. This doesn't seem to happen if the database is in simple recovery mode, possibly beacuse CHECKPOINT will prune the log regurarly during the shrink.

•Moving datapages around will fragment your database.
Say you rebuild your indexes (which will requires free space in the database), and then shrink the database. The shrink will essentially undo the index rebuild, leaving you with fragmented indexes. Don't believe me? This is easy to test for yourself.
What if you do it the other way around, shrink first, then rebuild? Well, the rebuld need free space in the database for the largest index that you rebuild, and it is likely you have a large table with a clustered index. A friend of mine had a 4GB used space db, where almost all space was one 4GB table. He did a shrink and then rebuild, where the re-build immediately "bumped up" the db size to 8GB.

•Heavy shrinking and growing of database files will fragment your file system, which will further hurt performance.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

crazy4sql SSCoach Points: 19590 More actions · Answer 10

ashish.kuriyal (8/18/2010)
have you googled about shrinkfile/shrinkdatabase and not able to find your answer there?

and here I found one of the good article...

http://www.mssqltips.com/tip.asp?tip=2097

----------
Ashish