

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/archDeleteIndexingAF.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/archDeleteIndexingAF.sp,v $ $Id: archDeleteIndexingAF.sp,v 1.6.2.21.4.3 2021/07/12 22:21:05 srajani Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='archDeleteIndexingAF')
BEGIN
	print '>>> Drop Stored Procedure: archDeleteIndexingAF <<<'
	drop procedure archDeleteIndexingAF
END
IF EXISTS (select * from GxQscripts where name='archDeleteIndexingAF')
	delete from GxQscripts where name = 'archDeleteIndexingAF'
GO

IF EXISTS (select * from GXDBVersions where aliasname='archDeleteIndexingAF')
	delete from GXDBVersions where aliasname = 'archDeleteIndexingAF'
GO
print '... Creating Procedure: archDeleteIndexingAF'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure archDeleteIndexingAF
  @i_dummy INTEGER
AS
  DECLARE @retVal INTEGER;
  DECLARE @retStr VARCHAR(1024);
	--This will turn off message: "xxx rows affected".
	SET NOCOUNT ON
	DECLARE		@archiveFileXML		XML
	DECLARE		@retCode		INTEGER
	SET				@retCode		= 0
	DECLARE		@rowCount		INTEGER
	SET				@rowCount		= 0
	DECLARE	@AFVersionsToKeep		INTEGER
	SET			@AFVersionsToKeep		= 3
	DECLARE	@deleteDanglingAF 	INTEGER
	SET			@deleteDanglingAF		= 0
	--This key will delete all the index backup which are not associated with the current storage policy of the IndexServer pseudo client.
	DECLARE	@deleteInactiveSPIndexBackup 	INTEGER
	SET			@deleteInactiveSPIndexBackup	= 0
	DECLARE	@numberOfIndexDBToProcess 	INTEGER
	SET			@numberOfIndexDBToProcess		= 5000
	DECLARE	@disableToBeCopiedJobs	INTEGER
	SET			@disableToBeCopiedJobs	= 0
	DECLARE @retCodeTbl TABLE
	(
		retCode			INTEGER,
		retString		NVARCHAR(MAX)
	)
	CREATE TABLE #IndexingAppId(
		appId 			INTEGER,
		dataArchGrpID	INT
		Primary Key (appId)
	)
	CREATE TABLE #IndexingAFToProcess(
		dbId				INTEGER,
		cs_afname			INTEGER,
		archFileName	NVARCHAR(MAX),
		isOrphan			SMALLINT
	)
	CREATE INDEX Idx_tmpIndexingAFToProcess_1 ON #IndexingAFToProcess (cs_afname)
	CREATE TABLE #IndexingAFList(
		dbId				INTEGER,
		cs_afname			INTEGER,
		archFileName	NVARCHAR(MAX),
		isOrphan			SMALLINT
	)
	CREATE INDEX Idx_tmpIndexingAFList_1 ON #IndexingAFList (cs_afname)
	CREATE TABLE #IndexingAFToPrune(
		archFileId 		INTEGER,
		commCellId 		INTEGER,
		JobId					INTEGER,
		fileType			INTEGER,
		archFileName	NVARCHAR(MAX),
		cs_afname			INTEGER,
		ctime					INTEGER,
		dbId				INTEGER
		Primary Key (archFileId, commCellId)
	)
	CREATE TABLE #IdxLogAFNamesToPrune(
		archFileName	NVARCHAR(MAX),
		dbId	        INTEGER
    )
	CREATE TABLE #IndexingAFToKeep(
		archFileId 		INTEGER,
		commCellId 		INTEGER
		Primary Key (archFileId, commCellId)
	)
	CREATE TABLE #jobsToDisable(
		jobId 				INTEGER,
		commCellId 		INTEGER
	)
	--For a dbId this table has all the subclients belonging to DB's backupset
	CREATE TABLE #DbToSubclientMapping(
		dbId					INTEGER,
		appId					INTEGER,
		committedTransactionId	INTEGER,
		isBackupsetIndex		INTEGER,
		subclientStatus			INTEGER
	)
	CREATE TABLE #MinBackupCtimePerDB(
		dbId				INTEGER,
		cTime				INTEGER,
	)
	IF object_id('tempdb.dbo.#indexCheckpointList') IS NOT NULL DROP TABLE #indexCheckpointList
	CREATE TABLE #indexCheckpointList(
		Afileid 	INT,
		commCellId	INT,
		JobId		INT,
		name		NVARCHAR(128),
		afileArchGroupId	INT,
		indexArchGroupId	INT,
		isValid		INT,
		cTime		INT
	)
	CREATE INDEX Idx_tmpjobsToDisable_1 ON #jobsToDisable (jobId, commCellId)
	CREATE TABLE #unassociatedAFList
	(
		id 				INT,
		checksum 		INT,
		name 			Nvarchar(256),
		subclientSPId 	INT,
		indexserverSPId INT
	)
	CREATE TABLE #afListToBeDeleted
	(
		dbId 		INT,
		AfileId 	INT,
		commcellId 	INT
	)
	SELECT @deleteDanglingAF = isnull(value, 0)
	FROM MMConfigs WITH (NOLOCK)
	WHERE name = 'DA_CONFIG_DELETE_DANGLING_AF_OF_INDEXING_SUBCLIENT'
	SET	@retVal = @@ERROR
	IF	@retVal != 0
	BEGIN
		SET @deleteDanglingAF = 0
	END
	SELECT @deleteInactiveSPIndexBackup = isnull(value, 0)
	FROM MMConfigs WITH (NOLOCK)
	WHERE name = 'DA_CONFIG_DELETE_INACTIVE_SP_INDEX_BACKUP'
	SET	@retVal = @@ERROR
	IF	@retVal != 0
	BEGIN
		SET @deleteInactiveSPIndexBackup = 0
	END
	SELECT @AFVersionsToKeep = isnull(value, 3)
	FROM MMConfigs WITH (NOLOCK)
	WHERE name = 'DA_CONFIG_AF_VERSIONS_TO_KEEP_FOR_INDEXING_SUBCLIENT'
	SET	@retVal = @@ERROR
	IF	@retVal != 0
	BEGIN
		SET @AFVersionsToKeep = 3
	END
	SELECT @numberOfIndexDBToProcess = isnull(value, 5000)
	FROM MMConfigs WITH (NOLOCK)
	WHERE name = 'DA_CONFIG_NUMBER_OF_INDEXING_DB_TO_PROCESS_IN_LOOP'
	SET	@retVal = @@ERROR
	IF	@retVal != 0
	BEGIN
		SET @AFVersionsToKeep = 5000
	END
	SELECT @disableToBeCopiedJobs = isnull(value, 0)
	FROM MMConfigs WITH (NOLOCK)
	WHERE name = 'DA_CONFIG_DISABLE_JOBS_FOR_AUXCOPY_THAT_MET_RETENTION'
	SET	@retVal = @@ERROR
	IF	@retVal != 0
	BEGIN
		SET @disableToBeCopiedJobs = 0
	END
	DECLARE @daysToKeepAfiles INT = 30
	SELECT @daysToKeepAfiles = isnull(value, 30)
	FROM MMConfigs WITH (NOLOCK)
	WHERE name = 'DA_CONFIG_RETENTION_DAYS_FOR_INDEX_BACKUPS'
	SET	@retVal = @@ERROR
	IF	@retVal != 0
	BEGIN
		SET @daysToKeepAfiles = 30
	END
	DECLARE @tExpiry INT = DBO.GetUnixTime(getdate()) - (@daysToKeepAfiles * 24 * 60 * 60)
	--Get all Indexing subclients
	INSERT INTO #IndexingAppId
	SELECT DISTINCT componentNameId, App.dataArchGrpID
	FROM App_SubclientProp SP WITH (NOLOCK)
	JOIN APP_Application APP WITH (NOLOCK) ON APP.id = SP.componentNameId
	WHERE	cs_attrName = CHECKSUM(N'Index SubClient')
	AND		attrName = N'Index SubClient'
	AND		attrVal = '1'
	AND		SP.modified = 0
	SELECT	@retVal = @@ERROR, @rowCount = @@ROWCOUNT
	IF	@retVal != 0
	BEGIN
		SET @retStr = 'Failed to retrieve indexing subclients.'
		GOTO CX_EXIT
	END
	IF @rowCount = 0
		GOTO CX_EXIT
	--Get list of AF Names to process
	INSERT INTO #IndexingAFToProcess
	SELECT DISTINCT info.id, CHECKSUM('IdxCheckPoint_' + CAST(backupSetGUID AS NVARCHAR(36)) + ':' + CAST(dbName AS NVARCHAR(36))),
				 'IdxCheckPoint_' + CAST(backupSetGUID AS NVARCHAR(36)) + ':' + CAST(dbName AS NVARCHAR(36)), 0
	FROM 	APP_IndexDBInfo info  WITH (NOLOCK)
	JOIN App_BackupsetName BS WITH (NOLOCK) ON  info.backupSetId = BS.id
	WHERE info.idxDbEngineType = 1
	SET	@retVal = @@ERROR
	IF	@retVal != 0
	BEGIN
		SET @retStr = 'Failed to retrieve Index Backup archive file names.'
		GOTO CX_EXIT
	END
	--Delete dangling AF's of Index Backups
	IF @deleteDanglingAF != 0
	BEGIN
		INSERT INTO #IndexingAFToProcess
		SELECT  AFP.dbId, AF.cs_afname, AF.name, 1
		FROM 	 archFile AF WITH (NOLOCK) INNER JOIN #IndexingAppId APP
		ON 	AF.appId = APP.appId
		LEFT OUTER JOIN #IndexingAFToProcess AFP
		ON 	AF.cs_afname = AFP.cs_afname
		AND	AF.name = AFP.archFileName
		WHERE (AFP.cs_afname IS NULL OR AFP.archFileName IS NULL)
