Check AutoClose Status of a Database | SansSQL

Wednesday, March 5, 2014

Check AutoClose Status of a Database

For Setting up Database Mirroring, one of the important pre-requisite is to turn off AutoClose option on the database.
This script helps to check the status of the autoclose option on a database and will generate the script to disable autoclose if it is enabled.

DECLARE @DBName NVARCHAR(100)
SET @DBName ='SansSQL'
PRINT CASE WHEN DATABASEPROPERTY ( @DBName, 'IsAutoClose') = 1
   THEN 'AUTO Close is enabled. Run the below statement to Disable Auto Close '+ 
     char(10) + '----------------------------------------------------------'+ char(10)+
     'ALTER DATABASE '+ @DBName +' SET AUTO_CLOSE OFF'
   WHEN DATABASEPROPERTY ( @DBName, 'IsAutoClose') = 0
   THEN 'AUTO Close is Disabled'
  END

No comments:

Post a Comment