SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

This Month

Month Archive

June 2009

Year Archive

2009

Recent Articles

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

Links

Get Information about Updates

Main Page

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization - Documentation

Database Backup

Procedure name: DatabaseBackup

Purpose: Create backup directories, backup databases, verify backups and delete old backups.

Parameters: See table below.

Name	Description	Supported Values
Databases	Selection of databases.	E.g. SYSTEM_DATABASES E.g. USER_DATABASES E.g. Database1 E.g. Database1, Database2 E.g. USER_DATABASES, master E.g. SYSTEM_DATABASES, -master E.g. %Database% E.g. %Database%, -Database1
Directory	Backup root directory.	E.g. C:\Backup
BackupType	Type of backup.	FULL DIFF LOG
Verify	Is the backup to be verified?	Y N (DEFAULT)
CleanupTime	Time in hours after which the backup files are deleted. If not specified no backup files are deleted.	NULL (DEFAULT) E.g. 24
Compress	Is the backup to be compressed?	Y * N (DEFAULT)
CopyOnly	Perform a copy-only backup.	Y N (DEFAULT)
ChangeBackupType	Change backup type if a differential or transaction log backup cannot be performed.	Y N (DEFAULT)
BackupSoftware	Third party backup software. If not specified sql server native backup is performed.	NULL (DEFAULT) LITESPEED
Execute	Execution of commands. Default the commands are executed normally. If set to N the commands are only printed.	Y (DEFAULT) N

* Backup compression is only supported in SQL Server 2008 Enterprise and Developer Edition.

Integrity Check

Procedure name: DatabaseIntegrityCheck

Purpose: Check the integrity of databases.

Parameters: See table below.

Name	Description	Supported Values
Databases	Selection of databases.	E.g. SYSTEM_DATABASES E.g. USER_DATABASES E.g. Database1 E.g. Database1, Database2 E.g. USER_DATABASES, master E.g. SYSTEM_DATABASES, -master E.g. %Database% E.g. %Database%, -Database1
PhysicalOnly	Limit the checks to the physical structures of the database.	Y N (DEFAULT)
NoIndex	Nonclustered indexes are not checked.	Y N (DEFAULT)
Execute	Execution of commands. Default the commands are executed normally. If set to N the commands are only printed.	Y (DEFAULT) N

Index Optimization

Procedure name: IndexOptimize

Purpose: Rebuild and reorganize indexes and update statistics.

Parameters: See table below.

Name	Description	Supported Values
Databases	Selection of databases.	E.g. SYSTEM_DATABASES E.g. USER_DATABASES E.g. Database1 E.g. Database1, Database2 E.g. USER_DATABASES, master E.g. SYSTEM_DATABASES, -master E.g. %Database% E.g. %Database%, -Database1
FragmentationHigh_LOB	Action to be performed on a high fragmented index that does contain a LOB.	INDEX_REBUILD_OFFLINE (DEFAULT) INDEX_REORGANIZE STATISTICS_UPDATE INDEX_REORGANIZE_STATISTICS_UPDATE NOTHING
FragmentationHigh_NonLOB	Action to be performed on a high fragmented index that does not contain a LOB.	INDEX_REBUILD_ONLINE * INDEX_REBUILD_OFFLINE (DEFAULT) INDEX_REORGANIZE STATISTICS_UPDATE INDEX_REORGANIZE_STATISTICS_UPDATE NOTHING
FragmentationMedium_LOB	Action to be performed on a medium fragmented index that does contain a LOB.	INDEX_REBUILD_OFFLINE INDEX_REORGANIZE (DEFAULT) STATISTICS_UPDATE INDEX_REORGANIZE_STATISTICS_UPDATE NOTHING
FragmentationMedium_NonLOB	Action to be performed on a medium fragmented index that does not contain a LOB.	INDEX_REBUILD_ONLINE * INDEX_REBUILD_OFFLINE INDEX_REORGANIZE (DEFAULT) STATISTICS_UPDATE INDEX_REORGANIZE_STATISTICS_UPDATE NOTHING
FragmentationLow_LOB	Action to be performed on a low fragmented index that does contain a LOB.	INDEX_REBUILD_OFFLINE INDEX_REORGANIZE STATISTICS_UPDATE INDEX_REORGANIZE_STATISTICS_UPDATE NOTHING (DEFAULT)
FragmentationLow_NonLOB	Action to be performed on a low fragmented index that does not contain a LOB.	INDEX_REBUILD_ONLINE * INDEX_REBUILD_OFFLINE INDEX_REORGANIZE STATISTICS_UPDATE INDEX_REORGANIZE_STATISTICS_UPDATE NOTHING (DEFAULT)
FragmentationLevel1	The lower limit in percent for medium fragmentation.	E.g. 5 (DEFAULT)
FragmentationLevel2	The lower limit in percent for high fragmentation.	E.g. 30 (DEFAULT)
PageCountLevel	Indexes under this size in pages are considered to be low fragmented regardless of actual fragmentation level.	E.g. 1000 (DEFAULT)
SortInTempdb	Use tempdb for sort operations when rebuilding indexes.	Y N (DEFAULT)
MaxDOP	The number of CPUs that are used when rebuilding indexes. If not specified the global max degree of parallelism is used.	NULL (DEFAULT) 0 - 64
FillFactor	Percentage that indicates how full the pages should be made when rebuilding indexes. If not specified the fillfactor in sys.indexes is used.	NULL (DEFAULT) 1 - 100
LOBCompaction	Should pages that contain LOB columns be compacted when reorganizing indexes?	Y (DEFAULT) N
StatisticsSample	Percentage that indicates how much of a table that is gathered when updating statistics. If not specified SQL Server automatically computes the required sample.	NULL (DEFAULT) 1 - 100
PartitionLevel	Optimize partitioned indexes on the partition level.	Y * N (DEFAULT)
Execute	Execution of commands. Default the commands are executed normally. If set to N the commands are only printed.	Y (DEFAULT) N

