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:
- Log on to server using Remote Desktop.
- Click the New Query button.
- 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
Article ID: 402, Created: April 9, 2012 at 2:49 PM, Modified: August 26, 2014 at 9:17 AM