As you might know, Orchestrator never deleted anything from the database, instead it marks the object as deleted.

Ryan talks about it in this blog post about undeleting objects. Read more here

In my Lab environment I import and delete a lot of runbooks.

A couple of weeks ago i hit a point where the web service did not show all objects. After a thorough investigation i found out that the authorization cache was too big to be generated within the giving time. Therefore the web service would timeout before the cache was complete.

I tried to delete objects, but it did not help. By checking the database i found out that the authorization cache was generating entries for all objects, even the ones that were already deleted!

so i checked the stored procedures in the data store, and found out that it was in fact using a query that included all objects.

To fix this problem i have changed the stored procedures inside the data store:

Here is my new version of the procedures:

[Microsoft.SystemCenter.Orchestrator.Internal].[ComputeRunbookAuthorizationCache]:

USE [Orchestrator]
GO
/****** Object:  StoredProcedure [Microsoft.SystemCenter.Orchestrator.Internal].[ComputeRunbookAuthorizationCache]    Script Date: 4/23/2013 6:06:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Microsoft.SystemCenter.Orchestrator.Internal].[ComputeRunbookAuthorizationCache]	
	@TokenId bigint
AS	
BEGIN
	SET NOCOUNT ON;

    DECLARE @cursor CURSOR 
	DECLARE @uniqueId uniqueidentifier
	DECLARE @readRights bigint
	DECLARE @readAndPublishRights bigint
	
	SET @readRights = 16
	SET @readAndPublishRights = 1048592	
	
	SET @cursor = CURSOR LOCAL FAST_FORWARD FOR
	SELECT [UniqueID] FROM dbo.POLICIES WHERE Deleted = 0
	OPEN @cursor
	FETCH NEXT FROM @cursor INTO @uniqueId
	WHILE @@FETCH_STATUS = 0 
	BEGIN   
		exec [Microsoft.SystemCenter.Orchestrator.Internal].PopulateAuthorizationCache  @TokenId, @uniqueId, @readRights
		exec [Microsoft.SystemCenter.Orchestrator.Internal].PopulateAuthorizationCache  @TokenId, @uniqueId, @readAndPublishRights
		FETCH NEXT FROM @cursor INTO @uniqueId
	END
	CLOSE @cursor
	DEALLOCATE @cursor
END

and the other procedure:

[Microsoft.SystemCenter.Orchestrator.Internal].[ComputeFolderAuthorizationCache]:

USE [Orchestrator]
GO
/****** Object:  StoredProcedure [Microsoft.SystemCenter.Orchestrator.Internal].[ComputeFolderAuthorizationCache]    Script Date: 4/23/2013 6:06:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Microsoft.SystemCenter.Orchestrator.Internal].[ComputeFolderAuthorizationCache]	
	@TokenId bigint
AS	
BEGIN
	SET NOCOUNT ON;

    DECLARE @cursor CURSOR 
	DECLARE @uniqueId uniqueidentifier
	DECLARE @rights bigint
	
	SET @rights = 16
	SET @cursor = CURSOR LOCAL FAST_FORWARD FOR
	SELECT [UniqueID] FROM dbo.FOLDERS WHERE Deleted = 0
	OPEN @cursor
	FETCH NEXT FROM @cursor INTO @uniqueId
	WHILE @@FETCH_STATUS = 0 
	BEGIN   
		exec [Microsoft.SystemCenter.Orchestrator.Internal].PopulateAuthorizationCache  @TokenId, @uniqueId, @rights
		FETCH NEXT FROM @cursor INTO @uniqueId
	END
	CLOSE @cursor
	DEALLOCATE @cursor
END

 

All i have added to the procedure queries is

WHERE Deleted = 0

after you have changed the procedures, wait 10+ minutes for the authorization cache to update or force the update by using this guide:

http://blogs.technet.com/b/thomase/archive/2012/05/29/orchestrator-runbooks-not-apperaring-on-the-web-console.aspx

Now it works and i have no problems.

But please remember

Changing the data store is NOT supported, and this solution should only be used in Lab environments.

Please do not hesitate to post any comments about the solution 🙂