August 21, 2009 at 12:13 am
Hi,
Please help me to understand the AutoClose and AutoShrink property of database. Whether both should be true or false when we create a data base.
What will be the effect of AutoClose and AutoShrink when it is false.
August 21, 2009 at 12:35 am
Auto_Close
When Auto_Close is set to ON/TRUE, the database is closed and shut down when all processes in the database complete and the last user exits the database, thereby freeing up the resources held by that database. When a new connection calls the database again, it automatically reopens. This option is set to ON/TRUE when using the SQL Server Desktop Edition, but is set to OFF/FALSE for all other editions of SQL Server.
The problem is that most servers sit behind applications that are repeatedly opening and closing connections to your databases, so the overhead of closing and reopening the databases between each connection is, well, “performance abuse”. The amount of memory that is saved by this is insignificant, and certainly does not make up for cost of repeatedly initializing the database.
Admittedly, this option may have advantages on personal desktop scenarios as (when they are closed) you can treat these database files as any other files. You can move them and copy them, or even e-mail them to other users. However, when it comes to a proper server environment these points are fairly irrelevant.
So as far as Auto_Close is concerned, don’t even be tempted. Just Don’t.
Auto_Shrink
The auto_shrink option has it’s uses in scenarios such as development servers and the like where disk space resources are usually limited and hotly contested, but (there’s always a ‘but’) there is a performance cost. Shrinking a database hogs the CPU and takes a long time. Plus, any indexes on the heaps (a table without a clustered index) affected by the shrink must be adjusted because the row locators will have changed. More work for the CPU. Like Auto_Close, this option is set to ON/TRUE for all databases when using SQL Server Desktop Edition, and OFF for all other editions, regardless of operating system.
When this option is set to ON/TRUE, all of a database's files are marked for shrinking, and will be automatically shrunk by SQL Server. This option causes files to be shrunk automatically when more than 25 percent of the file contains unused space. Not a wise option for your production systems which would suddenly suffer a performance hit when SQL decides it’s shrink-time. So, again – just don’t.
August 21, 2009 at 1:47 am
Auto shrink should never be on, not on a dev server even.
Auto close can be of use for databases that are very, very seldom accessed, but it's a very bad idea of even a relatively busy server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2009 at 5:14 am
GilaMonster (8/21/2009)
Auto shrink should never be on, not on a dev server even.Auto close can be of use for databases that are very, very seldom accessed, but it's a very bad idea of even a relatively busy server.
Yep agree with that, It is mostly normal practise to have these options switched off.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply