sp_syspolicy_delete_policy_execution_history (Transact-SQL)sp_syspolicy_delete_policy_execution_history (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

在基于策略的管理中删除策略的执行历史记录。Deletes execution history for policies in Policy-Based Management. 您可以使用此存储过程删除特定策略或所有策略的执行历史记录,也可以删除特定日期前的执行历史记录。You can use this stored procedure to delete execution history for a specific policy or for all policies, and to delete execution history before a specific date.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions


sp_syspolicy_delete_policy_execution_history [ @policy_id = ] policy_id ]  
    [ , [ @oldest_date = ] 'oldest_date' ]  


[ @policy_id = ] policy_id 要删除其执行历史记录的策略的标识符。[ @policy_id = ] policy_id Is the identifier of the policy for which you want to delete the execution history. policy_idint,并且是必需的。policy_id is int, and is required. 可以为 NULL。Can be NULL.

[ @oldest_date = ] 'oldest_date' 是要保留策略执行历史记录的最早日期。[ @oldest_date = ] 'oldest_date' Is the oldest date for which you want to keep policy execution history. 先于此日期的所有执行历史记录都将被删除。Any execution history earlier than this date is deleted. oldest_datedatetime,则是必需的。oldest_date is datetime, and is required. 可以为 NULL。Can be NULL.

返回代码值Return Code Values

0 (成功) 或 1 (失败) 0 (success) or 1 (failure)


您必须在 msdb 系统数据库的上下文中运行 sp_syspolicy_delete_policy_execution_history。You must run sp_syspolicy_delete_policy_execution_history in the context of the msdb system database.

若要获取 policy_id 的值以及查看执行历史记录日期,可以使用以下查询:To obtain values for policy_id, and to view execution history dates, you can use the following query:

SELECT a.name AS N'policy_name', b.policy_id, b.start_date, b.end_date  
FROM msdb.dbo.syspolicy_policies AS a   
INNER JOIN msdb.dbo.syspolicy_policy_execution_history AS b  
ON a.policy_id = b.policy_id  

如果您为一个或两个值指定 NULL,则下面的行为适用:The following behavior applies if you specify NULL for one or both values:

  • 若要删除所有策略执行历史记录,请为 policy_idoldest_date 指定 NULL。To delete all policy execution history, specify NULL for both policy_id and for oldest_date.

  • 若要删除特定策略的所有策略执行历史记录,请指定 policy_id 的策略标识符,并将 NULL 指定为 oldest_dateTo delete all policy execution history for a specific policy, specify a policy identifier for policy_id, and specify NULL as oldest_date.

  • 若要删除特定日期前的所有策略的策略执行历史记录,请为 policy_id 指定 NULL,并为 oldest_date 指定日期。To delete policy execution history for all policies before a specific date, specify NULL for policy_id, and specify a date for oldest_date.

若要将策略执行历史记录存档,您可以在对象资源管理器中打开策略历史记录日志,然后将执行历史记录导出到某一文件中。To archive policy execution history, you can open the Policy History log in Object Explorer and export the execution history to a file. 若要访问策略历史记录日志,请展开 " 管理",右键单击 " 策略管理",然后单击 " 查看历史记录"。To access the Policy History log, expand Management, right-click Policy Management, and then click View History.


要求具有 PolicyAdministratorRole 固定数据库角色的成员身份。Requires membership in the PolicyAdministratorRole fixed database role.


可能的凭据提升:具有 PolicyAdministratorRole 角色的用户可以创建服务器触发器并计划策略执行,这可能会影响数据库引擎Database Engine实例的正常运行。Possible elevation of credentials: Users in the PolicyAdministratorRole role can create server triggers and schedule policy executions that can affect the operation of the instance of the 数据库引擎Database Engine. 例如,PolicyAdministratorRole 角色中的用户可以创建一个策略,它可能会禁止在数据库引擎Database Engine中创建大多数对象。For example, users in the PolicyAdministratorRole role can create a policy that can prevent most objects from being created in the 数据库引擎Database Engine. 由于这种可能的凭据提升,只应将 PolicyAdministratorRole 角色授予受信任的用户,以控制的配置 数据库引擎Database EngineBecause of this possible elevation of credentials, the PolicyAdministratorRole role should be granted only to users who are trusted with controlling the configuration of the 数据库引擎Database Engine.


下面的示例为 ID 为 7 的策略删除特定日期前的策略执行历史记录。The following example deletes policy execution history before a specific date for a policy with an ID of 7.

EXEC msdb.dbo.sp_syspolicy_delete_policy_execution_history @policy_id = 7  
, @oldest_date = '2009-02-16 16:00:00.000';  

另请参阅See Also

基于策略的管理存储过程 (Transact-sql) Policy-Based Management Stored Procedures (Transact-SQL)
sp_syspolicy_set_config_history_retention (Transact-sql) sp_syspolicy_set_config_history_retention (Transact-SQL)
sp_syspolicy_purge_history (Transact-sql)sp_syspolicy_purge_history (Transact-SQL)