AUTO_CLOSE in SQL Server

Question

Hello Guys Yesterdays I was doing some research on AUTO_CLOSE option in SQL server and after reading many articles and blogs I've one little doubt. 95% of those articles said that we've to keep AUTO_CLOSE option OFF for any database in any environment we're not suppose to enable this option. So my question is if we need to keep this option OFF all the time then what is the use/purpose of this option??? Why this option is still available in SQL Server?? In what scenario we enable AUTO_CLOSE?? Anyone please suggest. Thanks and Regards Abhishek Ramjeet Yadav.

Manoj Bhoir · Answer

Hi Abhishek Your question is correct. If we need to keep SQL Server AUTO CLOSE optioin OFF all the time then what is the use/ Purpose of this option. But Check this link https://technet.microsoft.com/en-us/library/ms135094%28v=sql.105%29.aspx This feature will be removed in a future version of Microsoft SQL Server after SQL SERVER 2008 R2. So Avoid using this feature in new development work and plan to modify applications that currently use this feature.

Manoj Bhoir · Answer

Hi When 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 set to OFF  the database remains open after the last user exits. The problem however is that there is minimal cost and overhead associated with not only tearing down existing connections file-handles and buffer allocations in order to 'shut down' a database when it auto-closes but there can also be significant overhead involved in spinning a database back up when it's accessed by subsequent applications or users. In fact if you think about the benefits of how SQL Server connection pooling works from within .NET applications – the entire purpose of connection pooling is designed to amortize the COST of making expensive initial connections to SQL Server databases over multiple requests. For more info please check this link : http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases

Abhishek Yadav · Answer

Thanks for you effort Manoj but I want to know where we can get the benefit of this feature I mean if this feature needs to be kept OFF all the time why it's still present in SQL Server.