sp_syspolicy_delete_policy_execution_history (Transact-SQL)

Článek
05/16/2013

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.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

[ @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.

Return Code Values

0 (success) or 1 (failure)

Remarks

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.

Permissions

Requires membership in the PolicyAdministratorRole fixed database role.

Security Note
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. For example, users in the PolicyAdministratorRole role can create a policy that can prevent most objects from being created in the Database Engine. 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 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. For example, users in the PolicyAdministratorRole role can create a policy that can prevent most objects from being created in the Database Engine. 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 Database Engine.

Examples

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

Sdílet prostřednictvím