x

changing "Auto Close" option

Hi All,

Can I change this option from "True" to "False" online? And does it affect current connections or anything in the DB?

Regards

more ▼

asked Nov 10, 2009 at 12:53 PM in Default

avatar image

wassim
51 5 5 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Yes you can and I recommend that you do, even if it is an asynchronous process since SQL server 2005, and repeatedly opening and closing the database no longer reduces performance, but there are some other side effects.

  • Database mirroring requires AUTO_CLOSE OFF
  • an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance

Use:

ALTER DATABASE yourDBName SET AUTO_CLOSE OFF
more ▼

answered Nov 10, 2009 at 03:24 PM

avatar image

Håkan Winther
16.6k 37 45 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2170

asked: Nov 10, 2009 at 12:53 PM

Seen: 2269 times

Last Updated: Nov 10, 2009 at 12:53 PM

Copyright 2017 Redgate Software. Privacy Policy