Get Autoclose and Autoshrink Values for the Databases in Sql Server

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