

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

-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/MM_SMDeleteVolSnaps.sp,v $ $Id: MM_SMDeleteVolSnaps.sp,v 1.3.12.10.20.1 2021/07/07 02:27:07 kkumar Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='MM_SMDeleteVolSnaps')
	delete from GXDBVersions where aliasname = 'MM_SMDeleteVolSnaps'
GO
print '... Creating Procedure: MM_SMDeleteVolSnaps'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure MM_SMDeleteVolSnaps
  @i_options INTEGER,
  @i_flags INTEGER
AS
  DECLARE @retVal INTEGER;
--This will turn off message: "xxx rows affected".
SET NOCOUNT ON
BEGIN
	/* i_options:	1 - called from MM_SMDBPruneVolSnaps
					2 - called from archPruneSnapArchiveFiles.spb
					0 - called from archPruneVolSnaps
	*/
	SET @retVal = 0
	DECLARE @checkDanglingAFCIntervalHours integer = 0
	SET @checkDanglingAFCIntervalHours = ISNULL((select Value from MMConfigs (NOLOCK) where name = 'MMS2_CONFIG_SNAP_CHECK_DANGLING_AFC_INTERVAL_IN_HOURS'),
2)
	DECLARE @checkDanglingAFCLastRunTime integer = 0
	SET @checkDanglingAFCLastRunTime = ISNULL((select Value from MMConfigs (NOLOCK) where name = 'MMS2_CONFIG_SNAP_CHECK_DANGLING_AFC_LAST_RUN_TIME'), 0)
	DECLARE @cutoffTime INT = dbo.GetUnixTime(GETUTCDATE()) - (@checkDanglingAFCLastRunTime +  (@checkDanglingAFCIntervalHours * 60 * 60))
	IF ( @i_options = 0 OR @i_options = 1 )
	BEGIN
		IF OBJECT_ID('tempdb.dbo.#tblDelVolSnapsVolIds') IS NOT NULL
			DROP TABLE #tblDelVolSnapsVolIds
		CREATE TABLE #tblDelVolSnapsVolIds (
											SMVolumeId INT
											,ArchFileId INT
											,CommCellId INT
											,CopyId INT
											)
		CREATE INDEX #tblDelVolSnapsVolIds_SMVolumeId_Idx ON #tblDelVolSnapsVolIds (SMVolumeId)
		CREATE INDEX #tblDelVolSnapsVolIds_ArchFileId_CommCellId_CopyId_Idx ON #tblDelVolSnapsVolIds ( ArchFileId, CommCellId, CopyId )
		-- OLD style
		IF ( @i_options = 0 OR ( @i_options = 1 AND @cutoffTime > 0 ))
		BEGIN
			UPDATE MMConfigs
			SET value = dbo.GetUnixTime(GETUTCDATE())
			WHERE name = 'MMS2_CONFIG_SNAP_CHECK_DANGLING_AFC_LAST_RUN_TIME'
			DECLARE @danglingSnapDelayForAFCDel INT = 0
			SET @danglingSnapDelayForAFCDel = ISNULL((
						SELECT Value
						FROM MMConfigs(READUNCOMMITTED)
						WHERE name = 'MMS2_CONFIG_SNAP_DANGLING_SNAP_DELAY_FOR_AFC_DEL_IN_MIN'
), 180/*MMS2_CONFIG_SNAP_DANGLING_SNAP_DELAY_FOR_AFC_DEL_IN_MIN_DEFAULT*/)
			SET @danglingSnapDelayForAFCDel = (@danglingSnapDelayForAFCDel * 60)
			DECLARE @CurrentTime INTEGER = dbo.GetUnixTime(GetUTCDate())
			----------
			-- Pick all the smvolume ids which are not having valid archfiles
			-- skip materjob\parent jobs for VSA V2 where child volume exists
			----------
			INSERT INTO #tblDelVolSnapsVolIds
			SELECT 	 A.SMVolumeId
					,A.ArchFileId
					,A.CommCellId
					,A.CopyId
			FROM SMVolume A(READUNCOMMITTED)
			LEFT JOIN archFileCopy AFC WITH (READUNCOMMITTED)
					ON A.ArchFileId = AFC.ArchFileId
						AND A.CommCellId = AFC.CommCellId
						AND A.CopyId = AFC.ArchCopyId
			WHERE AFC.archFileId IS NULL
