| title | sp_syspolicy_delete_policy_execution_history (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/03/2017 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.reviewer | |||
| ms.technology | system-objects | ||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | fe651af9-267e-45ec-b4e7-4b0698fb1be3 | ||
| author | VanMSFT | ||
| ms.author | vanto |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]
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 Syntax Conventions
sp_syspolicy_delete_policy_execution_history [ @policy_id = ] policy_id ]
[ , [ @oldest_date = ] 'oldest_date' ]
[ @policy_id = ] policy_id
Is the identifier of the policy for which you want to delete the execution history. policy_id is int, and is required. Can be NULL.
[ @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_date is datetime, and is required. Can be NULL.
0 (success) or 1 (failure)
You must run sp_syspolicy_delete_policy_execution_history in the context of the msdb system database.
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
The following behavior applies if you specify NULL for one or both values:
-
To delete all policy execution history, specify NULL for both policy_id and for oldest_date.
-
To delete all policy execution history for a specific policy, specify a policy identifier for policy_id, and specify NULL as 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.
Requires membership in the PolicyAdministratorRole fixed database role.
Important
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 [!INCLUDEssDE]. For example, users in the PolicyAdministratorRole role can create a policy that can prevent most objects from being created in the [!INCLUDEssDE]. Because of this possible elevation of credentials, the PolicyAdministratorRole role should be granted only to users who are trusted with controlling the configuration of the [!INCLUDEssDE].
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';
GO
Policy-Based Management Stored Procedures (Transact-SQL)
sp_syspolicy_set_config_history_retention (Transact-SQL)
sp_syspolicy_purge_history (Transact-SQL)