AND		AF.flags & 131072 > 0
		AND AF.name like 'IdxCheckPoint_%'
		GROUP BY AFP.dbId, AF.cs_afname, AF.name
		SET	@retVal = @@ERROR
		IF	@retVal != 0
		BEGIN
			SET @retStr = 'Failed to retrieve orphan Index Backup archive file names.'
			GOTO CX_EXIT
		END
	END
	IF @deleteInactiveSPIndexBackup != 0
	BEGIN
		INSERT INTO #unassociatedAFList
		SELECT distinct tmp.dbId, tmp.cs_afname, tmp.archFileName, app.dataArchGrpID , app1.dataArchGrpID
		FROM #IndexingAFToProcess tmp
		JOIN App_IndexDBInfo info WITH(NOLOCK) ON info.id = tmp.dbId
		JOIN APP_Application app WITH(NOLOCK) on info.backupSetId = app.backupSet
		JOIN archGroup AG WITH(NOLOCK) on AG.id = app.dataArchGrpID
		JOIN APP_ClientProp AP WITH(NOLOCK) on AP.attrName = 'Index Server ArchGroup Id' AND CAST(AP.attrVal AS INT) = AG.id
		JOIN APP_Application app1 WITH(NOLOCK) on app1.clientId = AP.componentNameId
		WHERE dbo.isSubClientIndexEnabled(info.backupSetId) = 0
		INSERT INTO #afListToBeDeleted
		SELECT C.id, AF.Id, AF.commCellId from archFile AF WITH (NOLOCK)
		JOIN #unassociatedAFList C on C.checksum = AF.cs_afname and c.name = AF.name
		WHERE NOT EXISTS(SELECT 1 FROM #unassociatedAFList A where A.checksum = AF.cs_afname and A.name = AF.name and a.indexserverSPId = AF.archGroupId)
	END
	WHILE EXISTS(SELECT 1 FROM #IndexingAFToProcess)
	BEGIN
		INSERT INTO #IndexingAFList
		SELECT DISTINCT TOP (@numberOfIndexDBToProcess) *
		FROM #IndexingAFToProcess
		INSERT INTO #indexCheckpointList
		SELECT DISTINCT AF.id, AF.commCellId, JI.JobId, AF.name, AF.archGroupId, APP.dataArchGrpID, AF.isValid, AF.cTime
		FROM archFile AF WITH (NOLOCK) INNER JOIN #IndexingAppId APP
		ON 	AF.appId = APP.appId
		JOIN archFileCopy AFC WITH (NOLOCK) ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND (AFC.flags & 256 = 0)
		INNER JOIN #IndexingAFList AFL
		ON 	AF.cs_afname = AFL.cs_afname
		AND	AF.name = AFL.archFileName
		LEFT OUTER JOIN JMJobInfo JI (NOLOCK)
		ON 	AF.JobId = JI.JobId
		AND AF.commCellId = JI.CommCellId
		WHERE AFL.isOrphan = 0
AND		AF.flags & 131072 > 0
		AND	  ((AF.isValid = 1 AND AFC.isValid = 1) OR (JI.JobId IS NOT NULL))--To handle running job
		AND AF.fileType = 1
		--Get X versions of archive files with same name to retain for each indexserver pseudo client and its storage policy pair
		;WITH AFToKeep AS
		(
			SELECT Afileid as id, commCellId, JobId, ROW_NUMBER() OVER (PARTITION BY name, indexArchGroupId ORDER BY isValid DESC, cTime DESC) AS AFCount
			FROM #indexCheckpointList where afileArchGroupId = indexArchGroupId
		)
		INSERT INTO #IndexingAFToKeep
		SELECT id, commCellId
		FROM AFToKeep
		WHERE (AFCount >= 1 AND AFCount <= @AFVersionsToKeep) OR (JobId IS NOT NULL)
		SET	@retVal = @@ERROR
		IF	@retVal != 0
		BEGIN
			SET @retStr = 'Failed to retrieve archive files to keep.'
			GOTO CX_EXIT
		END
		DELETE FROM #indexCheckpointList
		WHERE name in (SELECT name FROM #indexCheckpointList WHERE afileArchGroupId = indexArchGroupId)
		--Make sure to retain 1 index in every possible scenario.
		;WITH AFToKeep AS
		(
			SELECT Afileid as id, commCellId, ROW_NUMBER() OVER (PARTITION BY name ORDER BY isValid DESC, cTime DESC) AS AFCount
			FROM #indexCheckpointList where afileArchGroupId <> indexArchGroupId
		)
		INSERT INTO #IndexingAFToKeep
		SELECT temp.id, temp.commCellId
		FROM AFToKeep temp
		LEFT JOIN #IndexingAFToKeep tmp ON tmp.archFileId = temp.id AND tmp.commCellId = temp.commCellId
		WHERE (AFCount = 1) AND tmp.archFileId IS NULL
		SET	@retVal = @@ERROR
		IF	@retVal != 0
		BEGIN
			SET @retStr = 'Failed to retrieve top 3 archive files to keep.'
			GOTO CX_EXIT
		END
		-- subclient where pruning ran has checkpoints available in App_IndexCheckpointInfo. They should not be pruned
		INSERT INTO #IndexingAFToKeep
		SELECT DISTINCT AF.id, AF.commCellId
		FROM archFile AF WITH (NOLOCK) INNER JOIN
		App_IndexCheckpointInfo CI WITH (NOLOCK)
		ON CI.afileId = AF.id AND CI.commCellId = AF.commCellId AND CI.flags&1 = 1 -- INDEXDBINFO_FLAG_PRUNINGBIT
		JOIN archFileCopy AFC WITH (NOLOCK) ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AFC.isValid = 1 AND (AFC.flags & 256 = 0)
		LEFT JOIN #IndexingAFToKeep tmp ON tmp.archFileId = CI.afileId AND tmp.commCellId = CI.commCellId
		WHERE AF.isValid = 1 AND tmp.archFileId IS NULL
		SET	@retVal = @@ERROR
		IF	@retVal != 0
		BEGIN
			SET @retStr = 'Failed to retrieve archive files to keep for subclient index.'
			GOTO CX_EXIT
		END
		--Get the archive files to prune
		INSERT INTO #IndexingAFToPrune
		SELECT DISTINCT AF.id, AF.commCellId, AF.JobId, AF.fileType, AF.name, AF.cs_afname, AF.ctime, AFL.dbId
		FROM 	archFile AF WITH (NOLOCK) INNER JOIN #IndexingAppId APP
		ON 		AF.appId = APP.appId
		INNER JOIN #IndexingAFList AFL
		ON 		AF.cs_afname = AFL.cs_afname
		AND		AF.name = AFL.archFileName
		LEFT OUTER JOIN	#IndexingAFToKeep AFK
		ON		AF.id = AFK.archFileId
		AND		AF.commCellId = AFK.commCellId
		LEFT OUTER JOIN JMJobInfo JI (NOLOCK)
		ON 	AF.JobId = JI.JobId
		AND AF.commCellId = JI.CommCellId
		WHERE	AFK.archFileId IS NULL
AND	AF.flags & 131072 > 0
			AND JI.JobId IS NULL
		SET	@retVal = @@ERROR
		IF	@retVal != 0
		BEGIN
			SET @retStr = 'Failed to retrieve archive files to prune.'
			GOTO CX_EXIT
		END
		IF @deleteInactiveSPIndexBackup > 0
		BEGIN
			INSERT INTO #IndexingAFToPrune
			SELECT DISTINCT AF.id, AF.commCellId, AF.JobId, AF.fileType, AF.name, AF.cs_afname, AF.ctime, tmp.dbId
			FROM #afListToBeDeleted tmp
			JOIN archFile AF WITH (NOLOCK) ON AF.id = tmp.afileId AND AF.commCellId = tmp.commCellId
			LEFT OUTER JOIN	#IndexingAFToPrune AFK
			ON		AF.id = AFK.archFileId
			AND		AF.commCellId = AFK.commCellId
			WHERE	AFK.archFileId IS NULL
		END
		--DO NOT prune archive files if the corresponding job is pending for aux copy
		DELETE #IndexingAFToPrune
		OUTPUT DELETED.JobId, DELETED.commCellId
 		INTO #jobsToDisable
		FROM #IndexingAFToPrune AFP, JMJobDataStats JDS WITH (NOLOCK)
		WHERE AFP.JobId = JDS.JobId
		AND		AFP.commCellId = JDS.commCellId
		AND		AFP.fileType = JDS.dataType
		AND		JDS.status IN (101, 102, 103)
AND		JDS.disabled & 1 = 0
AND		JDS.disabled & 256 = 0
		SET	@retVal = @@ERROR
		IF	@retVal != 0
		BEGIN
			SET @retStr = 'Failed to remove archive files of jobs that are pending for aux copy.'
			GOTO CX_EXIT
		END
		IF (@disableToBeCopiedJobs = 1)
		BEGIN
			-- Do not disable jobs that still has valid AF to keep
			DELETE #jobsToDisable
			FROM #jobsToDisable J, #IndexingAFToKeep AFK, archFile AF WITH (NOLOCK)
			WHERE J.jobId = AF.jobId
			AND 	J.commCellId = AF.commCellId
			AND		AF.id = AFK.archFileId
			AND		AF.commCellId = AFK.commCellId
			SET	@retVal = @@ERROR
			IF	@retVal != 0
			BEGIN
				SET @retStr = 'Failed to get job(s) to disable for aux copy.'
				GOTO CX_EXIT
			END
			UPDATE JMJobDataStats
SET disabled = disabled | (1 + 8192)
			FROM 	JMJobDataStats JDS, #jobsToDisable J
			WHERE	JDS.jobId = J.jobId
			AND		JDS.commCellId = J.commCellId
			SET	@retVal = @@ERROR
			IF	@retVal != 0
			BEGIN
				SET @retStr = 'Failed to mark job(s) disabled for aux copy.'
				GOTO CX_EXIT
			END
		END
		--Get the Log Archive File which need to be pruned
		INSERT INTO #IdxLogAFNamesToPrune
		SELECT DISTINCT REPLACE(AFP.archFileName, 'IdxCheckPoint', 'IdxLiveLogs'), AFP.dbId
		FROM #IndexingAFToPrune AFP
		SET	@retVal = @@ERROR
		IF	@retVal != 0
		BEGIN
			SET @retStr = 'Failed to populate LiveLogs archive files to be checked for pruning'
			GOTO CX_EXIT
		END
		--Populate dbToSubclientMapping
		INSERT INTO #DbToSubclientMapping
		SELECT IDX.id, APP.id, IDX.committedTransactionId, CASE WHEN IDX.backupSetGUID = IDX.dbName THEN 1 ELSE 0 END, APP.subclientStatus
		FROM App_Application APP WITH (NOLOCK)
		LEFT JOIN APP_SubClientProp SCP WITH(NOLOCK) ON SCP.componentNameId = APP.id
		AND SCP.attrName IN (N'Index SubClient')
		AND SCP.cs_attrName IN (CHECKSUM(N'Index SubClient'))
		AND SCP.modified = 0
		JOIN App_IndexDBInfo IDX WITH (NOLOCK) ON APP.backupSet = IDX.backupSetId
		WHERE
APP.subclientStatus & 0x00010 = 0 --Dummy
AND APP.subclientStatus & 0x00020 = 0 --Hidden
AND APP.appTypeId <> 1000 --DR Subclient
		AND ISNULL(SCP.attrVal, '0') <> '1'
		SET	@retVal = @@ERROR
		IF	@retVal != 0
		BEGIN
			SET @retStr = 'Failed to populate subclients mapping for index database'
			GOTO CX_EXIT
		END
		--Populate the log afile info which need to be pruned
		INSERT INTO #IndexingAFToPrune
		SELECT DISTINCT AF.id, AF.commCellId, AF.JobId, AF.fileType, AF.name, AF.cs_afname, AF.ctime, AFL.dbId
		FROM 	archFile AF WITH (NOLOCK), #IdxLogAFNamesToPrune AFL
		WHERE	AF.cs_afname = CHECKSUM(AFL.archFileName)
		AND		AF.name = AFL.archFileName
		AND
		(
			AFL.dbId = NULL OR
			-- Do not delete live logs archive file till there exists a backup job run before the erase operation for that backupset.
			NOT EXISTS
			(
				SELECT TOP 1 1 FROM archFile AF2 WITH (NOLOCK) WHERE
				AF2.appId IN (SELECT appId FROM #DbToSubclientMapping WHERE dbId = AFL.dbId AND committedTransactionId > 0 AND
subclientStatus & 0x00002 = 0 AND subclientStatus & 0x00004 = 0)
				AND AF2.isValid = 1
				AND AF2.cTime <= AF.ctime
			)
		)
		DECLARE @curTime BIGINT = dbo.GetUnixTime(GETDATE())
		INSERT INTO #MinBackupCtimePerDB
		SELECT tmp.dbId, MIN(ISNULL(AF.ctime, @curTime))
		FROM #DbToSubclientMapping tmp
		LEFT JOIN Archfile AF WITH(NOLOCK) ON AF.appid = tmp.appid AND AF.isValid = 1
		WHERE tmp.isBackupsetIndex = 1
		GROUP BY tmp.dbId
		--Prune the checkpoint where all the jobs are aged in it
		INSERT INTO #IndexingAFToPrune
		SELECT DISTINCT AF.id, AF.commCellId, AF.JobId, AF.fileType, AF.name, AF.cs_afname, AF.ctime, AFL.dbId
		FROM 	archFile AF WITH (NOLOCK) INNER JOIN #IndexingAppId APP
		ON 		AF.appId = APP.appId
		INNER JOIN #IndexingAFList AFL
		ON 		AF.cs_afname = AFL.cs_afname
		AND		AF.name = AFL.archFileName
		JOIN #MinBackupCtimePerDB tmp ON tmp.dbId = AFL.dbId
		LEFT JOIN #IndexingAFToPrune AFP ON AFP.archFileId = AF.id AND AFP.commCellId = AF.commCellId
		WHERE AF.isValid = 1 AND AFP.archFileId IS NULL
AND AF.flags & 131072 > 0
		AND AF.ctime < tmp.ctime
		SET	@retVal = @@ERROR
		IF	@retVal != 0
		BEGIN
			SET @retStr = 'Failed to populate LiveLogs archive files to be pruned'
			GOTO CX_EXIT
		END
		--Generate input XML to send list of archive files to prune
		SET @archiveFileXML = (	SELECT archFileInfo.archFileId AS archFileId, archFileInfo.commCellId AS commCelld
														FROM #IndexingAFToPrune archFileInfo
														FOR	 XML AUTO, ROOT('GalaxyOperations_PruneArchiveFiles')
													)
		SET	@retVal = @@ERROR
		IF	@retVal != 0
		BEGIN
			SET @retStr = 'Failed to generate XML for archive files to prune.'
			GOTO CX_EXIT
		END
		--Prune the archive files
		IF @archiveFileXML IS NOT NULL
		BEGIN
EXEC @retVal = archDeleteArchiveFiles @archiveFileXML, 8589934592, 1
			IF	(@@ERROR != 0 OR @retVal != 0)
			BEGIN
				SET @retStr = 'Failed to prune indexing archive files.'
				GOTO CX_EXIT
			END
		END
		DELETE #IndexingAFToProcess
		FROM #IndexingAFToProcess AFP, #IndexingAFList AFL
		WHERE AFP.cs_afname = AFL.cs_afname
		AND		AFP.archFileName = AFL.archFileName
		TRUNCATE TABLE #IndexingAFList
		TRUNCATE TABLE #IndexingAFToKeep
		TRUNCATE TABLE #IndexingAFToPrune
		TRUNCATE TABLE #IdxLogAFNamesToPrune
		TRUNCATE TABLE #DbToSubclientMapping
	END
	SET @retStr = 'Indexing archive files pruned successfully.'
CX_EXIT:
       IF object_id('tempdb.dbo.#IndexingAppId') IS NOT NULL DROP TABLE #IndexingAppId
       IF object_id('tempdb.dbo.#IndexingAFToProcess') IS NOT NULL DROP TABLE #IndexingAFToProcess
       IF object_id('tempdb.dbo.#IndexingAFList') IS NOT NULL DROP TABLE #IndexingAFList
       IF object_id('tempdb.dbo.#IndexingAFToKeep') IS NOT NULL DROP TABLE #IndexingAFToKeep
       IF object_id('tempdb.dbo.#IndexingAFToPrune') IS NOT NULL DROP TABLE #IndexingAFToPrune
       IF object_id('tempdb.dbo.#IdxLogAFNamesToPrune') IS NOT NULL DROP TABLE #IdxLogAFNamesToPrune
       IF object_id('tempdb.dbo.#jobsToDisable') IS NOT NULL DROP TABLE #jobsToDisable
	   IF object_id('tempdb.dbo.#DbToSubclientMapping') IS NOT NULL DROP TABLE #DbToSubclientMapping
	   IF object_id('tempdb.dbo.#indexCheckpointList') IS NOT NULL DROP TABLE #indexCheckpointList
	   IF object_id('tempdb.dbo.#MinBackupCtimePerDB') IS NOT NULL DROP TABLE #MinBackupCtimePerDB
	   IF object_id('tempdb.dbo.#unassociatedAFList') IS NOT NULL DROP TABLE #unassociatedAFList
	   IF object_id('tempdb.dbo.#afListToBeDeleted') IS NOT NULL DROP TABLE #afListToBeDeleted
       select @retVal, @retStr
       RETURN @retVal
GO

IF EXISTS (select * from GxQscripts where name = 'archDeleteIndexingAF')
	delete from GxQscripts where name = 'archDeleteIndexingAF'
GO

IF EXISTS (select * from GXDBVersions where aliasname='archDeleteIndexingAF')
	delete from GXDBVersions where aliasname = 'archDeleteIndexingAF'
GO

insert into GXDBVersions values(2, 'archDeleteIndexingAF',  'v1.6.2.21.4.3', 'archDeleteIndexingAF', 'v1.6.2.21.4.3')
GO