AND A.PruneFlags <> 4	/*MM_SM_PRUNEFLAGS_DANGLING*/
				AND (@CurrentTime - A.CreationTime) > @danglingSnapDelayForAFCDel
 				AND (
						-- For non-VSA v2 cases bit will be 0
(A.VolumeFlags & CAST( 4294967296 AS BIGINT) /*CVSM_VOLUMEFLAGS_VSA_V2_SNAP_DB*/ = 0)
						-- For VSA v2 & v21 cases
						OR
							(
								-- VSA v2 and v21 child and parent SMVolume entries should have this VolumeFlags bit set if not dont proceed further
(A.VolumeFlags & CAST( 4294967296 AS BIGINT) /*CVSM_VOLUMEFLAGS_VSA_V2_SNAP_DB*/ > 0)
								AND
								(
									-- Child VM/Guest client jobs
									A.jobid <> A.MasterJobId
									OR
									-- Parent without any active dependent children
									NOT EXISTS (
												SELECT 1
												FROM SMVolume B WITH (READUNCOMMITTED)
												WHERE A.MasterJobId = B.MasterJobId --all children + parent
													AND A.jobid <> B.jobid -- ONLY if there is a child entry B.jobid will be different
													AND A.CopyId = B.CopyId
AND B.PruneFlags <= 1	/*MM_SM_PRUNEFLAGS_ACTIVE*/
													AND A.CommCellId = B.CommCellId
												)
								)
							)
					)
			SET @retVal = @@ERROR
			IF @retVal <> 0
			BEGIN
SET @retVal = 60200/*E_MM_SM_SB_ERROR*/
				GOTO FINISH
			END
		END		-- IF ( @i_options = 0 OR ( @i_options = 1 AND @cutoffTime > 0 ))
		IF (@i_options = 1) -- get all vsa v2 parent volume entries whose afc's are removed and all children too are removed , ignore cutofftime.
		BEGIN
			INSERT	INTO #tblDelVolSnapsVolIds
			SELECT	A.SMVolumeId
					,A.ArchFileId
					,A.CommCellId
					,A.CopyId
			FROM 	SMVolume A(READUNCOMMITTED)
					LEFT JOIN archFileCopy AFC WITH (READUNCOMMITTED)
					ON  A.ArchFileId = AFC.ArchFileId
						AND A.CommCellId = AFC.CommCellId
						AND A.CopyId = AFC.ArchCopyId
			WHERE 	AFC.archFileId IS NULL
