SQL Server database settings

When creating a new SQL Server database I always use this checklist:

Name: Use as less non-alpha numeric characters in the database name as possible (do not use the ‘-‘, it will mess up your scripts). Use pascal casing for readability (use OrdersDb, do not use Orders-db or Orders_db)

Set Page verify to Checksum

Pre allocate data and log size to a reasonable size, do not rely on autogrowth.
Set a reasonable Autogrow size in MB (not a percentage) for data and log files

Enabling auto create/update statistics, is fine for most situations.

Set the right recovery model.
If your using ‘full’ or ‘bulk recovery’, schedule regular log backups (see this post )

Try to create at least two –equally sized!- device files for the data devices.
Not only reduces this locking issues but also when a database is regular under a heavy load,files can easily be moved to additional physical volumes (see this post )

Create one file for the Log

Use fn_virtualfilestats() to measure I/O load per device file (see this post )

Set the .MDF, .NDF and .LDF files in their own folders with the same name as the database. Don’t store all your db’s in one folder

Reorganize indexes/statistics on a regular basis (see this post )

Run DBCC CHECKDB on a regular basis (see this post )

Do not schedule any shrink operations

Configure instant file initialization (see this post )

Check out my post on auto index management and tools for performance, to setup more databases on the same instance and save costs

SQL Server 2000 post upgrade steps

Small post on additional steps to perform when upgrading from an existing SQL Server 2000 database to SQL Server 2005 / 2008 (after you backup’ed, run upgrade advisor and tested everything twice right!)

After a successful restore or attach database operation, execute the following commands in the new environment:

USE your_db_name
GO
DBCC UPDATEUSAGE ('your_db_name')
DBCC CHECKDB ('your_db_name') WITH DATA_PURITY
EXEC sp_updatestats

And:
- Set database compatibility level to ‘SQL Server 2005’ or ‘SQL Server 2008’
- Set Page verify option to Checksum
- Set 'Auto Create' and 'Auto Update' statistics to TRUE
- Reorganize all indexes

Running ‘DBCC UPDATEUSAGE’ and ‘DBCC WITH DATA_PURITY’ once is enough.
The DBCC CHECKDB of the newer (2005/2008) SQL Server versions has it all combined in one statement.

Upgrade to the the newer varchar(max), nvarchar(max), and varbinary(max) data types instead of text, ntext, and image data types.
You can check by executing this T-SQL on your upgraded database on SQL Server 2005 / 2008, to see which tables need some work:

USE = 'your_db-name_here'
GO

SELECT userobjects.name tablename, columns.name columnname, types.name columntype FROM sys.all_columns columns INNER JOIN sys.all_objects userobjects ON columns.object_id = userobjects.object_id INNER JOIN sys.types types ON columns.system_type_id = types.system_type_id
WHERE userobjects.[type] = 'U'
and types.name IN ('image','text','ntext')

SQL Server Day to day maintenance

Here’s a little SQL Server T-SQL script I wrote that will executes some day-to-day maintenance
It’ calls some sp’s in MSDB to remove old (as specified by the parameter in the dateadd() function) mail, job and backup history info that will normally accumulate in msdb making it grow bigger over time.

Also a DBCC CHECKDB statement is constructed for each on-line database. Microsoft recommends executing this command frequently. Especially before a full backup is made.

benefits of this script over a SSIS maintenance plan:
- newly created databases are automaticly included
- Databases can be set off-line without the job-step to fail
- Databases in standby mode are skipped
- minumum overhead, giving you precise control
- I've been running this script for more then 2 years now without any error(s)...

I normally schedule this script daily in SQL Server Agent as a separate job before the full backup kicks in, or include as a separate step in the backup process.

Here’s T-SQL Code (store in the CodeCatalog):

-- ====================================================================

-- Author: SQL Server Advisor
-- Create date: jan/2009
-- Description: execute maintenance tasks per database, deletes MSDB log info
-- ====================================================================
CREATE PROCEDURE [dbo].[usp_DatabaseMaintenance] AS
BEGIN
DECLARE @Histdate DATETIME
DECLARE @dbname VARCHAR(100), @command NVARCHAR(350)

  SELECT @Histdate= DATEADD(DAY, -28, GETDATE())

--sp’s stored in MSDB
  EXEC msdb..SP_DELETE_BACKUPHISTORY @Histdate -- delete old history info

  -- delete old mail items
  EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Histdate

  -- delete the log of the sent items
  EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @Histdate

  -- delete the job history log
  EXEC msdb.dbo.SP_PURGE_JOBHISTORY @oldest_date = @Histdate

  --get all the on-line databases
    DECLARE dbnames_cursor CURSOR FAST_FORWARD FOR
      SELECT name FROM sys.databases
     WHERE name NOT IN ('tempdb') -- skip the unwanted DBs
       AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
       AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
     ORDER BY name

   OPEN dbnames_cursor
   FETCH NEXT FROM dbnames_cursor INTO @dbname

   WHILE @@FETCH_STATUS = 0
   BEGIN
     SET @dbname = QUOTENAME(@dbname)

     SET @command = 'DBCC CHECKDB (' + @dbname + ') WITH NO_INFOMSGS'
     PRINT @command
     EXEC SP_EXECUTESQL @command

     FETCH NEXT FROM dbnames_cursor INTO @dbname
  END

  CLOSE dbnames_cursor
  DEALLOCATE dbnames_cursor

END