AUTO_CLOSE is a database option available in SQL Server. When this option is turned ON, a database will be shut down after all resources that reference this database are freed.
After a database is shutdown, next time an application attempts to use the database, the database has to be first opened and then get the status to online. This could take some time and can result in application time outs.
The AUTO_CLOSE setting can be a useful feature in some situations. When you have 32-bit SQL Server that contains a large number of databases, memory is a big constraint. Under such scenarios, it will be useful to enable AUTO_CLOSE and conserve the memory resources required to keep a database open when there is no application using the database. When the database is open, there are some default memory allocations that are required [e.g. internal structures to represent various database metadata objects, transaction log buffers, etc.].
After a database is shutdown, next time an application attempts to use the database, the database has to be first opened and then get the status to online. This could take some time and can result in application time outs.
The AUTO_CLOSE setting can be a useful feature in some situations. When you have 32-bit SQL Server that contains a large number of databases, memory is a big constraint. Under such scenarios, it will be useful to enable AUTO_CLOSE and conserve the memory resources required to keep a database open when there is no application using the database. When the database is open, there are some default memory allocations that are required [e.g. internal structures to represent various database metadata objects, transaction log buffers, etc.].