AND A.PruneFlags <> 4	/*MM_SM_PRUNEFLAGS_DANGLING*/
					-- we are dealing here with only parent which are having afile as NULL
					AND A.jobid = A.MasterJobId
					AND (
						-- For  VSA v2 cases bit will be > 0
(A.VolumeFlags & CAST( 4294967296 AS BIGINT) /*CVSM_VOLUMEFLAGS_VSA_V2_SNAP_DB*/ > 0)
						-- For VSA v2 & v21 cases
						AND
						-- Parent without any active dependent childresn
						NOT EXISTS
						(
							SELECT	1
							FROM 	SMVolume B WITH (READUNCOMMITTED)
									INNER JOIN SMVolSnapMap MapB WITH (READUNCOMMITTED)
										ON B.SMVolumeId = MapB.SMVolumeId
									INNER JOIN SMVolSnapMap MapA WITH (READUNCOMMITTED)
										ON A.SMVolumeId = MapA.SMVolumeId
									INNER JOIN SMSnap SnapA WITH (READUNCOMMITTED)
										ON SnapA.SMSnapId = MapA.SMSnapId
									INNER JOIN SMSnap SnapB WITH (READUNCOMMITTED)
										ON MapB.SMSnapId = SnapB.SMSnapId
							WHERE 	A.MasterJobId = B.MasterJobId --all children + parent
									AND A.jobid <> B.jobid -- ONLY if there is a child entry B.jobid will be different
									AND A.CopyId = B.CopyId
AND B.PruneFlags <= 1	/*MM_SM_PRUNEFLAGS_ACTIVE*/
									AND A.CommCellId = B.CommCellId
AND B.VolumeFlags & CAST( 4294967296 AS BIGINT) /*CVSM_VOLUMEFLAGS_VSA_V2_SNAP_DB*/ > 0
									-- only worry for its DS ( Hetal K suggested it )
									-- in case all VMs deleted from single DS it should be deleted.
									AND (
											-- if non-Netapp engine, dont bother checking grouping or partial delete.
SnapB.SnapShotEngineId <> 3
											OR
											MapA.SMSnapId = MapB.SMSnapId
											OR
											(SnapA.GroupId = SnapB.GroupId AND SnapB.GroupId > 0) --IF groupid is set, do the whole job level delete else let single snap delete from job.
										)
						)
					)
			SET @retVal = @@ERROR
			IF @retVal <> 0
			BEGIN
SET @retVal = 60200	/*E_MM_SM_SB_ERROR*/
				GOTO FINISH
			END
		END -- IF (@i_options = 1)
        -- Update prune flag if we have rows in #tblDelVolSnapsVolIds
		IF EXISTS (
				SELECT 1
				FROM #tblDelVolSnapsVolIds
				)
		BEGIN
			DELETE A
			FROM #tblDelVolSnapsVolIds A
			INNER JOIN archFileCopy AFC WITH (READCOMMITTED) ON A.ArchFileId = AFC.ArchFileId
				AND A.CommCellId = AFC.CommCellId
				AND A.CopyId = AFC.ArchCopyId
			SET @retVal = @@ERROR
			IF @retVal <> 0
			BEGIN
SET @retVal = 60200	/*E_MM_SM_SB_ERROR*/
				GOTO FINISH
			END
			UPDATE VOL
SET PruneFlags = 4 /*MM_SM_PRUNEFLAGS_DANGLING*/
			FROM #tblDelVolSnapsVolIds TMP
			INNER JOIN SMVolume VOL ON TMP.SMVolumeId = VOL.SMVolumeId
			SET @retVal = @@ERROR
			IF @retVal <> 0
			BEGIN
SET @retVal = 60268 /*E_MM_SM_SB_UPDATE_PRUNEFLAGS_FAILED*/
				GOTO FINISH
			END
		END    -- IF exists #tblDelVolSnapsVolIds
	END		-- IF ( @i_options = 0 OR @i_options = 1 )
		IF	object_id('tempdb.dbo.#ToBeDeletedVols') is null AND @i_options = 0 -- Called from archPruneVolsnaps
			GOTO FINISH
	DECLARE @tblSMVolSnap TABLE (SMVolumeId INT, SMVolSnapMapId INT, SMSnapId INT, MetadataId INT)
	INSERT	INTO @tblSMVolSnap
	SELECT	VOL.SMVolumeId, MAP.SMVolSnapMapId, SNAP.SMSnapId, MAP.MetadataId
	FROM	#ToBeDeletedVols VOL
			INNER JOIN SMVolSnapMap MAP (NOLOCK) ON VOL.SMVolumeId = MAP.SMVolumeId
			INNER JOIN SMSnap SNAP (NOLOCK) ON MAP.SMSnapID = SNAP.SMSnapId
	-- Check if any snap to be deleted is still mounted
	IF	EXISTS (SELECT * FROM @tblSMVolSnap T, SMSnapResource R WITH (NOLOCK)
				WHERE T.SMVolumeId = R.SMVolumeId)
	BEGIN
