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
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!