启用和禁用数据保留策略Enable and disable data retention policies

本主题说明如何为数据库和表启用和禁用数据保留策略。This topic describes how to enable and disable data retention policies for a database and a table.

为数据库启用数据保留Enable data retention for a database

下面的示例演示如何使用 Alter Database 来启用数据保留。The following example shows how to enable data retention by using Alter Database.

ALTER DATABASE [<DatabaseName>] SET DATA_RETENTION  ON;

检查是否已为数据库启用了数据保留Check if data retention is enabled for a database

以下命令可用于检查是否已为数据库启用了数据保留The following command can be used to check if data retention is enabled for a database

SELECT is_data_retention_enabled, name
FROM sys.databases;

为表启用数据保留Enable data retention for a table

必须为要自动清理其数据的每个表启用数据保留。Data Retention must be enabled for each table for which you want data to be automatically purged. 如果为数据库和表启用了数据保留,后台系统任务会定期扫描表,以识别并删除任何已过时的行。When Data Retention is enabled on the database and the table, a background system task will periodically scan the table to identify and delete any obsolete (aged) rows. 可以在表创建过程中使用 Create Table 或使用 Alter Table 来对表启用数据保留。Data Retention can be enabled on a table either during table creation using Create Table or by using Alter Table.

下面的示例演示如何使用 Create Table 来为表启用数据保留。The following example shows how to enable data retention for a table by using Create Table.

CREATE TABLE [dbo].[data_retention_table] 
(
[dbdatetime2] datetime2(7), 
[product_code] int, 
[value] char(10),  
CONSTRAINT [pk_current_data_retention_table] PRIMARY KEY CLUSTERED ([product_code])
) WITH (DATA_DELETION = ON ( FILTER_COLUMN = [dbdatetime2], RETENTION_PERIOD = 1 day ) )

create table 命令的 WITH (DATA_DELETION = ON ( FILTER_COLUMN = [dbdatetime2], RETENTION_PERIOD = 1 day ) ) 部分设置表的数据保留。The WITH (DATA_DELETION = ON ( FILTER_COLUMN = [dbdatetime2], RETENTION_PERIOD = 1 day ) ) part of the create table command sets the data retention on the table. 该命令使用以下必需的参数The command uses the following required parameters

  • DATA_DELETION - 指示数据保留是 ON 还是 OFF。DATA_DELETION - Indicates whether data retention is ON or OFF.
  • FILTER_COLUMN - 表中列的名称,用于确定行是否已过时。FILTER_COLUMN - Name on the column in the table, which will be used to ascertain if the rows are obsolete or not. 筛选列只能是具有以下数据类型的列The filter column can only be a column with the following data types
    • DateDate
    • SmallDateTimeSmallDateTime
    • DateTimeDateTime
    • DateTime2DateTime2
    • DateTimeOffsetDateTimeOffset
  • RETENTION_PERIOD - 一个整数值,后跟一个单位描述符。RETENTION_PERIOD - An integer value followed by a unit descriptor. 允许的单位为 DAY、DAYS、WEEK、WEEKS、MONTH、MONTHS、YEAR 和 YEARS。The allowed units are DAY, DAYS, WEEK, WEEKS, MONTH, MONTHS, YEAR and YEARS.

下面的示例演示如何使用 Alter Table 来为表启用数据保留。The following example shows how to enable data retention for table by using Alter Table.

Alter Table [dbo].[data_retention_table]
SET (DATA_DELETION = On (FILTER_COLUMN = [timestamp], RETENTION_PERIOD = 1 day))

检查是否已为表启用了数据保留Check if data retention is enabled for a table

以下命令可用于检查已启用数据保留的表The following command can be used to check the tables for which data retention is enabled

select name, data_retention_period, data_retention_period_unit from sys.tables

如果 data_retention_period 值 = -1 且 data_retention_period_unit 为 INFINITE,则表示未对表设置数据保留。A value of data_retention_period = -1 and data_retention_period_unit as INFINITE, indicates that data retention is not set on the table.

以下查询可用于标识用作数据保留的 filter_column 的列。The following query can be used to identify the column used as the filter_column for data retention.

Select name from sys.columns
where is_data_deletion_filter_column =1 
and object_id = object_id(N'dbo.data_retention_table', N'U')

关联 DB 和表数据保留设置Corelating DB and table data retention settings

数据库和表中的数据保留设置结合使用,以确定是否在表上运行对过期行的自动清理。The data retention setting on the database and the table, are used in conjunction to determine if autocleanup for aged rows will run on the tables or not.

数据库选项Database Option 表选项Table Option 行为Behavior
OFFOFF OFFOFF 已禁用数据保留策略,且已禁用对过时记录的自动和手动清理。Data Retention policy is disabled and both auto and manual cleanup of aged records is disabled.
OFFOFF ONON 已为表启用数据保留策略。Data Retention policy is enabled for the table. 已禁用过时记录的自动清理,但手动清理方法可用于清理过时的记录。Auto cleanup of obsolete records is disabled, however manual cleanup method can be used to cleanup obsolete records.
ONON OFFOFF 已在数据库级别启用数据保留策略。Data Retention policy is enabled at the database level. 但是,由于在表级别禁用了该选项,因此不存在基于保留的过期行清理。However since the option is disabled at the table level, there is no retention-based cleanup of aged rows.
ONON ONON 已同时为数据库和表启用数据保留策略。Data Retention policy is enabled for both the database and tables. 已启用过时记录的自动清理。Automatic cleanup of obsolete records is enabled.

禁用表上的数据保留Disable data retention on a table

可以使用 Alter Table 来禁用表上的数据保留。Data Retention can be disabled on a table by using Alter Table. 以下命令可用于禁用表上的数据保留。The following command can be used to disable data retention on a table.

Alter Table [dbo].[data_retention_table]
Set (DATA_DELETION = OFF)

禁用数据库上的数据保留Disable data retention on a database

可以使用 Alter Database 来禁用表上的数据保留。Data Retention can be disabled on a table by using Alter Database. 以下命令可用于禁用数据库上的数据保留。The following command can be used to disable data retention on a database.

ALTER DATABASE <DatabaseName> SET DATA_RETENTION  OFF;

后续步骤Next steps