AUTO_CLOSE reverts to ON in db created by attaching .mdf

Question

AUTO_CLOSE reverts to ON in db created by attaching .mdf

anonymous2009

Ten Centuries

Points: 1164
More actions
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!

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Bill Hansen Ten Centuries Points: 1201 More actions · Answer 2

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.

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

viiki.seth Ten Centuries Points: 1127 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

anonymous2009 Ten Centuries Points: 1164 More actions · Answer 6

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!

sudnya_s Grasshopper Points: 17 More actions · Answer 7

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

Lowell SSC Guru Points: 323518 More actions · Answer 8

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!