Set standard database options

,

This routine sets standardised database options. It is a common problem that database options differ from the site standard, especially in Development environments. Running this routine via a weekly SQL Agent job overcomes this problem.

To set standard options for all databases:

EXEC spSetDBOptions

To set standard options for a given database:

EXEC spSetDBOptions @dbname='Your_Database'

To generate code but make no changes, use the @process parameter. To seet debugging information, use the @debug parameter:

EXEC spSetDBOptions @process='N',@debug='Y'

The setting of individual options can be suppressed by using the @setopts, @setowner, @setsize parameters.

This routine will produce an appropriate message if an errors occurs. Use the following statement to add the message to SQL Server:

EXEC sp_addmessage 700000, 16, N'%ls'

USE DBA_Data
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSetDBOptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].[spSetDBOptions]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[spSetDBOptions] 
  @dbname	nvarchar(120) = N'%',	-- List of database names to process
  @setopts	char(1) = 'Y',		-- Set DB options
  @setowner	char(1) = 'Y',		-- Set DB owner
  @setsize	char(1) = 'Y',		-- Set DB size
  @debug	char(1) = 'N',		-- Print debug information
  @process	char(1) = 'Y',		-- Process commands
  @stop		char(1) = 'N'		-- Stop if error encountered
AS BEGIN
--
-- spSetDBOptions Version V1.2 09May2007
--
-- Change History
-- V1.0 11Jan2007 Ed Vassie    Initial version
-- V1.1 21Apr2007 Ed Vassie Added DB Sizing
-- V1.2 09May2007 Ed Vassie Added @dbname parameter
--
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NOCOUNT ON
--
DECLARE @cmptlevel	tinyint
DECLARE @dbowner	nvarchar(60)
DECLARE @debug_label	nvarchar(60)
DECLARE @fname		nvarchar(60)
DECLARE @index    	integer
DECLARE @instlevel	integer
DECLARE @sa_acnt	nvarchar(60)
DECLARE @sa_flag	bit
DECLARE @sqlstring	nvarchar(4000)
DECLARE @rc		integer
--
PRINT 'Setup DB Options for ' + @@SERVERNAME + ' at ' + Convert(char(20), Getdate(), 113)
--
-- Get Instance software level
SET @debug_label = 'SELECT @instlevel'
SELECT @instlevel = cmptlevel FROM master.dbo.sysdatabases WHERE name = 'master'
SELECT @rc = @@error
IF @rc > 0 GOTO setrc 
--
-- Get builtin system administration account name
SET @debug_label = 'SELECT @sa_acnt'
SELECT @sa_acnt = name FROM master..syslogins WHERE sid=1
SELECT @rc = @@error
IF @rc > 0 GOTO setrc 
--
-- Create work tables
SET @debug_label = 'CREATE TABLE #dblist'
CREATE TABLE #dblist (dbid int not null, name nvarchar(256) not null, cmptlevel tinyint, owner nvarchar(256))
SELECT @rc = @@error
IF @rc > 0 GOTO setrc 
--
SET @debug_label = 'CREATE TABLE #dbsize'
CREATE TABLE #dbsize (fileid integer, action bit, name nvarchar(512), sqlalter nvarchar(512))
SELECT @rc = @@error
IF @rc > 0 GOTO setrc
--
-- Build table of databases to be processed
SET @debug_label = 'INSERT INTO #dblist'
INSERT INTO #dblist SELECT dbid,name,cmptlevel,suser_sname(sid)
 FROM master..sysdatabases 
 WHERE HAS_DBACCESS(name) = 1
 AND name LIKE @dbname
SELECT @rc = @@error
IF @rc > 0 GOTO setrc 
IF @debug <> 'N' SELECT * FROM #dblist ORDER BY name
--
-- Declare database cursor dbline
SET @debug_label = 'DECLARE dbline CURSOR'
DECLARE dbline CURSOR FOR 
SELECT
  name,cmptlevel,owner
