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