SC orchestrator: DB Query To Stop Running Runbooks in SCORCH and Clear Their Orphans

  • Article

How To Clear All Orphaned Runbooks With A Single Query

Anyone, who has been working with System Center Orchestrator (SCORCH) long enough, knows the frustration with orphaned runbooks and the log-jam they can create.  There are several great articles available to show how to clear then, but I wanted to come up with something that would get the job done in a single step.  So I came up with the query that finds all running runbooks, stops them and clears their orphans.  I'm, by no means, an expert DBA, but the code works. 

Requirements

Couple things you'll have to make sure you have:

  • Access to your SCORCH DB Instance via SQL Management Studio (or your favorite means of executing queries)
  • DB Rights to execute the query

Stop & Clear SQL Query

  1. Copy and past the query below into Management Studio
  2. Make sure the DB references in the query match the name of your Orchestrator database; I have it marked in the code.  This is the default name, so most should be fine. 
  3. Execute the query
  4. Start your runbooks back up and you're done!!
01.USE Orchestrator -- Make sure this matches the name of your Orchestrator DB
02.GO
03.Declare @polID nvarchar(40) 
04.Declare @SeqNum bigint
05.Declare @getid CURSOR
06.Declare @getid2 CURSOR
07. 
08.Set @getid = cursor for
09. 
10.SELECT pin.PolicyID, ppq.SeqNumber 
11.  FROM [Orchestrator].[dbo].[POLICY_PUBLISH_QUEUE] ppq  -- Verify DB Name
12.  LEFT JOIN Orchestrator.dbo.POLICYINSTANCES pin on  pin.PolicyID = ppq.PolicyID --Verify DB Name
13.  LEFT JOIN Orchestrator.dbo.POLICIES pin2 on  pin2.UNiqueID = ppq.PolicyID --Verify DB Name
14. Where AssignedActionServer IS NOT NULL
15.and TimeEnded IS NULL
16. 
17. 
18.Set @getid2 = cursor for
19.SELECT pin.PolicyID, ppq.SeqNumber 
20.  FROM [Orchestrator].[dbo].[POLICY_PUBLISH_QUEUE] ppq   --Verify DB Name
21.  LEFT JOIN Orchestrator.dbo.POLICYINSTANCES pin on  pin.PolicyID = ppq.PolicyID --Verify DB Name
22.  LEFT JOIN Orchestrator.dbo.POLICIES pin2 on  pin2.UNiqueID = ppq.PolicyID --Verify DB Name
23. Where AssignedActionServer IS NOT NULL
24.and TimeEnded IS NULL
25. 
26. 
27.OPEN @getid
28.Fetch next
29.From @getid into @PolID, @SeqNum
30.WHile @@FETCH_STATUS = 0
31.Begin
32.    EXEC [dbo].[sp_UnpublishPolicyRequest] @PolID, @SeqNum
33.    Fetch next
34.    From @getid into @PolID, @SeqNum
35.End
36. 
37.DECLARE @return_value int EXEC  @return_value = [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].[ClearOrphanedRunbookInstances] SELECT  'Return Value' = @return_value
38. 
39.CLose @getid
40.Deallocate @getid
41. 
42.OPEN @getid2
43.   
44.Fetch Next 
45.From @getid2 into @PolID, @SeqNum
46.WHile @@FETCH_STATUS = 0
47.Begin
48.    EXEC [dbo].[sp_PublishPolicy] @PolID
49.    Fetch next
50.    From @getid2 into @PolID, @SeqNum
51.End
52.CLose @getid2
53. 
54.Deallocate @getid2

Additional Information

I also found this article that shows how to create a stored procedure for simplifying stopping all running runbooks.

Hope This Helps!

References