FROM #dblist ORDER BY name
SELECT @rc = @@error
IF @rc > 0 GOTO setrc 
--
-- Declare database cursor dbsize
SET @debug_label = 'DECLARE dbsize CURSOR'
DECLARE dbsize CURSOR FOR 
SELECT
  sqlalter, name
FROM #dbsize WHERE action = 1 ORDER BY fileid
SELECT @rc = @@error
IF @rc > 0 GOTO setrc 
--
SET @debug_label = 'OPEN dbline'
OPEN dbline
SELECT @rc = @@error
IF @rc > 0 GOTO setrc 
--
SET @debug_label = 'FIRST FETCH dbline'
FETCH dbline INTO @dbname,@cmptlevel,@dbowner
SELECT @rc = @@error
IF @rc > 0 GOTO setrc 
--
WHILE @@fetch_status <> -1
  BEGIN
  PRINT ' '
  PRINT 'Processing database ' + @dbname

  IF @setopts = 'Y'
    BEGIN
    -- Process database options

    IF @instlevel = 80 -- set options for database in SQL 2000 mode
      BEGIN
      IF @cmptlevel < @instlevel
        PRINT 'Invalid database compatibility level. Run "sp_dbcmptlevel ' + @dbname + ', ' + Cast(@cmptlevel as Char(2)) + '" then re-run this procedure'
      ELSE 
        BEGIN
        SET @debug_label = 'Setup DB option string'
        SET @sqlstring = 'ALTER DATABASE [' + @dbname + '] SET AUTO_CREATE_STATISTICS ON, AUTO_UPDATE_STATISTICS ON'
        IF @dbname <> 'tempdb' 
          BEGIN
          SET @sqlstring = @sqlstring + ', TORN_PAGE_DETECTION ON'
          IF @dbname <> 'master' 
            SET @sqlstring = @sqlstring + ', AUTO_CLOSE OFF, AUTO_SHRINK OFF'
          END
        SET @sqlstring = @sqlstring + ' WITH ROLLBACK IMMEDIATE'
        IF @debug <> 'N' PRINT 'SQL String:' + @sqlstring
        IF @process = 'Y' 
          BEGIN
          SET @debug_label = 'EXEC (@sqlstring) db options'
          PRINT 'Setting DB Options'
          EXEC (@sqlstring)
          SELECT @rc = @@error
          IF @rc > 0 
            BEGIN
            IF @stop <> 'N' GOTO setrc 
            SET @sqlstring = @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
            RAISERROR (700000,16,1,@sqlstring) WITH LOG,NOWAIT
            END 
          END
        ELSE 
          PRINT 'Bypassed ' + @sqlstring
        END
      END

    IF @instlevel = 90 -- set options for database in SQL 2005 mode
    BEGIN
    IF @cmptlevel < @instlevel
      PRINT 'Invalid database compatibility level. Run "sp_dbcmptlevel ' + @dbname + ', ' + Cast(@cmptlevel as Char(2)) + '" then re-run this procedure'
    ELSE 
      BEGIN
      SET @debug_label = 'Setup DB option string'
      SET @sqlstring = 'ALTER DATABASE [' + @dbname + '] SET AUTO_CREATE_STATISTICS ON, AUTO_UPDATE_STATISTICS ON, AUTO_UPDATE_STATISTICS_ASYNC ON, DATE_CORRELATION_OPTIMIZATION ON'
      IF @dbname <> 'tempdb' 
        BEGIN
        SET @sqlstring = @sqlstring + ', TORN_PAGE_DETECTION OFF, PAGE_VERIFY CHECKSUM'
        IF @dbname <> 'master' 
          SET @sqlstring = @sqlstring + ', AUTO_CLOSE OFF, AUTO_SHRINK OFF'
        END
      SET @sqlstring = @sqlstring + ' WITH ROLLBACK IMMEDIATE'
      IF @debug <> 'N' PRINT 'SQL String:' + @sqlstring
      IF @process = 'Y' 
        BEGIN
        SET @debug_label = 'EXEC (@sqlstring) DB options'
        PRINT 'Setting DB Options'
        EXEC (@sqlstring)
        SELECT @rc = @@error
        IF @rc > 0 
          BEGIN
          IF @stop <> 'N' GOTO setrc 
          SET @sqlstring = @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
          RAISERROR (700000,16,1,@sqlstring) WITH LOG,NOWAIT
          END 
        END
      ELSE 
        PRINT 'Bypassed ' + @sqlstring
      END
    END
  END 

  IF @setowner = 'Y'
   BEGIN
   -- Set DB owner name
   SELECT @sa_flag = sysadmin FROM master.dbo.syslogins WHERE name = @dbowner
   IF ((Charindex('\#',@dbowner) > 0) OR (Coalesce(@sa_flag, 0) = 1)) AND (@dbowner <> @sa_acnt) AND (Charindex(@dbname + ' ', 'master model tempdb ') = 0)
     BEGIN
     SET @debug_label = 'Setup DB owner string'
     SET @sqlstring = 'USE [' + @dbname +'];EXEC sp_changedbowner ''' + @sa_acnt + ''''
     IF @debug <> 'N' PRINT 'SQL String:' + @sqlstring
     IF @process = 'Y' 
       BEGIN
       SET @debug_label = 'EXEC (@sqlstring) sp_changedbowner'
       PRINT 'Changing DB owner from ' + @dbowner + ' to ' + @sa_acnt
       EXEC (@sqlstring)
       SELECT @rc = @@error
       IF @rc > 0 
         BEGIN
         IF @stop <> 'N' GOTO setrc 
         SET @sqlstring = @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
         RAISERROR (700000,16,1,@sqlstring) WITH LOG,NOWAIT
         END 
       END
     ELSE 
       PRINT 'Bypassed ' + @sqlstring
     END
   END 

  IF @setsize = 'Y'
    BEGIN
    -- Set DB size parameters
    --
    -- Prepare database size table
    SET @debug_label = 'Truncate table #dbsize'
    TRUNCATE TABLE #dbsize
    SELECT @rc = @@error
    IF @rc > 0 GOTO setrc 
    --
    -- Build table of database size alter statements
    SET @debug_label = 'Setup dbsize string'
    SET @sqlstring = 'INSERT INTO #dbsize SELECT fileid, action, name, sqlalter FROM (SELECT s.fileid,' +
       'CASE WHEN s.size < s.newsize THEN 1 WHEN s.growth <> s.newgrowth THEN 1 ELSE 0 END As action, s.name, ' + 
       '''ALTER DATABASE [' + RTrim(@dbname) + '] MODIFY FILE (NAME=['' + s.name + '']'' +' +
       'CASE WHEN s.size < s.newsize THEN '', SIZE = '' + Cast(s.newsize as varchar(50)) + '' MB'' ELSE '''' END + ' +
       'CASE WHEN s.growth <> s.newgrowth THEN '', FILEGROWTH = '' + Cast(s.newgrowth as varchar(50)) + '' MB'' ELSE '''' END + ' + 
       ''', MAXSIZE=UNLIMITED)'' As [sqlalter] ' +
     'FROM (SELECT fileid ,RTrim(f.name) As name ,(f.size / 128) As size ,CASE ' +
       'WHEN ((f.size / 128) <= 10) AND (f.groupid = 0) THEN (((((f.size - 1) / 128) / 5) * 5) + 5) ' +
       'WHEN (f.size / 128) <= 50 THEN (((((f.size - 1) / 128) / 10) * 10) + 10) ' +
       'WHEN (f.size / 128) <= 200 THEN (((((f.size - 1) / 128) / 50) * 50) + 50) ' +
       'WHEN (f.size / 128) <= 1000 THEN (((((f.size - 1) / 128) / 200) * 200) + 200) ' +
       'WHEN (f.size / 128) <= 10000 THEN (((((f.size - 1) / 128) / 500) * 500) + 500) ' +
       'WHEN (f.size / 128) <= 100000 THEN (((((f.size - 1) / 128) / 5000) * 5000) + 5000) ' +
       'ELSE (((((f.size - 1) / 128) / 100000) * 100000) + 100000) ' +
       'END AS newsize ,(f.growth / 128) As growth ,CASE ' +
       'WHEN (f.size / 128) <= 50 THEN 10 ' +
       'WHEN (f.size / 128) <= 200 THEN 50 ' +
       'WHEN (f.size / 128) <= 1000 THEN 200 ' +
       'WHEN (f.size / 128) <= 10000 THEN 500 ' +
       'WHEN (f.size / 128) <= 100000 THEN 5000 ' +
       'ELSE 100000 END AS newgrowth ' +
     'FROM [' + RTrim(@dbname) + '].dbo.sysfiles f ' +
       'WHERE f.groupid IN (SELECT m.groupid FROM [' + RTrim(@dbname) + '].dbo.sysfiles m GROUP BY m.groupid HAVING Count(*) = 1)) s) r'
    IF @debug <> 'N' PRINT 'SQL String:' + @sqlstring
    -- 
    SET @debug_label = 'EXEC (@sqlstring) insert #dbsize'
    EXEC (@sqlstring)
    SELECT @rc = @@error
    IF @debug <> 'N' SELECT * FROM #dbsize ORDER BY fileid
    IF @rc > 0 
      BEGIN
      IF @stop <> 'N' GOTO setrc 
      SET @sqlstring = @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
      RAISERROR (700000,16,1,@sqlstring) WITH LOG,NOWAIT
      END 
    --
    SET @debug_label = 'OPEN dbsize'
    OPEN dbsize
    SELECT @rc = @@error
    IF @rc > 0 GOTO setrc 
    --
    SET @debug_label = 'FIRST FETCH dbsize'
    FETCH dbsize INTO @sqlstring,@fname
    SELECT @rc = @@error
    IF @rc > 0 GOTO setrc 
    --
    WHILE @@fetch_status <> -1
      BEGIN

      PRINT 'Set DB file size: ' + @sqlstring
      IF @process = 'Y' 
        BEGIN
        SET @debug_label = 'EXEC (@sqlstring) Set size'
        EXEC (@sqlstring)
        SELECT @rc = @@error
        IF @rc > 0 
          BEGIN
          IF @stop <> 'N' GOTO setrc 
          SET @sqlstring = @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
          RAISERROR (700000,16,1,@sqlstring) WITH LOG,NOWAIT
          END 
        END
      ELSE 
        PRINT 'Bypassed ' + @sqlstring

      SET @debug_label = 'FETCH dbsize'
      FETCH dbsize INTO @sqlstring,@fname
      SELECT @rc = @@error
      IF @rc > 0 GOTO setrc 
      END

    SET @debug_label = 'CLOSE dbsize'
    CLOSE dbsize
    SELECT @rc = @@error
    IF @rc > 0 GOTO setrc
    END

  SET @debug_label = 'FETCH dbline'
  FETCH dbline INTO @dbname,@cmptlevel,@dbowner
  SELECT @rc = @@error
  IF @rc > 0 GOTO setrc 
  END
--
setrc:
IF @rc > 0 
  BEGIN
  SET @sqlstring = @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
  RAISERROR (700000,16,1,@sqlstring) WITH LOG,NOWAIT
  END
SET @debug_label = 'CLOSE dbline'
CLOSE dbline
SELECT @rc = @@error
IF @rc > 0 PRINT @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
SET @debug_label = 'DEALLOCATE dbline'
DEALLOCATE dbline
SELECT @rc = @@error
IF @rc > 0 PRINT @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
SET @debug_label = 'DROP TABLE #dblist'
DROP TABLE #dblist
SELECT @rc = @@error
IF @rc > 0 PRINT @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
SET @debug_label = 'DEALLOCATE dbsize'
DEALLOCATE dbsize
SELECT @rc = @@error
IF @rc > 0 PRINT @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
SET @debug_label = 'DROP TABLE #dbsize'
DROP TABLE #dbsize
SELECT @rc = @@error
IF @rc > 0 PRINT @debug_label + ' FAILED, ERROR=' + Cast(@rc As Varchar)
--
RETURN @rc
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

4 (1)

Share

Share

Rate

4 (1)