Turn off Auto_Close on all MS SQL Databases

 

Turn off Auto_Close on All Databases

In order to turn off Auto_Close on all of the databases on the server please follow the steps below:
  1. Log on to server using Remote Desktop.
  2. Click the New Query button.
  3. Copy the follow script into the New Query box then run the script.
DECLARE @Databases TABLE
(
name nvarchar(255)
)
 
insert into @Databases
select [name] from master.sys.databases where [name] NOT IN ('master', 'msdb', 'tempdb') and is_auto_close_on = 1 order by name
 
declare @NAME nvarchar(255)
DECLARE LOOPER CURSOR FOR
select [name] from @Databases
 
OPEN LOOPER
FETCH LOOPER INTO @Name
 
WHILE @@Fetch_Status = 0 BEGIN
Declare @cmd varchar(255)
set @cmd = 'ALTER DATABASE [' + @name + '] SET auto_close off'
exec (@cmd)
print @cmd
FETCH LOOPER INTO @Name END
 
CLOSE LOOPER
DEALLOCATE LOOPER
 

 

Add Feedback