Creating a DB
-
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:
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.
-
AutoShrink = True?
No. Just No.
-
Can you explain?
"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.
-
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - 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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - 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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - 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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply