SQL Server 2005 Integration Services – Part 48

In the most recent installments of our series dedicated to SQL Server 2005
Integration Services, we started exploring database maintenance functionality.
Our interest in this subject (within the context of our series) results from
the fact that SSIS Designer of Business Intelligence Development Studio
includes a number of Control Flow tasks that belong to this category. However,
as we have demonstrated, an equivalent set of features is also available
through Maintenance Plan Wizard and Maintenance Plan Designer components of SQL
Server Management Studio. In this article, we will focus on the second one of
these utilities, describing its characteristics in more detail.

Maintenance Plan Designer gives you access to a number of tasks in the
manner similar to the one implemented in SQL Server Business Intelligence
Development Studio. Within its Toolbox, you will find a total of 11 tasks,
which allow you to perform a variety of database management activities (note
though that SQL Server 2005-based maintenance plans only run against databases
at compatibility level 80 or higher). We will provide a brief overview of each
of them, focusing on their recent improvements (introduced in SQL Server 2005 SP2)
and intricacies that you should be aware of (for general information regarding
their features you can refer to individual articles
of the "SQL 2005 Maintenance Wizard" series published on the Database
Journal Web site):

  • Back Up Database task allows you to perform backup of one or more
    databases using Full, Differential, and Transaction Log options (which,
    incidentally, are listed as three separate entries on the Select Maintenance
    Task page of Maintenance Plan wizard). If you installed SQL Server 2005 SP2,
    you will be able to specify an expiration date of a backup set (either as the
    number of days following its creation or a specific date, which corresponds to
    RETAINDAYS and EXPIREDATE T-SQL parameters of the BACKUP DATABASE statement).
    Unfortunately, this innovation appears to be of a limited practical use at this
    point (at least as long as your intention was to rely exclusively on predefined
    Maintenance Plans tasks) since there seems to be no graphical interface for
    setting the NOSKIP option that verifies the expiration status of backup sets. (For
    more information regarding interdependencies between SKIP/NOSKIP and
    INIT/NOINIT T-SQL arguments, refer to SQL Server 2005
    Books Online
    ). Another improvement (slightly more useful) is automatic
    restriction of available backup types for a target database based on its
    recovery model.

  • Maintenance Cleanup task is frequently paired up with the Back Up
    Database task in order to delete outdated backups files and maintenance plan
    reports (based on file extensions that you specify). This task was
    conspicuously missing from pre-SP2 versions of SQL Server 2005 Maintenance Plan
    Wizard (especially since it was available in its SQL Server 2000 rendition).
    However, the new implementation, while filling this void, introduced a number
    of unexpected issues. Its initial release (which had been posted on the Microsoft
    Web site until March 05, 2007) was not backwards compatible and in many cases
    resulted in deletion of backup files sooner or later than intended. The
    confusion apparently was caused by newly introduced hourly intervals, which
    were incorrectly interpreted when modified and processed by a mix of pre- and
    post-SP2 utilities (for more details regarding this issue and its resolution,
    refer to Microsoft Knowledge Base article 933508). In addition,
    while SQL Server 2005 SP1 provided the ability to delete aged maintenance plan
    files from subfolders of a target folder (this feature is currently
    configurable with the "Include first-level subfolders" checkbox in the
    Maintenance Cleanup Task editor dialog box), it failed to address the inability
    to delete files based on their extension. (Following SP2 installation, the task
    can remove backup files, but does not purge reports). Until this flaw is fixed,
    you will need to resort to workarounds (such as, incorporating the master.dbo.xp_delete_file
    extended stored procedure with an appropriate set of parameters into Execute
    T-SQL Statement Task).

  • History Cleanup Task serves a role similar to the Maintenance
    Cleanup task, although instead of dealing with files created by maintenance
    plans, it removes outdated entries from a number of tables in the msdb database
    (backupfile, backupfilegroup, backupmediafamily, backupmediaset, backupset,
    restorefile, restorefilegroup, and restorehistory). Note that these entries
    might be generated not only by maintenance plans, but can also appear as the
    result of performing standard backups and restores, or executing SQL Server
    Agent jobs. The scope of cleanup is determined by the content of "Remove
    historical data older than" property that, starting with SQL Server 2005
    SP2, can have its value expressed in hours, in addition to days, weeks, months,
    and years (which have been available earlier).

  • Check Database Integrity task is designed as a graphical interface
    to DBCC CHECKDB, which, in turn, incorporates features encompassed by DBCC
    procedures, the task performs a variety of tests, such as verifying allocation
    of all database pages, confirming structural integrity of tables and indexed
    views, and checking consistency between system metadata tables (any issues
    discovered through these actions would need to be subsequently addressed
    through proper administrative actions). Note that, depending on the version and
    build number of your SQL Server 2005 installations (refer to the Knowledge Base
    article 321185 to find
    out how to determine their values) and the way you structure your maintenance
    plans (in particular, when verifying integrity of multiple databases within the
    same task), you might be running into problems outlined in the Microsoft
    Knowledge Base articles 934459
    or 934458. (Both detail
    the actual issues and point to a patch posted on the Microsoft Download Center
    that fixes them).

  • Rebuild Index Task is used to improve performance of operations
    that involve indexes by recreating them a specific amount of free space on the
    leaf-level pages according to the value of its Fill Factor parameter
    (compacting and reordering them in the process), which decreases index
    fragmentation and lowers the amount of page splits during subsequent data
    inserts/modifications. SQL Server 2005 Enterprise Edition is capable of
    performing this operation online, without restricting access to the underlying
    table, or its clustered and nonclustered indexes (to take advantage of this
    feature, turn on the "Keep index online while reindexing" advanced
    option in the task Editor dialog box). There are two other advanced options
    that, while present in pre-Service Pack 2 instances, no longer appear following
    its installation. The first, labeled "Pad index," controls the amount
    of free space in intermediate-level pages of the index that is being rebuilt
    (based on the value of the fill factor parameter). The second one, called
    "Ignore duplicate keys," dictates behavior triggered by an attempt to
    add a duplicate into a table with unique index, as part of a multi-row
    transaction. With this option enabled, such a scenario will generate a warning
    but permit non-violating rows to be inserted; keeping it disabled (which is the
    default) will result in an error and rollback of the entire INSERT transaction.
    Apparently, Microsoft decided to remove these two settings due to a design
    flaw, which resulted in applying their values to all indexes across target
    objects, regardless on their previous values (i.e. following the task
    execution, all of the target indexes were ending up with the same PAD_INDEX and
    IGNORE_DUP_KEY settings). Since this frequently was not the intended outcome,
    the new default simply reapplies original index options during its recreation (hence,
    there is no need to include it in the list of configurable options).

  • Reorganize Index Task (based on the ALTER INDEX REORGANIZE T-SQL
    statement) provides a less intrusive (and automatically invoked in online
    mode), but also less powerful alternative to rebuilding an index. It allows you
    to perform online reordering and compacting index leaf-level pages (without
    reapplying their fill factor parameter), leading to their defragmentation,
    which, in turn, results in improved performance. In general, Microsoft
    guidelines recommend its use in situations when the index fragmentation level
    (which can be determined by examining the outcome of the sys.dm_db_index_physical_stats
    function) is below 30% (you should consider rebuilding an index otherwise).

  • Shrink Database Task (which leverages DBCC SHRINKDATABASE
    statement) is typically used to reduce the size of a files containing a target
    database and its logs (e.g. following truncating or dropping a large table),
    which is accomplished by compacting its content and lowering the amount of
    extra free space they contain (based on the value of the "Amount of free
    space to remain after shrink" task property, representing the percentage
    of free space that is left after the task completes). Its effectiveness depends
    on having a sufficient amount of free space in the database (to establish its
    value, use sp_spaceused stored procedure), as well as such factors as minimum
    database size. Note that you will not see any changes in file sizes if you
    selected the "Retain freed space in database files" option
    (corresponding to the NOTRUNCATE argument of DBCC SHRINKDATABASE), which only
    rearranges allocated pages of a database data file (but not logs) to make them
    contiguous (by moving their content to unallocated pages in the beginning of
    the file) but without returning the resulting free space at the end of the file
    to the operating system.

  • Update Statistics Task recalculates information that represents
    data distribution in tables and indexes, which is then used by query optimizer
    to determine the most efficient approach when processing T-SQL statements. The
    recalculation process can be based either on a full scan (which might take a considerable
    amount of time with larger objects but provides the best accuracy) or by
    analyzing a sample of arbitrary size (which saves processing time necessary to
    generate statistical data but at the risk of potential inaccuracies). Starting
    with SQL Server 2005 SP2, an updated task editor interface gives you the
    ability to specify scan type (you can choose between full scan and a
    sample-based one, with its size determined by either a percentage of the entire
    data set or a number of rows).

  • Execute T-SQL Statement Task provides the ability to launch
    custom T-SQL statements, which functionality is not covered by any of existing
    maintenance plan tasks. This allows you to supplement the outcome of
    Maintenance Plan wizard (which, by the way, does not include it on its Select
    Maintenance Task page).

  • Notify Operator Task, which leverages Database Mail functionality
    (for more information on this subject, refer to Books Online),
    is limited strictly to e-mail notifications and requires an existing SQL Server
    Agent operator entry with its e-mail address property defined. As with the
    Execute T-SQL Statement task, this feature is not available directly from the Maintenance
    Plan wizard.

  • Execute SQL Server Agent Job task allows you to incorporate
    existing jobs into maintenance plan workflow (you can simply pick the ones you
    are interested in directly from the task editor dialog box).

In the next article of our series, we will take a closer look at Maintenance
Plan tasks available from within SSIS Designer of Business Intelligence
Development Studio and compare them with equivalent functionality implemented
in SQL Server Management Studio.


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles