Basic SQL Maintenance Plan tasks:

1. Daily full backup, delete the old backups
Could use the SQL scripts or Quest Tool

2. Differential backup
Could use the SQL scripts or Quest Tool


3. Transaction backup (only for Full recovery model and need to be done after a full backup is performed).
Could use the SQL scripts or Quest Tool

4. Index Defrag
This feature will be removed in the next version of MS SQL server. It is not supported for use on system tables.

It defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes. therefore improving index-scanning performance.

DBCC INDEXDEFRAG shuffles index leaf pages in place. Therefore, if an index is interleaved with other indexes on disk, running

DBCC_INDEXDEFRAG against that index does not make all leaf pages in the index contiguous, To improve the clustering of pages, rebuild the index.

When it runs, index defragmentation occurs serially, which menas that the operation on a single index is performed using a single thread. No parallelism occurs. operations on multiple indexes from the same DBCC INDEXDEFRAG statements are performed on one index at a time.

It is an online operation. It does not hold locks long term. regardless of the database recovery model setting, it is always fully logged.

5. Index Rebuild -- to recreate the indexes on the tables in the database with a new fill factor. The fill factor determines the amount of empty space on each page in the index, to accommodate future expansion. As data is added to the table, the free space fills because the fill factor is not maintained. Reorganizing data and index pages can re-establish the free space. SQL 2005 or higheruse the ALTER INDEX. SQL 2000 use DBCC DBREINDEX.

It will 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 value are from 0 through 100.

Use the SORT_IN_TEMPDB option, which determines where the intermediate sort results, generated during index creation, are temporarily stored. If a sort opertion is not required, or if the sort can be performanced in memeroy, this option is ignored.

Use the ONLINE option which allows users to access the underlying table or clustered index data and any associated nonclustered indexes during index opertions.

6. Cycle SQL error log
make the SQL error log size under control so it can be opened and view through SSMS. EXEC master.sys.sp_cycle_errorlog;

7. Update statistics
The Database Engine keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index or indexes to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.

If lots of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated by using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

To see when the statistics were last updated, use the STATS_DATE function.

8. DBCC CheckDB

If the compatibility level is 100 (SQL Server 2008) or higher:

Unless NOINDEX is specified, DBCC CHECKDB performs both physical and logical consistency checks on a single table and on all its

nonclustered indexes. However, on XML indexes, spatial indexes, and indexed views only physical consistency checks are performed by default.

If WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on an indexed view, XML indexes, and spatial indexes, here present. By default, physical consistency checks are performed before the logical consistency checks. If NOINDEX is also specified, only the logical checks are performed.
These logical consistency checks cross check the internal index table of the index object with the user table that it is referencing. To find outlying rows, an internal query is constructed to perform a full intersection of the internal and user tables. Running this query can have a very high effect on performance, and its progress cannot be tracked. Therefore, we recommend that you specify WITH EXTENDED_LOGICAL_CHECKS only if you suspect index issues that are unrelated to physical corruption, or if page-level checksums have been turned off and you suspect column-level hardware corruption.

If the index is a filtered index, DBCC CHECKDB performs consistency checks to verify that the index entries satisfy the filter predicate.

If the compatibility level is 90 or less, unless NOINDEX is specified, DBCC CHECKDB performs both physical and logical

consistency checks on a single table or indexed view and on all its nonclustered and XML indexes. Spatial indexes are not supported.

9.00.3042 is SQL 2005 SP2 version.