EMPTYFILE

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720952

    Comments posted to this topic are about the item EMPTYFILE

  • HappyGeek

    SSCoach

    Points: 18684

    Good question thanks Steve.

    ...

  • paul s-306273

    SSChampion

    Points: 10615

    Interesting QOTD.

    Thanks Steve.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71915

    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

    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: 22010

    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: 847

    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: 720952

    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.

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

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