

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/PruneSqlTableEntries_V1.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/PruneSqlTableEntries_V1.sp,v $ $Id: PruneSqlTableEntries_V1.sp,v 1.12.34.4.8.1 2021/06/09 22:33:05 ankumar Exp $";
--
--  +========================================================================+
--  | Stored Precedure: PruneSqlTableEntries_V1
--  |
--  | Description:
--  |  Clears all the entries from sqlTable after job is pruned.
--  |
--  |   Revisions	Author		Description
--  |   ---------  ----------	---------------------------------------------
--  |   1.0       Kiran & Anup   Initial Edit
--	|
--  +========================================================================+
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='PruneSqlTableEntries_V1')
	delete from GXDBVersions where aliasname = 'PruneSqlTableEntries_V1'
GO
print '... Creating Procedure: PruneSqlTableEntries_V1'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure PruneSqlTableEntries_V1
AS
  DECLARE @retVal INTEGER;
--This will turn off message: "xxx rows affected".
SET NOCOUNT ON
DECLARE @rowCount INT = 0
IF object_id('tempdb.dbo.#PruneSQLArchEntries') is not null DROP TABLE #PruneSQLArchEntries
CREATE TABLE #PruneSQLArchEntries
(
	sqlDbBackupFileId	INTEGER, PRIMARY KEY (sqlDbBackupFileId)
)
INSERT INTO #PruneSQLArchEntries
select distinct SAI.sqlDbBackupFileId
from sqlArchiveInfo SAI WITH (NOLOCK)
left outer join archfile AF WITH (NOLOCK) ON SAI.backupJobId = AF.jobid and SAI.commCellId = AF.commCellId
INNER JOIN sqlDbBackupInfo SDBI WITH (NOLOCK) ON SDBI.Id = SAI.sqlDbBackupFileId
where AF.id is null AND SDBI.backupmethod = 2
-- Apply inner join with archfile to workaround left outer join MS SQL bug.
DELETE #PruneSQLArchEntries
	FROM #PruneSQLArchEntries T, sqlArchiveInfo SAI WITH (NOLOCK), archFile AF WITH (NOLOCK)
	WHERE	T.sqlDbBackupFileId = SAI.sqlDbBackupFileId
	AND		SAI.commCellId = AF.commCellId
	AND		SAI.backupJobId = AF.jobId
INSERT INTO #PruneSQLArchEntries
select distinct SAI.sqlDbBackupFileId
from sqlArchiveInfo SAI WITH (NOLOCK)
left outer join archfile AF WITH (NOLOCK) ON SAI.aFileId = AF.id and SAI.commCellId = AF.commCellId
INNER JOIN sqlDbBackupInfo SDBI WITH (NOLOCK) ON SDBI.Id = SAI.sqlDbBackupFileId
where AF.id is null AND SDBI.backupmethod <> 2
-- Apply inner join with archfile to workaround left outer join MS SQL bug.
DELETE #PruneSQLArchEntries
	FROM #PruneSQLArchEntries T, sqlArchiveInfo SAI WITH (NOLOCK), archFile AF WITH (NOLOCK)
	WHERE	T.sqlDbBackupFileId = SAI.sqlDbBackupFileId
	AND		SAI.commCellId = AF.commCellId
	AND		SAI.aFileId = AF.id
WHILE 1 = 1
BEGIN
	delete TOP (2000) SDBI
	FROM  SqlDbBackupInfo SDBI
	inner join #PruneSQLArchEntries PAF ON SDBI.id = PAF.sqlDbBackupFileId
	SELECT @retVal = @@ERROR, @rowCount = @@ROWCOUNT
	IF @retVal <> 0
		BREAK
	IF @rowCount = 0
		BREAK
END
TRUNCATE TABLE #PruneSQLArchEntries
INSERT INTO #PruneSQLArchEntries
SELECT DISTINCT SAAI.sqlAnalysisDbBackupFileId
FROM sqlAnalysisArchiveInfo SAAI WITH (NOLOCK)
LEFT OUTER JOIN archfile AF WITH (NOLOCK) ON SAAI.aFileId = AF.id and SAAI.commCellId = AF.commCellId
WHERE AF.id IS NULL
-- Apply inner join with archfile to workaround left outer join MS SQL bug.
DELETE #PruneSQLArchEntries
	FROM #PruneSQLArchEntries T, sqlAnalysisArchiveInfo SAAI WITH (NOLOCK), archFile AF WITH (NOLOCK)
	WHERE	T.sqlDbBackupFileId = SAAI.sqlAnalysisDbBackupFileId
	AND		SAAI.commCellId = AF.commCellId
	AND		SAAI.aFileId = AF.id