* Online rebuild is only supported in Enterprise and Developer Edition.

* Table partitioning is only supported in Enterprise and Developer Edition.

Setup

To use the solution for backup you need the objects DatabaseBackup, CommandExecute and DatabaseSelect,
to use it for integrity check you need DatabaseIntegrityCheck, CommandExecute and DatabaseSelect
and to use it for index optimization you need IndexOptimize, CommandExecute and DatabaseSelect.

Execute the stored procedures from SQL Server Agent Jobs, through sqlcmd.

E.g. sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'SYSTEM_DATABASES'" -b

Use SQL Server Agent Job Output files with tokens for the logging.

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT_JOB
_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

The solution is supported on SQL Server 2005 and SQL Server 2008.

Download and install the solution in one script.

Error Scenarios

Description	Applies to	Behavior
A database command fails.	DatabaseBackup DatabaseIntegrityCheck	The procedure logs the error and continues to the next database. In the end the job reports failure.
An index command fails.	IndexOptimize	The procedure logs the error and continues to the next index. In the end the job reports failure.
A backup command fails.	DatabaseBackup	The procedure skips the verify backup and delete backup steps for that database. The procedure then continues to the next database. In the end the job reports failure.
A verify backup command fails.	DatabaseBackup	The procedure skips the delete backup step for that database. The procedure continues to the next database. In the end the job reports failure.
A table is locked and the index command therefore cannot be performed.	IndexOptimize	When the index command has been waiting on locks for one hour it logs an error and the procedure continues to the next index. In the end the job reports failure.
A database is not in an Online state.	DatabaseBackup DatabaseIntegrityCheck IndexOptimize	The procedure logs that the database is not Online and continues to the next database. In the end the job reports success.
A table or index is dropped or altered after the job has started.	IndexOptimize	A final check is performed just before starting an index command. If the index has been dropped or altered the procedure skips the index and continues to the next index. In the end the job reports success.
A backup file is locked and cannot be deleted.	DatabaseBackup	The file is skipped with no waiting. In the end the job reports success. This is the behavior of the extended stored procedure xp_delete_file and cannot be changed.

Frequently Asked Questions

Question	Answer
I'm getting this error message when I try to create the objects. "Msg 102, Level 15, State 1, Procedure IndexOptimize, Line 354" "Incorrect syntax near '('."	This error is because of that the database that you are creating the objects in, is not in compatibility level 90 or 100. Change the compatibility level to 90 or 100 and try again. EXECUTE dbo.sp_dbcmptlevel @dbname=N'DatabaseName', @new_cmptlevel=90
Does all databases have to be in compatibility level 90 or 100?	It is only the database that you are creating the objects in, that has to be in compatibility level 90 or 100. All other databases can be in any compatibility level.
I'm getting this informational message when I create the stored procedure DatabaseBackup, DatabaseIntegrityCheck or IndexOptimize. "Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'dbo.CommandExecute'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists."	DatabaseBackup, DatabaseIntegrityCheck and IndexOptimize are using the stored procedure CommandExecute and the function DatabaseSelect. Download these objects and create them in the same database. You could also use the script MaintenanceSolution.sql. This script contains all the objects that you need.
I'm getting these error messages or similar when I execute the stored procedure DatabaseBackup, DatabaseIntegrityCheck or IndexOptimize. "Msg 208, Level 16, State 1, Procedure DatabaseBackup, Line 100" "Invalid object name 'dbo.DatabaseSelect'." "Msg 2812, Level 16, State 62, Procedure DatabaseBackup, Line 278" "Could not find stored procedure 'dbo.CommandExecute'."	DatabaseBackup, DatabaseIntegrityCheck and IndexOptimize are using the stored procedure CommandExecute and the function DatabaseSelect. Download these objects and create them in the same database. You could also use the script MaintenanceSolution.sql. This script contains all the objects that you need.
Does the stored procedures have to be executed in a CmdExec job step with sqlcmd? Can I not use a T-SQL job step?	The stored procedures have to be executed in a CmdExec job step with sqlcmd and the -b parameter. Otherwise the error handling and logging will not work as designed. You could use the script MaintenanceSolution.sql to see how the jobs could be created.
Can the solution be used with SQL Server Express?	The solution is supported on SQL Server Express. As SQL Server Express has no SQL Server Agent, the jobs have to be scheduled using cmd - files and Windows Scheduled Tasks instead. Examples of how the cmd - files can be created are available here.
I'm using an old version of the solution. How do I upgrade to the latest version?	The easiest way to upgrade to the latest version is to use the script MaintenanceSolution.sql. This script will drop and recreate all the objects in the solution. It will not drop or change any jobs. All your existing jobs will continue to work unchanged as all the objects are backward compatible.
What should the fragmentation levels be for reorganization and rebuild of indexes?	Microsoft has some recommendations in Books Online. The default values in IndexOptimize are based on this.
Some of my indexes are still fragmented after I have just executed IndexOptimize. How could that be?	Very small indexes sometimes shows a high fragmentation even if they've just been rebuilt or reorganized. In addition fragmentation on very small tables has no impact on performance. Microsoft has a whitepaper about this. "Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."