I just couldn’t resist having a little fun with the title of the article…but today’s topic is a little more serious. In the previous articles,IPs and Activities and How Those are Represented in the Database and SQL Sequel: More Useful Orchestrator Database Queries, I talked about some queries to get information out of the Orchestrator Database. In this article, I’m going to talk about actually removing data, so here goes the big scary warning box:

Note from the legal department: The process described here is not officially supported by Microsoft and are provided only as an example to the community. This process makes modifications to your SQL Database for Orchestrator and will remove data from the database!

Neither I nor Microsoft, nor any other person, animal, vegetable or mineral assumes responsibility for the process demonstrated here. USE AT YOUR OWN RISK!

Ok, now that’s over…on to the fun stuff!

In Opalis 6.3, there was a stored procedure named “sp_StopAllRequests” (and a similar one named “sp_StopAllRequestsForPolicy”). Using this stored procedure is something usually recommended only by support personnel when something goes really wrong with your system and you get a huge number of jobs lined up in the queue. A perfect example came over the email list today (which is what spurred this blog post). Let’s say you have a runbook set to monitor a folder and trigger a job each time a new file appears in the folder. Someone accidentally dropped 20GB of files in the folder, causing thousands of jobs to get queued up.

In this situation, you could stop the Runbook Server service, but that doesn’t empty the queue. If you have multiple Runbook Servers, the next one will start picking up the jobs. If you only have one, then once you restart the service it will start picking up the jobs again. And, of course, all this time you have other jobs that need to get queued and run. So what do you do? You could run the stored procedure to clean up the queue so nothing else will get picked up, that is, if the stored procedure existed in the Orchestrator database configuration. So how do you get this useful piece of functionality? Easy. You just drop it in.

Since the database schema didn’t change much from Opalis to Orchestrator, it’s relatively easy to copy the stored procedure from one to the other – the only change is renaming “Opalis” to “Orchestrator”. Of course, it’s handy to have around both versions of the product to do just this sort of thing Smile

Take the following two SQL scripts and run them on your Orchestrator database and two stored procedures will be created. When you run sp_StopAllRequests, it actually loops through each runbook and runs the other stored procedure, so you need both. Here are the scripts:

USE [Orchestrator]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_StopAllRequestsForPolicy]
    @PolicyID uniqueidentifier
AS
BEGIN
    DELETE FROM [POLICY_PUBLISH_QUEUE] WHERE [PolicyID] = @PolicyID

    UPDATE [POLICIES] SET [Published] = 0, [PublishingTime] = getutcdate()
    WHERE [UniqueID] = @PolicyID
END

And here’s the other one:

USE [Orchestrator]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_StopAllRequests]
AS
BEGIN
    DECLARE running_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT [PolicyID]
    FROM [POLICY_PUBLISH_QUEUE]

    DECLARE @policyID uniqueidentifier

    OPEN running_cursor
    FETCH NEXT FROM running_cursor INTO @policyID
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC [sp_StopAllRequestsForPolicy] @PolicyID = @policyID
        FETCH NEXT FROM running_cursor INTO @policyID
    END

    CLOSE running_cursor
    DEALLOCATE running_cursor
END

Now when you need to clear out the queue, simply run sp_StopAllRequests, click OK and it clears out the queue!

That’s all for now…until the next SQL…I mean sequel!