SQL Server Agent: Data Aging

Getting Started

Data Aging is the process of removing old data from secondary storage to allow the associated media to be reused for future backups.

You can change the retention of your data based on your needs.

Setting Up the Basic Retention Rule

1. From the CommCell Browser, expand Policies > Storage Policies > storage_policy.

2. Right-click the appropriate storage policy copy, and then click the Properties.

3. In the Copy Properties dialog box, on the Retention tab, under Basic Retention Rule for All Backups, click the Retain For.

   • Enter number of days to retain the data.

   • Enter number of cycles to retain the data.

4. Click OK.

5. In the Confirm Basic Retention dialog box, click Yes.

6. On the ribbon in the CommCell Console, on the Reports tab, click Forecast, and then click Run.

7. The Data Retention Forecast and Compliance report displays the data to be pruned when a data aging job is run.

   Note:

   • To ensure only data intended for aging is actually aged, it is important to identify the data that will be aged based on the retention rules you have configured. Hence, ensure this report includes only the data you intend to age.

     If necessary, fine-tune your rules so that only the intended data is aged.

Running the Data Aging Job

1. From the CommCell Console, right click the CommServe node, click All Tasks > Data Aging.

2. In the Data Aging Options dialog box, in the Job Initiation area, define whether the data aging job runs immediately or if it will be scheduled.

3. Click OK.

   If you chose to run the job immediately, the data aging job starts now.

   If you chose to run the job according to a schedule, the data aging job runs according to the schedule that you defined.

   After data aging job is run, the data will be pruned from the storage.

Extended Retention Rules

Extended retention rules allow you to keep specific full (or synthetic full) backups for an additional period.

Extended retention rules can be used in the following circumstances:

• If you have a single drive tape library

• If you want to create a hierarchical retention scheme (grandfather-father-son tape rotation)

Extended retention rules allow you to define three additional "extended" retention periods for full (or synthetic full) backups. For example:

• You may want to retain your weekly full backups for 30 days.

• You may want to retain your monthly full backup for 90 days.

• You may want to retain your yearly full backup for 365 days.

A backup job will be selected for extended retention based on its start time. For example: If a backup job starts at 11:55 pm on August 31st and ends at 1 am on September 1st, then it will be selected as the last full backup for the month of August and will be picked up for extended retention.

In all other cases, we recommend you use Auxiliary Copy for extended storage since it creates another physical copy of the data, thereby reducing the risk of data loss due to media failure.

Setting Up the Extended Retention Rules

Use the following steps for setting up the extended retention rules:

1. Right-click the storage policy copy and click Properties.

2. Click the Retention tab.

3. Set the basic retention rules by clicking Retain for and entering the number of days and cycles appropriate for your organization.

4. Set the extended retention rules as follows:

   1. Click the For button.

   2. Enter the number of Days Total to retain the backup.

   3. Click the Keep drop-down list, and select the desired backup criteria (for example, Monthly Full, Weekly Full).

   4. Click the Grace Days drop-down list and select the number of days (for example, 2).

      This allows you to consider the additional number of days along with the Extended Retention rule. For example, if the last full backup job fails with in the defined extended retention criteria, then the next full backup job that ran in the specified grace days will be selected for retention.

5. Repeat Step 4 to configure additional extended retention.

6. Click OK.

   

Data Aging for Transaction, Archive, and Logical Log Backups

Log Backups (transaction, archive, or logical logs) are not considered part of the backup cycle. Therefore, storage policy cycle retention parameters do not apply to them.

Retention Rules for Log Backups

Log backups may be chained to data backup operations, which can allow storage policy cycle retention parameters to be applied to them.

The log backups are not aged until the chained data is aged. In addition, the following are considered:

• Logs that need to be copied to secondary copies will not be aged both on primary and non-primary source copy.

• Logs that exist only on one copy will be aged when they are older than the oldest data that is retained based on basic retention criteria.

• Logs that exist on multiple copies will be aged according to copy retention days.

