SQL Server Modeling Services Lifetime Services

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

SQL Server Modeling Services uses Lifetime Services to remove data that has a limited lifetime and usefulness. This frees up space for new data and potentially improves performance. Modeling Services uses the SQL Server Agent to schedule jobs that periodically remove data that has exceeded its lifetime policies.

Using Lifetime Policies

Lifetime policies are created by adding rows to the [Repository.Item].[RemovalPolicies] view. This view defines the policy name, the schedule, the removal rule, and a timestamp value that marks the last time the policy ran. The following table shows the column names and values for an example policy in [Repository.Item].[RemovalPolicies].

Column Name	Type	Value
Id	int	2
Name	[Repository.Item].[RemovalPolicyName]	DailyRemoval
Schedule	sysname	Daily
Rule	nvarchar(max)	[TimeStamp] < @lastTimeStamp
LastTimeStamp	timestamp	0x0000000000000000

The preceding example defines a policy that can be applied to multiple tables by using the policy DailyRemoval where DailyRemoval is the value of the Name column. Each table that participates in lifetime services must contain a column that is used in the Rule field of the [Repository.Item].[RemovalPolicies] view to determine what data to delete. In the preceding example, tables that use the DailyRemoval policy must have a column named TimeStamp with a timestamp SQL data type. Note that columns with the timestamp data type are automatically updated with a database-wide incrementing binary(8) value each time the rows are inserted or updated. In the case of updates, changes to the rows will update the corresponding TimeStamp field, keeping the data from being deleted on the next scheduled execution of the removal policy.

After the target tables have been correctly modified to include a column used by the lifetime rule, an extended property named RemoveOldItems must be added to the tables to associate them with the desired policy. The following T-SQL statement connects the removal policy DailyRemoval with the table CustomApp.ApplicationEventData.

execute sys.sp_addextendedproperty
  @name = N'RemoveOldItems', @value = N'DailyRemoval',
  @level0Type = N'Schema', @level0Name = [CustomApp],
  @level1Type = N'Table', @level1Name = [ApplicationEventData];

In a standard rule, like in the DailyRemoval example, the frequency of running the removal policy determines the lifespan of data in the table—all unmodified rows since the last execution of the policy are removed. Modeling Services uses the information specified by the Schedule field of the [Repository.Item].[RemovalPolicies] view to decide how often to execute a specific removal policy. Modeling Services then calls an internal stored procedure, [Repository.Item].[RemoveOldItems], with the name of the policy that should be run. Each time a policy is run, the procedure updates the LastTimeStamp column of the [Repository.Item].[RemovalPolicies] view to the value of the most recent timestamp for the current database. The procedure also assigns the LastTimeStamp value to the @lastTimeStamp parameter for use by any lifetime policy rules. In this way, the example rule of [TimeStamp] < @lastTimeStamp deletes all unmodified rows since the last execution of the policy.

Warning

Do not directly call the [Repository.Item].[RemoveOldItems] stored procedure or manually execute any jobs related to lifetime services, because these are managed by the Modeling Services. The frequency of executing policies controls the data lifespan, so running the same policy twice in succession results in the deletion of all of the rows from the table (in rare circumstances there may be one row left, but all other rows would be gone).

Although the rules for lifetime services provide support for using a timestamp column in conjunction with the supplied @lastTimeStamp parameter, there is flexibility for creatively using other types of rules. For example, a table could have a datetime column named DateInserted that stores the actual insertion date for each row. In that case, the Rule field for the policy in the [Repository.Item].[RemovalPolicies] view could contain the following text:

DATEDIFF(d, [DateInserted], GETDATE()) > 30

In the preceding example, rows that were inserted longer than 30 days earlier are deleted. The frequency of running this rule does not affect the lifespan of the data in the table, because the rule uses a calculation on a datetime column instead of a timestamp column comparison with the LastTimeStamp field.

See Also

Concepts

SQL Server Modeling Services Features
Lifetime Services Tasks (Modeling Services)