Post reply

AUTO_CLOSE reverts to ON in db created by attaching .mdf

  • November 23, 2011 at 11:37 am

    #251006

    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!

  • November 23, 2011 at 11:49 am

    #1412398

    that's a feature of express; by default, a database is autoclose = true in order to conserve resources.

    since the setting is visible in master.sys.databases, and not INSIDE the database, it doesn't really matter what the original setting was at it's former location.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 23, 2011 at 11:50 am

    #1412400

    The AUTO_CLOSE option is a server option in the model database and not a setting of the database. You can update the setting on your local server with sp_configure.

  • November 23, 2011 at 12:00 pm

    #1412407

    Bill Hansen (11/23/2011)

    The AUTO_CLOSE option is a server option in the model database and not a setting of the database. You can update the setting on your local server with sp_configure.

    yep that will affect any newly created databases, but I believe the behavior for attaching a database is still based on the version of SQL (Express vs any others)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 23, 2011 at 12:04 pm

    #1412409

    You also do it by changing the auto-close property to false of model DB.. so after attaching u don't need to change.

  • November 23, 2011 at 12:06 pm

    #1412412

    viiki.seth (11/23/2011)

    You also do it by changing the auto-close property to false of model DB.. so after attaching u don't need to change.

    As Lowell said:

    yep that will affect any newly created databases, but I believe the behavior for attaching a database is still based on the version of SQL (Express vs any others)

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 28, 2011 at 11:01 am

    #1413571

    Thanks for the responses.

    My Model database already has AUTO_CLOSE as FALSE.

    But even then when I attach the .mdf to the same instance, AUTO_CLOSE is being set to TRUE.

    Looks like I'll have to set the AUTO_CLOSE to FALSE explicitly using ALTER database statement right after attaching the .mdf.

    Thanks!

  • March 2, 2012 at 9:10 am

    #1454255

    I am seeing the same behavior. Auto close resets to true after a detach and attach.

  • March 2, 2012 at 9:49 am

    #1454277

    sudnya_s (3/2/2012)

    I am seeing the same behavior. Auto close resets to true after a detach and attach.

    yeah, as we explained above, if you are running an EXPRESS version, NO MATTER WHAT YOU DO, it will be attached as AutoClose = true,and you must manually change it afterwards.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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