autoclose logic

from what I read auto-close is a dangerous setting in SQL as it can lead to performance issues. But are there any situations where it would be ok/beneficial? we have some DB which are archive data and rarely queries, in this case does autoclose make sense( trying to understand the logic why a DBA set it on some DB and not others).
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

When AutoClose set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

When AutoClose  set to OFF, the database remains open after the last user exits.
0
Vikas GargBusiness Intelligence DeveloperCommented:
Since for few databases which are not transaction databases it is good to keep the autoclose off since every other minute / second new connection is about to establish.

Where as in non transnational databases due to less connections traffic autoclose  can be ON
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's no good reason to let Autoclose ON. Even Microsoft knows that and will remove that option in a near future:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

You can read it here.

NOTE: If you have databases for archival purposes just move them to a less perfomant server.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.