WHILE 1 = 1
BEGIN
	delete TOP (25000) SADBI
	FROM  sqlAnalysisDbBackupInfo SADBI
	inner join #PruneSQLArchEntries PAF ON SADBI.id = PAF.sqlDbBackupFileId
	SELECT @retVal = @@ERROR, @rowCount = @@ROWCOUNT
	IF @retVal <> 0
		BREAK
	IF @rowCount = 0
		BREAK
END
DROP TABLE #PruneSQLArchEntries
-- Since sqlnames table is being used for Analysis Services instance as well, before deleting records from
-- sqlnames table, we need to check sqlDbBackupInfo and sqlAnalysisDbBackupInfo both.
-- In SP15, change was made to use sqlnames table for AS and hence before that dbid will be empty.
-- Before SP15, database name was directly stored in the sqlAnalysisDbBackupInfo table.
delete from sqlNames
where id not in (
					select DISTINCT SDBI.sqlNameId
					from sqlDbBackupInfo SDBI WITH (NOLOCK)
					UNION
					SELECT DISTINCT CONVERT(INT, SADBI.dbid)
					from sqlAnalysisDbBackupInfo SADBI WITH (NOLOCK)
					WHERE LEN(LTRIM(RTRIM(SADBI.dbid))) > 0
				) and type = 1
delete from sqlNames2 where id not in (select sqlid from sqlNames WITH (NOLOCK) where type = 1) and type = 1
delete from sqlNames where id not in (select sqlNameFgId from sqlFilebackupinfo WITH (NOLOCK)) and type = 2
delete from sqlNames2 where id not in (select sqlid from sqlNames WITH (NOLOCK) where type = 2) and type = 2
delete from sqlNames where id not in (select sqlNamePhyId from sqlFilebackupinfo WITH (NOLOCK)) and type = 3
delete from sqlNames2 where id not in (select sqlid from sqlNames WITH (NOLOCK) where type = 3) and type = 3
delete from sqlNames where id not in (select sqlNameLogId from sqlFilebackupinfo WITH (NOLOCK)) and type = 4
delete from sqlNames2 where id not in (select sqlid from sqlNames WITH (NOLOCK) where type = 4) and type = 4
IF object_id('tempdb.dbo.#tblSqlLogMarksToBeDeleted') IS NOT NULL DROP TABLE #tblSqlLogMarksToBeDeleted
CREATE TABLE #tblSqlLogMarksToBeDeleted (
	id		INT,
	dbId		INT,
	instanceId	INT,
	flsn		varchar(255),
	llsn		varchar(255),
	sqlNameId	int,
	sqlId		int,
	dbName		sysname,
	PRIMARY KEY(dbId, instanceId, id)
)
INSERT INTO #tblSqlLogMarksToBeDeleted
SELECT SDBI.id, SDBI.dbId, SDBI.instanceId, SDBI.first_lsn, SDBI.last_lsn, SDBI.sqlNameId, SN.sqlId, SN2.name
FROM sqlDbBackupInfo SDBI WITH (NOLOCK)
INNER JOIN sqlNames SN WITH (NOLOCK) ON sn.id = SDBI.sqlNameId
INNER JOIN sqlnames2 SN2 WITH (NOLOCK) ON SN2.id = SN.sqlId AND SN2.type = 1
DELETE sqlLogMarks
FROM sqlLogMarks
LEFT OUTER JOIN #tblSqlLogMarksToBeDeleted TSLM on
	TSLM.dbId = sqlLogMarks.dbId and
	TSLM.instanceId = sqlLogMarks.instanceId and
	sqlLogMarks.lsn >= TSLM.flsn and
	sqllogmarks.lsn < TSLM.llsn and
	TSLM.dbname = sqlLogMarks.dbName
WHERE
	TSLM.id IS NULL
SET @retVal = @@ERROR
SELECT @retVal
RETURN @retVal;
GO

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

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

insert into GXDBVersions values(2, 'PruneSqlTableEntries_V1',  'v1.12.34.4.8.1', 'PruneSqlTableEntries_V1', 'v1.12.34.4.8.1')
GO

