Creating a DB

  • Nusc

    SSCommitted

    Points: 1618

    Hi there,

    when creating a DB with

    Auto close= False

    Auto shrink= True

    Database read only= True

    with a memory optimized data file, I receive the following message:

    4c68942e2e43792fcb33d7031382a0403c412362

     

    Similarly,

    Auto close= False

    Auto shrink= True

    Database read only= True

    why one cannot create the DB with these settings?

    I haven't been able to find a clear explanation.

  • pietlinden

    SSC Guru

    Points: 62804

    AutoShrink = True?

    No. Just No.

  • Nusc

    SSCommitted

    Points: 1618

    Can you explain?

     

    https://www.mssqltips.com/sqlservertip/1481/auto-grow-auto-shrink-and-physical-file-fragmentation-in-sql-server/

     

    "Auto shrink is a database option that is used to conserve disk space. It creates a thread that runs periodically to detect when the database has more than 25% free disk space allocated to it. The database will be shrunk leaving it with 25% free disk space while the rest of the previously allocated disk space is released back to the Windows operating system. "

     

    I was able to create the DB with this setting as true but why do you consider this an issue if it was a test db?

    The issue I want to understand is the conflict between auto close and the memory optimized file.

     

     

  • Jeff Moden

    SSC Guru

    Points: 996655

    Post withdrawn.

     

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeffrey Williams

    SSC Guru

    Points: 88547

    I have no idea why Microsoft has kept that option in the product - auto shrink will cause massive fragmentation of the database every time it shrinks.  It is just a bad option that has no benefits...there should be no reason to shrink a database on a normal basis.

    As for why you cannot use auto close - I think that is obvious.  If you enable memory optimized tables - they must remain in memory and auto closing the database forces them out of memory, so you cannot use that option in conjunction with memory optimized tables.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996655

    Jeffrey Williams wrote:

    I have no idea why Microsoft has kept that option in the product - auto shrink will cause massive fragmentation of the database every time it shrinks.  It is just a bad option that has no benefits...there should be no reason to shrink a database on a normal basis.

    As for why you cannot use auto close - I think that is obvious.  If you enable memory optimized tables - they must remain in memory and auto closing the database forces them out of memory, so you cannot use that option in conjunction with memory optimized tables.

    If you look carefully at the original post, he's setting Auto_Close to 'False'... it shouldn't be causing an error. 🙂

    Totally agreed on your stance for Auto_Shrink... especially since the database is also being set to Read_Only.  There shouldn't be a need even if it was a good idea (and I agree it's not).  Which brings us to a different subject...

    Why would anyone create a new, empty, database as Read_Only?  It would remain empty because you couldn't write to it unless you changed Read_Only to 'False' at least for an initial load.

     

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeffrey Williams

    SSC Guru

    Points: 88547

    Jeff Moden wrote:

    Jeffrey Williams wrote:

    I have no idea why Microsoft has kept that option in the product - auto shrink will cause massive fragmentation of the database every time it shrinks.  It is just a bad option that has no benefits...there should be no reason to shrink a database on a normal basis.

    As for why you cannot use auto close - I think that is obvious.  If you enable memory optimized tables - they must remain in memory and auto closing the database forces them out of memory, so you cannot use that option in conjunction with memory optimized tables.

    If you look carefully at the original post, he's setting Auto_Close to 'False'... it shouldn't be causing an error. 🙂

    Totally agreed on your stance for Auto_Shrink... especially since the database is also being set to Read_Only.  There shouldn't be a need even if it was a good idea (and I agree it's not).  Which brings us to a different subject...

    Why would anyone create a new, empty, database as Read_Only?  It would remain empty because you couldn't write to it unless you changed Read_Only to 'False' at least for an initial load.

    I think his question is on *why* you cannot specify the parameter 'Auto_Close' and use a memory optimized data file.  I think that is pretty obvious...you cannot specify that option because the memory has to be allocated for that file when the database starts up and you really want that memory to be contiguous and locked.  Auto closing the database would force the memory to be released - and then reallocated when the database is next opened - and released - and opened, etc...

    To prevent that - they just don't allow the option to even be specified on the create statement...which makes sense.

    As for the read_only - I have to admit I did not even look at that but I totally agree it doesn't make sense.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996655

    Jeffrey Williams wrote:

    Jeff Moden wrote:

    Jeffrey Williams wrote:

    I have no idea why Microsoft has kept that option in the product - auto shrink will cause massive fragmentation of the database every time it shrinks.  It is just a bad option that has no benefits...there should be no reason to shrink a database on a normal basis.

    As for why you cannot use auto close - I think that is obvious.  If you enable memory optimized tables - they must remain in memory and auto closing the database forces them out of memory, so you cannot use that option in conjunction with memory optimized tables.

    If you look carefully at the original post, he's setting Auto_Close to 'False'... it shouldn't be causing an error. 🙂

    Totally agreed on your stance for Auto_Shrink... especially since the database is also being set to Read_Only.  There shouldn't be a need even if it was a good idea (and I agree it's not).  Which brings us to a different subject...

    Why would anyone create a new, empty, database as Read_Only?  It would remain empty because you couldn't write to it unless you changed Read_Only to 'False' at least for an initial load.

    I think his question is on *why* you cannot specify the parameter 'Auto_Close' and use a memory optimized data file.  I think that is pretty obvious...you cannot specify that option because the memory has to be allocated for that file when the database starts up and you really want that memory to be contiguous and locked.  Auto closing the database would force the memory to be released - and then reallocated when the database is next opened - and released - and opened, etc...

    To prevent that - they just don't allow the option to even be specified on the create statement...which makes sense.

    As for the read_only - I have to admit I did not even look at that but I totally agree it doesn't make sense.

    Understood and I agree with the reasoning to not be able to set AUTO_CLOSE for the reason stated.  You would think that MS would disallow the Read_Only setting on a CREATE Database and allow you to set AUTO_CLOSE to false even though it would never allow you to set it to True.  I've also not been able to find anything (yet) in the documentation as to why you shouldn't even think of setting it to TRUE for the memory optimized stuff.  I've not been looking in a dedicated fashion but one shouldn't have to.  It should be clearly documented both in the setting documentation and in the memory optimization documentation.

    The error they post is, as you say, pretty obvious, though.  I would just shrug and remove the setting even though I was trying to explicitly set it to "False".

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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