Shrink File by using empty file option

Question

Shrink File by using empty file option

glugingboulder

SSCommitted

Points: 1832
More actions
May 10, 2010 at 3:23 pm

#143053

Hi All,
I have a database which has been archived so half the data is now else where leaving 100gig of free space inside the datafile. I understand the shrink database is the devil and don't really want to do it but we are running low on drive space there will also be a daily archive job so the database will not grow any larger, seems a shame to waste that space.
One idea was to create another datafile in the same file group with the appropriate size and use the Empty file option to migrate the data over then drop the old datafile. My question is is it using exactly the same page by page processing method as a shrink db causing hideous file fragmentation or is it a better way to do things?

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

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

JeremyE SSCoach Points: 16121 More actions · Answer 1

It will still cause index fragmentation as the pages are moved between files. It seems like an extra step to me. I would just shrink the current file leaving enough free space to go back through and rebuild the fragmented indexes the shrink caused.

Leo.Miller SSChampion Points: 13048 More actions · Answer 2

If you shrink the file you'll have to do the index maintenance regardless of you you do it, sorry. The real question is what are you trying to do.

If this is a secondary (*.ndf) file and only has data in it, the approach you have given is a great way to move the file to another disk without having to take the database off line. If you use the "EMPTYFILE" option on the primary file (*.mdf) you will still have the system objects in the file, as these can't be moved without taking the db off line.

If all you want to do is shrink the file, then I don't recomment you use them "EMPTYFILE" option as you aren't gaining anything. I haven't tested with a large data file, but it may also be a little bit slower with the emptyfile option.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

glugingboulder SSCommitted Points: 1832 More actions · Answer 3

well i thought it maybe be quicker to use empty file as it would just be moving pages to a new space which is empty and splitting the IO operations onto another disk, read from disk X write to disk Y, if it was a shrink operation on the current datafile it would need to find the empty space within the current data file and move using IO's on the same drive, just thought it was a messy way to do it.

the objective is to shrink the database and span data files across multiple disks

some index fragmentation we can live.

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

You can span the data file across a couple disks, but you'll want to defragment the indexes once you're done and keep that size data file(s). The indexe fragmentation can cause a decent amount of performance loss, so if this database is being used with any load, you ought to just live with a little extra space in it.

glugingboulder SSCommitted Points: 1832 More actions · Answer 5

not sure what you mean by system objects left in the *.mdf

I can create a second mdf in the file-group and use emptyfile to move the contents to the new datafile then remove the old datafile, i have tested this and it works, the database is completely operational and doesn't require a outage, if the original data file isn't empty it wont let you remove it.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

glugingboulder - Monday, May 10, 2010 7:55 PM
not sure what you mean by system objects left in the *.mdfI can create a second mdf in the file-group and use emptyfile to move the contents to the new datafile then remove the old datafile, i have tested this and it works, the database is completely operational and doesn't require a outage, if the original data file isn't empty it wont let you remove it.

"It works" and "It works correctly" are two different things. You can't just leave the new file alone because it suffers all of the same problems that any use of DBCC SHRINKFILE would cause including the logical inversion of pages in indexes, which can cause huge performance problems. You will still need to do index maintenance as if you didn't move the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

glugingboulder - Monday, May 10, 2010 5:57 PM
...some index fragmentation we can live.

While I'll be the first to agree with the fact that the "logical fragmentation" doesn't matter as much as people would think during the normal life span of any index, it seriously matters after a DBCC SHRINKFILE. You WILL need to do "normal" index maintenance to ensure that you don't have massive performance problems due to the "index inversion" that DBCC SHRINKFILE can cause. I HAVE, many times, seen a shrinkfile cause a 500ms query suddenly take 2 hours to execute and actually have to be "killed".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)