SET	@retVal = 85
		GOTO FINISH
	END
	IF EXISTS ( SELECT * FROM @tblSMVolSnap T INNER JOIN SMMountVolume MNTVOL WITH(NOLOCK) ON T.SMVolumeId = MNTVOL.SMVolumeId
WHERE MNTVOL.MountStatus < 79 /*SM_VS_UMT_UNMOUNTED*/)
	BEGIN
SET @retVal = 60314
		GOTO FINISH
	END
	IF EXISTS ( SELECT * FROM @tblSMVolSnap T INNER JOIN SMMountMap MNTMAP WITH(NOLOCK) ON T.SMVolSnapMapId = MNTMAP.SMVolSnapMapId
INNER JOIN SMMountVolume MVOL (NOLOCK) ON MNTMAP.SMMountVolumeId = MVOL.Id AND MVOL.MountStatus < 79 /*SM_VS_UMT_UNMOUNTED*/)
	BEGIN
SET @retVal = 60314
		GOTO FINISH
	END
	IF EXISTS ( SELECT * FROM SMMountSnap MSNAP WITH (NOLOCK)
				INNER JOIN SMSnap SNAP WITH (NOLOCK) ON MSNAP.SMSnapId = SNAP.SMSnapId
				INNER JOIN SMMountMap MMAP (NOLOCK) ON MMAP.SMMountSnapId = MSNAP.Id
				INNER JOIN SMMountVolume MVOL (NOLOCK) ON MVOL.Id = MMAP.SMMountVolumeId
				AND MVOL.MountJobId = MSNAP.ReserveField4 AND MVOL.CreationTime = MSNAP.CreationTime
WHERE MVOL.MountStatus < 79 /*SM_VS_UMT_UNMOUNTED*/ AND MSNAP.SMSnapId IN
				(SELECT SMSnapId FROM @tblSMVolSnap)
				AND SNAP.SMSnapId > 0 )
	BEGIN
SET @retVal = 60314
		GOTO FINISH
	END
	DELETE SMVolSnapMap WITH (ROWLOCK)
	WHERE SMVolSnapMapId IN (SELECT SMVolSnapMapId FROM @tblSMVolSnap)
	SET @retVal = @@ERROR
	IF @retVal <> 0
	BEGIN
SET @retVal = 60317
		GOTO FINISH
	END
	DELETE SMVolume WITH (ROWLOCK)
	WHERE SMVolumeId IN (SELECT SMVolumeId FROM @tblSMVolSnap)
	SET @retVal = @@ERROR
	IF @retVal <> 0
	BEGIN
SET @retVal = 60315
		GOTO FINISH
	END
	SET @retVal = @@ERROR
	IF @retVal <> 0
	BEGIN
SET @retVal = 60268
		GOTO FINISH
	END
	DELETE	SMSnap WITH (ROWLOCK)
	WHERE	SMSnapId IN (SELECT SMSnapId FROM @tblSMVolSnap)
			AND SMSnapId NOT IN (SELECT SMSnapId FROM SMvolSnapMap(NOLOCK))
			AND SMSnapId > 0
	SET @retVal = @@ERROR
	IF @retVal <> 0
	BEGIN
SET @retVal = 60316
		GOTO FINISH
	END
	DELETE SMMetaData WITH (ROWLOCK)
WHERE RefType = 1
	AND RefId IN (SELECT SMVolumeId FROM @tblSMVolSnap)
	AND MetaDataId > 0
	SET @retVal = @@ERROR
	IF @retVal <> 0
	BEGIN
SET @retVal = 60318
		GOTO FINISH
	END
	DELETE	SMMetaData WITH (ROWLOCK)
WHERE	RefType = 2
	AND	RefId IN (SELECT SMSnapId FROM @tblSMVolSnap)
	AND RefId NOT IN (SELECT SMSnapId FROM SMSnap(NOLOCK))
	AND MetaDataId > 0
	SET @retVal = @@ERROR
	IF @retVal <> 0
	BEGIN