• Logs that exist on multiple copies with the longest retention days will be aged when they are older than the oldest data that is retained based on basic retention criteria.

• Partial, disabled logs will be aged when they are older than the oldest data that is retained based on basic retention criteria.

Pruning All Log Backups by Days Retention Rule

If you want log backups to be aged according to the defined days retention rule for the data, the SQL Chain Check and SQL Log Rule will be skipped and the SQL transaction logs will be pruned for each copy using the days retention rule only. Follow the steps given below to set this up:

1. From the CommCell Console, click the Storage tab.

2. Click the Media Management icon.

3. Click the Data Aging tab.

4. In the Prune All Database Agent Logs Only By Days Retention Rule, type 1 to enable pruning for all database agent logs based on the days retention.

5. Click OK.

   

Data Aging for Stored Procedures

Data Aging for the SQL Server Agent performs the following stored procedures that you may have been manually running on Enterprise Manager. When Data Aging is run, the system ages these histories from the CommServe database and the SQL Server.

• sp_delete_backuphistory

• sp_delete_database_backuphistory

• sp_delete_backup_and_restore_history

SQL Back in Time Restores and Data Aging Rules

When you perform a back in time restore (i.e., restoring to a backup cycle earlier than the current backup cycle), all differential and transaction log backups which were run after the full backup from which the restored data was obtained will not be able to be aged until a new full backup is run. Running a full backup after performing a back in time restore releases the older backups and subsequent log backups for data aging.

Retention Rules for Full Backups on Selective Copies

By default, the Honor SQL Chaining for Full jobs on Selective copy option is set to 1, causing the SQL full backups on a selective copy to retain the logs that are chained to other copies. Full backups are not aged.

When you set this option to 0, the full backups on selective copy are retained by the basic retention criteria, but full backups do not retain the logs that are chained with SQL_CHAIN or SQL_LOG_RULE.

1. From the CommCell Console ribbon, click the Home tab, and then click Control Panel.

2. Under the Storage section, click the Media Management icon.

3. On the Data Aging tab of the Media Management Configuration dialog box, in the Honor SQL Chaining for Full jobs on Selective copy, type 0.

4. Click OK to close the Media Management Configuration dialog box.

Data Aging Rules for Backing Up On-Demand Subclients

Data Aging for On-Demand backup jobs uses days/time, and ignores cycles and extended retention rules, as the determining factor for pruning the data. Therefore, once the retention time criteria has been met, all data (for both data and logs) is pruned that was backed up using the storage policy specified in the Command Line Interface.

An effective storage policy strategy for SQL On-Demand backups is as follows:

• The same storage policy should not be used for regular backups and On-Demand backups.

• The storage policy copy containing logs of On-Demand backups should have a much longer retention time than other storage policies used by regular backups for the same instance. This is to prevent the logs of On-Demand backups from being pruned before the data of regular backups, and allow the database to be fully restored and recovered using the data of old regular backups and logs afterwards.

Enabling MSDB Database Clean-Up

By default, Data Aging jobs do not perform a client-side clean-up of database metadata. However, to ensure that unnecessary data is not left behind, you can either use the system stored procedures mentioned below per SQL instance:

• <sp_delete_backuphistory>

• <sp_delete_database_backuphistory>

• <sp_delete_backup_and_restore_history>

Or enable the client-side clean-up of database metadata process as follows:

1. From the CommCell Browser, expand Client Computers.

2. Right-click the client, and then click Properties.

   The Client Properties dialog box is displayed.

3. Click Advanced.

   The Advanced Client Properties dialog box is displayed.

4. On the Additional Settings tab, click Add.

   The Add Additional Settings dialog box is displayed.

   1. In the Name box, type nDisableMSDBCleanup.

   2. In the Category list, select Cvd.

   3. In the Type list, select INTEGER.

   4. In the Value box, type 0 to enable database clean-up.

   5. Click OK.

5. Click OK.

6. Click OK.