What's New for Maintenance with Service Pack 2
With SQL Server Service Pack 2, many new improved features and fixes have bolstered the maintenance plan creation experience. These changes include the following:
The Maintenance Plan designer supports multiple subplans within a maintenance plan and the functionality to create independent schedules for each subplan. Multiple Schedules is a highly anticipated feature that can be leveraged to set separate schedules for items such as backups, updating statistics, and executing SQL Server jobs.
Upon the launch of SQL Server 2005, the installation of SQL Server Integration Services (SSIS) was warranted if organizations wanted to run maintenance plans. This has since changed. Integration Services is no longer required because maintenance plans are now a fully supported feature within the Database Engine.
For increased administration, maintenance plans now support multiserver environments and logging maintenance plan information to remote servers. You can now configure maintenance plans for all target servers from one central master server.
The Maintenance Cleanup Task, which first appeared in SQL Server 2000, has reemerged in the maintenance plans to be enjoyed by every DBA. To review, this task removes any remaining files that were part of the maintenance plan execution.
Other improvements and enhancements are seen with the introduction of SQL Server 2005 Service Pack 2. The following reveals some of the fixes that more than a few people have anticipated to improve specific tasks.
With the release of SQL Server 2005 Service Pack 2, new backup expiration options have been added to the Database Backup maintenance plan task. You can specify backup expiration options, such as having the backup set expire after a specific date or on a specific date. This feature existed in SQL Server 2000; however, it was not available when SQL Server 2005 was released.
If you select a specific location to store the backup folder, the Database Backup maintenance plan task will not reset this option.
In the past, when using the Backup Database maintenance plan task, it was possible to create differential and transaction log backup plans on system databases that were using the Simple Recovery model. This bug has been addressed.
The History Cleanup maintenance plan task offers the option to delete files by selecting a unit of time in hours, ultimately reducing manual work.
The Update Statistics task includes options for full scan or for sample size, as was available in SQL Server 2000 maintenance plans.
Establishing a SQL Server Maintenance Plan
A maintenance plan performs a comprehensive set of SQL Server jobs that run at scheduled intervals. The maintenance plan conducts scheduled SQL Server maintenance tasks to ensure that relational databases within the database engine are performing optimally, conducting regular backups, and checking for anomalies. The Database Maintenance Plan, a feature included within the SQL Server Database Engine, can be used to automatically create and schedule these daily tasks. A comprehensive maintenance plan includes these primary administrative tasks:
Running database integrity checks
Updating database statistics
Reorganizing database indexes
Performing database backups
Cleaning up database historical operational data
Shrinking a database
Cleaning up leftover files from the maintenance plan
Executing SQL Server jobs
Cleaning up maintenance tasks
Note - Unlike SQL Server 2000, Log Shipping is no longer a task included with the maintenance plan. Log Shipping can be configured at the database level in SQL Server Management Studio (SSMS) or via Transact-SQL (TSQL) scripts.
Check Database Integrity Task
The Check Database Integrity Task verifies the health and structural integrity of both user and system tables within relational databases selected in the SQL Server Database Engine. When running this task, you have the option to also check the integrity of all index pages. This specific task can be created in the Maintenance Plan Wizard, which will manually create a Maintenance Task. On the other hand, you can use TSQL to create this task. When you create the Database Integrity task, the database options available include all system databases, all user databases, or specific databases.
Although the following is a basic syntax, it supplies the information you need to assess the health and integrity of the database on the AdventureWorks database.
USE [AdventureWorks] GO DBCC CHECKDB WITH NO_INFOMSGS GO
Shrink Database Task
The Shrink Database Task reduces the physical database and log files to a specific size, similar to the Automatic Shrink Task available in SSMS. When creating a maintenance task, you can shrink all databases, all system databases, all user databases, or specific databases within a single task. This operation removes excess space in the database based on a percentage value you enter. In addition, thresholds in MB can be entered, indicating the amount of shrinkage that needs to take place after the database reaches a certain size and the amount of free space that must remain after the excess space is removed. Finally, free space can be retained in the database or released back to the operating system.
This TSQL syntax shrinks the AdventureWorks database, returns freed space to the operating system, and allows for 15% of free space to remain after the shrink:
USE [AdventureWorks] GO DBCC SHRINKDATABASE(N'AdventureWorks', 15, TRUNCATEONLY) GO
Tip - When you create maintenance plans, it is a best practice not to select the option to shrink the database. First, when shrinking the database, SQL Server moves pages toward the beginning of the file, allowing the tail end of the files to be shrunk. This process can increase the transaction log size because all moves are logged. Second, if the database is heavily used and there are many inserts, the database files will have to grow again. SQL Server 2005 addresses slow autogrowth with instant file initialization; therefore, the growth process is not as slow as it was in the past. However, at times autogrow does not catch up with the space requirements, causing performance degradation. Third, constant shrinking and growing of the database leads to excessive fragmentation. If you need to shrink the database size, it should be done manually when the server is not being heavily utilized.
Reorganize Index Task
When there is a need to improve index scanning performance, look toward the Reorganize Index Task. This task defragments and compacts clustered and nonclustered indexes on all tables or views, or a particular table or view. The Reorganize Index Task can also be applied to all databases, system databases, user databases, or individually targeted databases. By also selecting an additional option, large object (LOB) data types such as images or text will also be included in the compacting process.
To gain better insight into the operation of this task, use the TSQL syntax that follows to reorganize indexes for the AdventureWorks [Sales]. [SalesOrderDetail] table. This example also includes the option to compact large objects:
USE [AdventureWorks] GO ALTER INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail] REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE [AdventureWorks] GO ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE [AdventureWorks] GO ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetail] REORGANIZE WITH ( LOB_COMPACTION = ON )
Rebuild Index Task
The Rebuild Index Task aims at eliminating fragmentation by reorganizing all the table indexes in the database. This task is particularly good for ensuring that query performance and application response do not degrade. Therefore, when SQL is called on to conduct index scans and seeks, it operates at its full potential. In addition, this task optimizes the distribution of data and free space on the index pages, which allows for growth to take place faster.
The two rebuild index free space options consist of the following:
Reorganize Pages with the Default Amount of Free Space—Drop the indexes on the tables in the database and re-create them with the fill factor that was specified when the indexes were created.
Change Free Space per Page Percentage To—Drop the indexes on the tables in the database and re-create them with a new, automatically calculated fill factor, thereby reserving the specified amount of free space on the index pages. The higher the percentage, the more free space is reserved on the index pages, and the larger the index grows. Valid values are from 0 through 100.
The rebuild index task advanced options consist of the following:
Sort Results in tempdb—The Sort Results in tempdb is the first Advanced option available in the Rebuild Index Task. This option is comparable to the SORT_IN_TEMPDB option for the index. When this option is enabled, the intermediate results are stored in tempdb during the rebuild of an index.
Keep Index Online While Reindexing—The second advanced option allows users to access the underlying table, clustered index data, and the associated indexes during the index rebuild operation.
Armed with the knowledge of what the Rebuild Index Task can do, use the following information to gain some hands-on experience. Use the Rebuild Index syntax that follows to rebuild indexes for the AdventureWorks [Sales]. [SalesOrderDetail] table. The option to Reorganize Pages Using the Default Amount of Free Space has been selected. This example will also sort results in tempdb and keep the index online while reindexing.
USE [AdventureWorks] GO ALTER INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON ) GO USE [AdventureWorks] GO ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON ) GO USE [AdventureWorks] GO ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetail] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON )
Update Statistics Task
The Update Statistics Task ensures the data in the tables and indexes on one or more SQL Server databases are up to date by resampling the distribution statistics of each index on user tables.
There are numerous choices available to customize this task. Each of the options is explained next:
Databases—First select the databases that will be impacted by this task. The choices range from All Databases, System Databases, or User Databases and These Databases.
Object—After the databases are selected, decide in the Objects box whether to display both tables and views or only one of these options.
Selection—Choose the tables or indexes that will be impacted. If the Tables and Views option was selected in the Objects box, this box will be unavailable.
Update—The Update box offers three choices. Select All existing statistics if you need to update both columns and indexes. Select Column statistics if you need to update only column statistics, and select Index Statistics if you need to update only index statistics.
Scan Type—The Scan Type section allows you to update statistics based on a Full Scan or by entering a Sample By value. The Sample By values can be either a percentage or a specific number of rows.
The syntax to update statistics on the AdventureWorks [Sales]. [SalesOrderDetail] table with the advanced options to update all existing statistics and conduct a full scan is as follows:
use [AdventureWorks] GO UPDATE STATISTICS [Sales].[SalesOrderDetail] WITH FULLSCAN
History Cleanup Task
The History Cleanup Task offers organizations the perfect opportunity to remove historical data in a few simple steps. You can delete several types of data using this task. The following explains the options associated with this task.
Historical Data to Be Deleted—Use the Maintenance Plan Wizard to purge several types of data, including Backup and Restore history, SQL Server Agent Job history, and Maintenance Plan history.
Remove Historical Data Older Than—Use the wizard also to select the age of the data you want to delete. For example, you can choose to periodically remove older data based on daily, weekly, monthly, and yearly increments.
When the History Cleanup Task is complete, you can save a report to a text file or email the report to an operator by clicking Next. The Select Report Options screen is invoked and you must enable the check box Write a Report to a Text File, and then the storage location of the report by specifying the file and folder location.
The following TSQL example removes historical data older than four weeks for the following items: Backup and Restore history, SQL Server Agent Job history, and Maintenance Plan history.
declare @dt datetime select @dt = cast(N'2006-12-28T09:26:24' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt GO EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2006-12-28T09:26:24' GO EXECUTE msdb..sp_maintplan_delete_log null, null,'2006-12-28T09:26:24'