Answered by:
Question regarding orphan policy instances and jumping to different action servers

Question
-
Hello.
Many of our policies are using "Monitor Date/Time" and are scheduled to run every X minutes, so these policies are always running. After an action server is rebooted due to patching we are seeing a couple things:
1) In the Ops Console we see dozens of "instances" still running although all of those (except 1) are orphaned instances. Aside from manually removing these in the OIS client, is there another way to clean them up or prevent them from occurring in the first place?
2) We have a few "special purpose" Action Servers that are living in different domains. We use these action servers for specific policies that are touching those different domains. They are the lowest in the "Priority" and I figured we have enough capacity (3 normal action servers, each with ability of 50 concurrent policies) to handle all of our running policies. However when the above issue happens, say after patching, our normal production policies are moving over to these special actions servers and failing. Is there any way to mark these special action servers as special, instead of manually marking all of our workflows on those production action servers? Also when we see those dozens of orphan instances running, is that counting towards the "max concurrent policies" that a server is allowed to run, thereby causing it to fail over to the standby action servers faster?
Thanks.
-RR
Monday, February 27, 2012 7:06 PM
Answers
-
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]
ASBEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @Count intPRINT '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] = 0IF @@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 #ObjectInstanceListSELECT
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
ENDPRINT ' 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
ENDPRINT ' Deleting policy instances...'
DELETE FROM [POLICYINSTANCES]
WHERE
UniqueID IN (SELECT InstanceID FROM #PolicyInstanceList)
SELECT @Error = @@ERROR, @Count = @@ROWCOUNTIF @Error = 0 PRINT ' '+CONVERT(varchar, @Count)+' row(s) deleted.'
ELSE
BEGIN
PRINT ' ERROR: Error occurred deleting policy instances.'
GOTO ERROR
ENDPRINT 'COMPLETED: Deleting instances.'
GOTO FINALLYERROR:
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 #ObjectInstanceListPRINT '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
- Marked as answer by Yog LiModerator Monday, March 5, 2012 9:13 AM
Tuesday, February 28, 2012 5:52 AM
All replies
-
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]
ASBEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @Count intPRINT '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] = 0IF @@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 #ObjectInstanceListSELECT
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
ENDPRINT ' 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
ENDPRINT ' Deleting policy instances...'
DELETE FROM [POLICYINSTANCES]
WHERE
UniqueID IN (SELECT InstanceID FROM #PolicyInstanceList)
SELECT @Error = @@ERROR, @Count = @@ROWCOUNTIF @Error = 0 PRINT ' '+CONVERT(varchar, @Count)+' row(s) deleted.'
ELSE
BEGIN
PRINT ' ERROR: Error occurred deleting policy instances.'
GOTO ERROR
ENDPRINT 'COMPLETED: Deleting instances.'
GOTO FINALLYERROR:
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 #ObjectInstanceListPRINT '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
- Marked as answer by Yog LiModerator Monday, March 5, 2012 9:13 AM
Tuesday, February 28, 2012 5:52 AM -
Hi Stefan.
We implemented this for Opalis 6.3 and it worked well. Now that we've upgraded to Orchestrator 2012 SP1 we're seeing the same orphaned records. Does this SP still work for Orchestrator?
Wednesday, July 31, 2013 4:21 PM -
Hi Reza,
in Orchestrator there's a Stored Procedure now for this: Microsoft.SystemCenter.Orchestrator.Runtime.Internal.ClearOrphanedRunbookInstances (http://technet.microsoft.com/en-us/library/hh403785.aspx):
- Expand Programmability, and then click Stored Procedures.
- Right-click Microsoft.SystemCenter.Orchestrator.Runtime.Internal.ClearOrphanedRunbookInstances to select Execute Stored Procedure.
- In the Execute Procedure dialog box, click OK.
- On the toolbar, click Execute.
Regards,
Stefan
Friday, August 2, 2013 1:59 PM