I found it useful to clear Orchestrator Runbook logs by Folder. This allowed me to clear smaller chunks of logs from Orchestrator where in the past I was clearing the entire database which was a very slow process. A small improvement, but it helps.
-- This script will remove logs from a folder in Orchestrator
-- enter a folder name into the folder variable
-- Create temporary table for sysprocesses
set QUOTED_IDENTIFIER Off
SET NOCOUNT ON
-- Variables
declare @strsql char(200),
@ThePolicyId varchar(50),
@USRCOUNT int,
@PRINTUSRCOUNT VARCHAR (255),
@SqlScript varchar(255),
@PolicyId varchar(50),
@FolderName VARCHAR(100),
@NumberOfDaysToKeep INT
-- Folder name
SET @FolderName = 'Folder Name'
SET @NumberOfDaysToKeep = 180
-- Query for UniqueId number of all Runbooks in a folder
use Orchestrator
set @strsql = 'select p.UniqueID from [dbo].[POLICIES] p
INNER JOIN [dbo].[FOLDERS] f ON p.ParentID = f.UniqueID
WHERE f.[Name] = ''' + @FolderName + '''
AND p.Deleted = 0
ORDER BY f.name, p.name'
IF OBJECT_ID('tempdb..#toRun') IS NOT NULL DROP TABLE #toRun
create table #toRun
(
PolicyId uniqueidentifier,
)
-- Insert into temp table
INSERT INTO #toRun EXEC(@strsql)
-- Comment out the query below to hide connections
--select * from #toRun
SELECT @USRCOUNT = (SELECT CONVERT(CHAR, COUNT(1)) FROM #toRun)
SET @PRINTUSRCOUNT = 'Attempting to run ' + CAST(@USRCOUNT AS VARCHAR(36)) + ' Runbooks'
PRINT @PRINTUSRCOUNT
-- Pause script for 1 seconds
WAITFOR DELAY '00:00:01'
-- Begin cursor
DECLARE CUSRRUN SCROLL CURSOR FOR
SELECT PolicyId FROM #toRun
OPEN CUSRRUN
FETCH FIRST FROM CUSRRUN INTO @PolicyId
SELECT @SqlScript = 'DECLARE @Completed bit
SET @Completed = 0
WHILE @Completed = 0 EXEC sp_CustomLogCleanup
@Completed OUTPUT,
@FilterType=2,
@YDays=''' + CAST(@NumberOfDaysToKeep AS VARCHAR) + ''',
@PolicyID=''' + CAST(@PolicyId AS VARCHAR(36)) + ''''
PRINT 'Runbook: ' + CAST(@PolicyId AS VARCHAR(36))
PRINT @SqlScript
EXEC(@SqlScript)
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CUSRRUN into @PolicyId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SqlScript = 'DECLARE @Completed bit
SET @Completed = 0
WHILE @Completed = 0 EXEC sp_CustomLogCleanup
@Completed OUTPUT,
@FilterType=2,
@YDays=''' + CAST(@NumberOfDaysToKeep AS VARCHAR) + ''',
@PolicyID=''' + CAST(@PolicyId AS VARCHAR(36)) + ''''
PRINT 'Runbook: ' + CAST(@PolicyId AS VARCHAR(36))
PRINT @SqlScript
EXECUTE(@SqlScript)
FETCH NEXT FROM CUSRRUN INTO @PolicyId
END
END
CLOSE CUSRRUN
DEALLOCATE CUSRRUN
--End cursor
PRINT 'Finished running'
DROP TABLE #toRun
--select TOP 10 * from [dbo].[POLICIES] p
--select TOP 10 f.Name,* from [dbo].[POLICIES] p
--INNER JOIN [dbo].[FOLDERS] f ON p.ParentID = f.UniqueID
--WHERE f.[Name] = 'Cisco'
--AND p.Deleted = 0
--ORDER BY f.name, p.name
--select * from [dbo].[FOLDERS] f WHERE f.[Name] = 'Cisco' ORDER BY f.name
Notes:
When the sp_CustomLogCleanup stored procedure is executed, it identifies 200 policy instances to purge and returns either a 0 or a 1 upon completion. The stored procedure identifies the 200 policy instances that are to be purged by executing one of the three additional stored procedures based on the log purging option selected:
- sp_GetLogEntriesForDelete_FilterByEntries
- sp_GetLogEntriesForDelete_FilterByDays
- sp_GetLogEntriesForDelete_FilterByEntriesAndDays
The log purge utility has a hard-coded time out value for the execution of the stored procedure. While not recommended, you can change this value within the stored procedure to accommodate extremely large log counts.
If you choose to turncate, truncating the OBJECTINSTANCEDATA and OBJECTINSTANCES tables should be enough, as these two tables contain the bulk of log data. You may not be able to truncate the POLICYINSTANCES due to foreign key constraints.
