EMPTYFILE

Question

Post reply

EMPTYFILE

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
April 30, 2018 at 8:11 pm

#73756

Comments posted to this topic are about the item EMPTYFILE

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

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

HappyGeek SSCoach Points: 18726 More actions · Answer 1

HappyGeek

SSCoach

Points: 18726

April 30, 2018 at 9:33 pm

#1988720

Good question thanks Steve.

...

paul s-306273 SSChampion Points: 10727 More actions · Answer 2

paul s-306273

SSChampion

Points: 10727

May 1, 2018 at 3:43 am

#1988738

Interesting QOTD.

Thanks Steve.

Stewart "Arturius" Campbell SSC Guru Points: 72486 More actions · Answer 3

Really interesting, thanks Steve - Never used this, but can think of a few instances where it might come in handy

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

tonykindermann Valued Member Points: 59 More actions · Answer 4

Hi Steve,
Great question!!

But from my experience and test on SQL2017... In the end of the question you put "If I change the file size, will new data..." so if you execute an alter database modify file (name=logicalName, size = 10GB) after the emptyfile this file will be available again to be loaded with data.

As I don't have an application inserting data on my test DBs I tested this behavior with rebuild table using ALTER INDEX ALL ON [blahblah] REBUILD WITH ( FILLFACTOR = 95 );
My test results are that the datafile was not used right after the EMPTYFILE... but after the increase the file gets used again normally.

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 5

tonykindermann - Wednesday, May 2, 2018 6:01 AM
Hi Steve,
Great question!!
But from my experience and test on SQL2017... In the end of the question you put "If I change the file size, will new data..." so if you execute an alter database modify file (name=logicalName, size = 10GB) after the emptyfile this file will be available again to be loaded with data.
As I don't have an application inserting data on my test DBs I tested this behavior with rebuild table using ALTER INDEX ALL ON [blahblah] REBUILD WITH ( FILLFACTOR = 95 );
My test results are that the datafile was not used right after the EMPTYFILE... but after the increase the file gets used again normally.

That's true!
... and the files are filled proportionally

andrew.ing SSC Eights! Points: 853 More actions · Answer 6

The Microsoft article uses a strangely anthropomorphic explanation, "Emptyfile assures you that no new data will be added to the file". I'm happy to take DBCC's assurances any day but odd that the author didn't use clearer and simpler description in Steve's question - "... it marks the file as read only...".

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 7

tonykindermann - Wednesday, May 2, 2018 6:01 AM
Hi Steve,
Great question!!
But from my experience and test on SQL2017... In the end of the question you put "If I change the file size, will new data..." so if you execute an alter database modify file (name=logicalName, size = 10GB) after the emptyfile this file will be available again to be loaded with data.
As I don't have an application inserting data on my test DBs I tested this behavior with rebuild table using ALTER INDEX ALL ON [blahblah] REBUILD WITH ( FILLFACTOR = 95 );
My test results are that the datafile was not used right after the EMPTYFILE... but after the increase the file gets used again normally.

I suspect the ALTER FILE undoes the readonly flag that's set. This appears to work on SQL 2016. I've edited the question to better show this and then awarded points back.