SQL Server Performance

AUTO_CLOSE reverts to ON after atatching the .mdf

Discussion in 'SQL Server 2008 General Developer Questions' started by anonymous2009, Nov 23, 2011.

  1. anonymous2009 New Member

    Hello,
    I have this .mdf file which is being generated by a different team.
    The database on their side has AUTO_CLOSE as OFF.
    But once I attach that .mdf in my local instance in SQL SEREVR EXPRESS 2008, and look
    at the database properties, AUTO_CLOSE seems to be set to ON.

    Why is this happening?
    Should I manually set the AUTO_CLOSE to OFF everytime after attaching the .mdf file?

    Thanks!
    anonymous2009, Nov 23, 2011
    #1

  2. Luis Martin Moderator

    From Microsoft:
    Features like Auto-Close and the ability to copy databases as files are enabled by default in SQL Server Express, while the high availability and business intelligence features are absent. It is very easy to "scale up" if that becomes necessary, as Express applications will work seamlessly with SQL Server 2005 Workgroup, Standard, or Enterprise editions. The Web download enables a free, fast, and convenient deployment.
    Luis Martin, Nov 23, 2011
    #2

  3. anonymous2009 New Member

    Found out the following:

    In Restore Option:
    If the source database has AUTO_CLOSE off, after restoring to Express edition, the AUTO_CLOSE is still turned off.
    The AUTO_CLOSE is turned on only if you are creating new databases in SQL Express.

    In Attach Option:
    If you attach the .mdf file,you need to turn off the Auto_ClOSE manually as when AUTO_CLOSE is set ON, this option can cause performance degradation on frequently accessed databases due to increased overhead of opening and closing the database after each connection.
    anonymous2009, Nov 29, 2011
    #3

  4. satya Moderator

    Always set AUTO_CLOSE to OFF on the production database.
    satya, Dec 23, 2011
    #4

Share This Page