MSSQL - enable or disable the auto-close feature for all databases

Overview

We have a situation, it seems like the SQL server is crawling and we found many events in the Event viewer which stated a database has been opened and closed. This happened many times, especially during the set up of a new server.

If auto-close has been enabled in the database, it will flood the event log with "starting database..". It will affect the storage performance because of the unnecessary IO process.

How does it work

When a database has been loaded into the memory, it won't be closed. Sooner or later, it will cause memory contention issue if your MSSQL server has many databases. If auto close feature has been enabled, the database will be closed when idle and it will be removed from the memory.

When to use it

This feature is useful in the following situation

• You need to save some data and after that no more data to be saved for a long period (i.e., not frequent read or write activity).
• You are a developer. You don't need the database to be remained open after your testing or writing codes has been completed.

To disable or enable the database auto close feature

Here's the command to disable the auto close feature:

alter database your_db_name set AUTO_CLOSE off

And to enable it:

alter database your_db_name set AUTO_CLOSE on

Disable the auto-close in all databases

Here is the SQL script to disable database auto-close feature if it has been enabled:

set nocount on
declare
    @seq int
    , @db sysname
    , @sql nvarchar(max)

select seq = row_number() over (order by name), name
into #db_list
from sys.databases
where is_auto_close_on = 1

set @seq = 0

while exists(
    select *
    from #db_list
    where seq > @seq
)
begin
    select top 1
        @seq = seq
        , @db = name
    from #db_list
    where seq > @seq
    order by seq

    print 'resetting auto close for ' + @db
    set @sql = 'alter database ' + @db + ' set AUTO_CLOSE off'

    ----print @sql
    exec sp_executesql @sql
end

print 'done'

drop table #db_list
set nocount off

Back to #MSSQL blog

Back to #blog listing