AutoClose Option for SQL Server:
By default, this option should be false. If the connection to the database or
the last user release his connection from the database. Immediately, the
database will think that all the connections were closed. So it will get closed
and shutdown automatically. In addition, it will release all the memory and
other resources.
This will impact the performance of the application due to database shutdown if
there is no connections. Releasing the resources and getting the resources back
while the database is online will hurt the performance. So, it's advisable to
keep this option false for ever in the production systems.
Auto Shrink:
It's an other option which needs to be kept off for the production databases.
This option will shrunk the database files automatically if the free space of
the database files is more than 25 percent. If the shrinking of database files
happened it will surely affect the data position + your indexes will get
affected hugely. That's why, this option needs to be made false.
Below is the query to find the options set in your databases.
SELECT [name] AS DatabaseName
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) AS AutoClose
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) AS AutoShrink
FROM master.dbo.sysdatabases
Order By DatabaseName
![image1.gif]()