Disable the auto-close feature for the database

Published on: 28th Nov 2019

Updated on: 19th May 2021

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 hard disk performance.

Here is the script to disable the auto-close

Here is the SQL script to disabled auto-close feature.

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

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.