Hi Reza,
1) here is a SP originally from Jeff Fanjoy to delete the orphaned instances (http://www.systemcentercentral.com/BlogDetails/tabid/143/IndexID/84345/Default.aspx):
CREATE PROCEDURE [sp_ClearOrphanedPolicyInstances]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @Count int
PRINT 'STARTED: Clearing orphaned policy instances.'
PRINT 'STARTED: Creating policy instance list.'
CREATE TABLE #PolicyInstanceList (InstanceID UNIQUEIDENTIFIER, PolicyID UNIQUEIDENTIFIER)
PRINT ' Identifying policy instances and adding to instance list.'
INSERT INTO #PolicyInstanceList
SELECT pinst.[UniqueID], pinst.[PolicyID]
FROM
[POLICYINSTANCES] AS pinst, [POLICY_REQUEST_HISTORY] AS prq
WHERE
pinst.[PolicyID] = prq.[PolicyID] AND
pinst.[SeqNumber] = prq.[SeqNumber] AND
pinst.[TimeEnded] IS NULL AND
prq.[Active] = 0
IF @@ROWCOUNT = 0
BEGIN
PRINT ' No inactive orphaned policy instances found.'
PRINT 'COMPLETED: Creating policy instance list.'
GOTO FINALLY
END
PRINT 'COMPLETED: Creating policy instance list.'
PRINT 'STARTED: Checking for active policy requests.'
DECLARE @ActiveRequestCount INT
SET @ActiveRequestCount = (
SELECT
COUNT(InstanceID)
FROM #PolicyInstanceList AS pil, [POLICY_REQUEST_HISTORY] AS prq
WHERE
pil.[PolicyID] = prq.[PolicyID] AND prq.[Active] = 1)
IF @ActiveRequestCount = 0 PRINT ' No active policy requests identified.'
ELSE
PRINT ' Active policy requests identified, orphaned instances associated to active requests will not be cleared!'
PRINT 'COMPLETED: Checking for active policy requests.'
PRINT 'STARTED: Creating object instance list.'
CREATE TABLE #ObjectInstanceList (ObjectInstanceID UNIQUEIDENTIFIER)
PRINT ' Identifying object instances and adding to instance list.'
INSERT INTO #ObjectInstanceList
SELECT
oinst.[UniqueID]
FROM
[OBJECTINSTANCES] AS oinst, #PolicyInstanceList AS pinst
WHERE
oinst.[InstanceID] = pinst.[InstanceID]
PRINT 'COMPLETED: Creating object instance list.'
PRINT 'STARTED: Deleting instances.'
PRINT ' Deleting object instance data instances...'
DELETE FROM [OBJECTINSTANCEDATA]
WHERE
ObjectInstanceID IN (SELECT ObjectInstanceID FROM #ObjectInstanceList)
SELECT @Error = @@ERROR, @Count = @@ROWCOUNT
IF @Error = 0 PRINT ' '+CONVERT(varchar, @Count)+' row(s) deleted.'
ELSE
BEGIN
PRINT ' ERROR: Error occurred deleting object instance data instances.'
GOTO ERROR
END
PRINT ' Deleting object instances...'
DELETE FROM [OBJECTINSTANCES]
WHERE
UniqueID IN (SELECT ObjectInstanceID FROM #ObjectInstanceList)
SELECT @Error = @@ERROR, @Count = @@ROWCOUNT
IF @Error = 0 PRINT ' '+CONVERT(varchar, @Count)+' row(s) deleted.'
ELSE
BEGIN
PRINT ' ERROR: Error occurred deleting object instances.'
GOTO ERROR
END
PRINT ' Deleting policy instances...'
DELETE FROM [POLICYINSTANCES]
WHERE
UniqueID IN (SELECT InstanceID FROM #PolicyInstanceList)
SELECT @Error = @@ERROR, @Count = @@ROWCOUNT
IF @Error = 0 PRINT ' '+CONVERT(varchar, @Count)+' row(s) deleted.'
ELSE
BEGIN
PRINT ' ERROR: Error occurred deleting policy instances.'
GOTO ERROR
END
PRINT 'COMPLETED: Deleting instances.'
GOTO FINALLY
ERROR:
PRINT 'ERROR: Error occurred while clearing orphaned policy instances.'
FINALLY:
IF object_id('tempdb..#PolicyInstanceList') IS NOT NULL DROP TABLE #PolicyInstanceList
IF object_id('tempdb..#ObjectInstanceList') IS NOT NULL DROP TABLE #ObjectInstanceList
PRINT 'COMPLETED: Clearing orphaned policy instances.'
END
2) You can specify the other ActionServers except the "specical ones" in the properties of the other policies.
Best regards,
Stefan