SET @retVal = 60318
		GOTO FINISH
	END
	DELETE SMMetaData WITH (ROWLOCK)
WHERE RefType = 0
	AND MetaDataId IN (SELECT MetaDataId FROM @tblSMVolSnap)
	AND MetaDataId NOT IN (SELECT MetaDataId FROM SMVolSnapMap WITH (NOLOCK))
	AND MetaDataId > 0
	SET @retVal = @@ERROR
	IF @retVal <> 0
	BEGIN
SET @retVal = 60318
		GOTO FINISH
	END
	IF @i_options = 1 -- Called from Snap Prune Thread
	BEGIN
		DELETE SMSnap WITH (ROWLOCK)
		WHERE SMSnapId NOT IN (SELECT SMSnapId FROM SMVolSnapMap WITH (NOLOCK))
		AND SMSnapId > 0
		SET @retVal = @@ERROR
		IF @retVal <> 0
		BEGIN
SET @retVal = 60316
			GOTO FINISH
		END
		DELETE SMMetaData WITH (ROWLOCK)
WHERE RefType = 1
		AND RefId NOT IN (SELECT SMVolumeId FROM SMVolume WITH (NOLOCK))
		AND MetaDataId > 0
		SET @retVal = @@ERROR
		IF @retVal <> 0
		BEGIN
SET @retVal = 60318
			GOTO FINISH
		END
		DELETE SMMetaData WITH (ROWLOCK)
WHERE RefType = 2
		AND RefId NOT IN (SELECT SMSnapId FROM SMSnap WITH (NOLOCK))
		AND MetaDataId > 0
		SET @retVal = @@ERROR
		IF @retVal <> 0
		BEGIN
SET @retVal = 60318
			GOTO FINISH
		END
		DELETE SMMetaData WITH (ROWLOCK)
WHERE RefType = 0
		AND MetaDataId NOT IN (SELECT MetaDataId FROM SMVolSnapMap WITH (NOLOCK))
		AND MetaDataId NOT IN (SELECT MetaDataId FROM SMMountMap WITH (NOLOCK))
		AND MetaDataId > 0
		SET @retVal = @@ERROR
		IF @retVal <> 0
		BEGIN
SET @retVal = 60318
			GOTO FINISH
		END
		DECLARE	@tblPrepJobDeleted TABLE (
				JobId integer,
				CommCellId integer
			)
		INSERT INTO @tblPrepJobDeleted
		SELECT A.JobId, A.CommCellId
		FROM JMPreparedJob A WITH (NOLOCK)
		DELETE @tblPrepJobDeleted
		FROM @tblPrepJobDeleted A, SMVolume B WITH (NOLOCK)
		WHERE  A.JobId = B.JobId
		AND A.CommCellId = B.CommCellId
		DELETE JMPreparedJob
		FROM JMPreparedJob a, @tblPrepJobDeleted b
		WHERE a.JobId = b.JobId
		AND a.CommCellId = b.CommCellId
		SET @retVal = @@ERROR
		IF @retVal <> 0
		BEGIN
SET @retVal = 60319
			GOTO FINISH
		END
		DELETE jmJobSnapShotStats
		FROM jmJobSnapShotStats a, @tblPrepJobDeleted b
		WHERE a.JobId = b.JobId
		AND a.CommCellId = b.CommCellId
		SET @retVal = @@ERROR
		IF @retVal <> 0
		BEGIN
SET @retVal = 60319
			GOTO FINISH
		END
	END
FINISH:
IF OBJECT_ID('tempdb.dbo.#tblDelVolSnapsVolIds') IS NOT NULL
		DROP TABLE #tblDelVolSnapsVolIds
SELECT	@retVal
RETURN	@retVal
END
GO

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

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

insert into GXDBVersions values(2, 'MM_SMDeleteVolSnaps',  'v1.3.12.10.20.1', 'MM_SMDeleteVolSnaps', 'v1.3.12.10.20.1')
GO

