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]
@dbnamenvarchar(120) = N'%',-- List of database names to process
@setoptschar(1) = 'Y',-- Set DB options
@setownerchar(1) = 'Y',-- Set DB owner
@setsizechar(1) = 'Y',-- Set DB size
@debugchar(1) = 'N',-- Print debug information
@processchar(1) = 'Y',-- Process commands
@stopchar(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 @cmptleveltinyint
DECLARE @dbownernvarchar(60)
DECLARE @debug_labelnvarchar(60)
DECLARE @fnamenvarchar(60)
DECLARE @index integer
DECLARE @instlevelinteger
DECLARE @sa_acntnvarchar(60)
DECLARE @sa_flagbit
DECLARE @sqlstringnvarchar(4000)
DECLARE @rcinteger
--
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 
