Sunday 28 June 2009

Best Practice : Remove Auto Close from all databases!

Following Buck Woody's post that 'AutoClose Should be Off' , here is a script to ensure that is the case... 

SQL 2000 Version :
DECLARE @databasename varchar(100)
DECLARE @sqlAlterStatement varchar(500)

DECLARE NastyCursorThing CURSOR READ_ONLY FOR
SELECT Name FROM sysdatabases
WHERE DBID > 4 AND DATABASEPROPERTYEX(name, 'IsAutoClose') = 1

OPEN NastyCursorThing
FETCH NEXT FROM NastyCursorThing INTO @databasename
WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @sqlAlterStatement ='ALTER DATABASE [' + @databasename + '] SET AUTO_CLOSE OFF WITH NO_WAIT' + CHAR(10)
 print @sqlAlterStatement
 EXEC(@sqlAlterStatement)
 FETCH NEXT FROM NastyCursorThing INTO @databasename
 END

CLOSE NastyCursorThing
DEALLOCATE NastyCursorThing
SQL 2005/2008 Version :
DECLARE @databasename varchar(100)
DECLARE @sqlAlterStatement varchar(500)

DECLARE NastyCursorThing CURSOR READ_ONLY FOR
SELECT name FROM sys.databases
WHERE database_id >  4 AND DATABASEPROPERTYEX(name, 'IsAutoClose') = 1

OPEN NastyCursorThing
FETCH NEXT FROM NastyCursorThing INTO @databasename
WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @sqlAlterStatement ='ALTER DATABASE [' + @databasename + '] SET AUTO_CLOSE OFF WITH NO_WAIT' + CHAR(10)
 print @sqlAlterStatement
 EXEC(@sqlAlterStatement)
 FETCH NEXT FROM NastyCursorThing INTO @databasename
 END

CLOSE NastyCursorThing
DEALLOCATE NastyCursorThing

No comments: