--@querytype CSV --Name:- Index Backup Stats (Health) --- Please follow the below comments to insert SQL statements. -- Client version query --------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY --------- SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET QUOTED_IDENTIFIER OFF --------- BEGIN SURVEY QUERY --------- DECLARE @csReleaseId INT = 0 SELECT @csReleaseId = releaseId FROM APP_Client WHERE id = 2 DECLARE @csSPVersion INT = 0 IF @csReleaseId >= 15 SELECT @csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2 IF @csReleaseId < 15 OR @csReleaseId = 15 AND @csSPVersion < 7 BEGIN RETURN END DECLARE @timezonename NVARCHAR(1024) = '' SELECT @timezonename = dbo.GetClientTimeZone(2) IF ISNULL(@timezonename, '') = '' BEGIN SELECT @timezonename = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2 SELECT @timezonename = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK) WHERE TimeZoneName = SUBSTRING(@timezonename, CHARINDEX(':', @timezonename, CHARINDEX(':', @timezonename, 0) + 1) + 1, 255) END IF object_id('dbo.IdxDbState') IS NOT NULL BEGIN IF object_id('tempdb.dbo.#AppsDisabled') IS NOT NULL DROP TABLE #AppsDisabled IF object_id('tempdb.dbo.#AppsBackupDisableForInstance') IS NOT NULL DROP TABLE #AppsBackupDisableForInstance IF object_id('tempdb.dbo.#tmpDetails') is not null DROP TABLE #tmpDetails IF object_id('tempdb.dbo.#tempValidApps') is not null DROP TABLE #tempValidApps IF object_id('tempdb.dbo.#tempSCIndexBS') is not null DROP TABLE #tempSCIndexBS IF object_id('tempdb.dbo.#tmpLegalHoldBackupsets') is not null DROP TABLE #tmpLegalHoldBackupsets IF object_id('tempdb.dbo.#tmpBackupHistory') is not null DROP TABLE #tmpBackupHistory IF object_id('tempdb.dbo.#tmpIndexMigrations') is not null DROP TABLE #tmpIndexMigrations CREATE TABLE #tmpDetails( ClientId INT, Client NVARCHAR(255), AppType VARCHAR(255), Backupset NVARCHAR(128), backupSetGUID VARCHAR(36), dbName VARCHAR(128), subclientName NVARCHAR(255), idxEngineType INT, IndexServer NVARCHAR(255), uptodate INT NULL, lastTimeDbUptodate BIGINT NULL, lastPlayedJobTime BIGINT NULL, lastCompactionTime BIGINT NULL, errorLvl INT NULL, errorDesc NVARCHAR(4000) NULL, LastBackupTime BIGINT NULL, LastBackupForDb BIGINT NULL, LastIdxErrorTime BIGINT NULL, FirstBackupForDb BIGINT NULL, ScIdxConversionTime BIGINT NULL, DatabaseSize BIGINT NULL, LogsSize BIGINT NULL, ReportsSize BIGINT NULL, MaintenanceSize BIGINT NULL, IndexedItems BIGINT NULL, ApplicationSize BIGINT NULL, LastIndexServer NVARCHAR(255), MigrationReason INT NULL ) DECLARE @dynQuery NVARCHAR(MAX) -- Get backups disabled for instance if CommCell version is >= 11.4.0 CREATE TABLE #AppsBackupDisableForInstance(appId INT, PRIMARY KEY(appId)) IF @csReleaseId > 15 AND @csSPVersion >= 4 BEGIN SET @dynQuery = 'SELECT DISTINCT APP.id FROM APP_Application APP WITH (NOLOCK) JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1 AND J.instanceId = APP.instance AND J.instanceId > 1' INSERT INTO #AppsBackupDisableForInstance(appId) EXEC sp_executeSQL @dynQuery END --> List of apps that has backup activity disabled thru different entry points CREATE TABLE #AppsDisabled(appId INT, PRIMARY KEY(appId)) INSERT INTO #AppsDisabled SELECT DISTINCT APP.id -- Backup disabled for Client Group FROM APP_Application APP WITH (NOLOCK) JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON APP.clientId = CGA.clientId JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1 AND CGA.clientGroupId = J.clientGroupId AND J.clientGroupId > 0 AND J.clientId = 1 AND J.appType = 0 AND J.appId = 1 UNION SELECT DISTINCT APP.id -- Backup disabled for Subclient Policy FROM APP_Application APP WITH (NOLOCK) JOIN ( SELECT componentNameId AS appId, CAST(attrVal AS INT) AS subclientPolicyAppId FROM APP_SubClientProp WITH (NOLOCK) WHERE attrName = 'Associated subclient Policy' AND cs_attrName = CHECKSUM(N'Associated subclient Policy') AND modified = 0 ) T ON APP.id = T.appId JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1 AND J.appId = T.subclientPolicyAppId UNION SELECT DISTINCT APP.id -- Backup disabled for Subclient FROM APP_Application APP WITH (NOLOCK) JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1 AND J.appId = APP.id UNION SELECT DISTINCT APP.id -- Backup disabled for Client: 1) For all appTypes 2) For a particular appType FROM APP_Application APP WITH (NOLOCK) JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1 AND J.clientId = APP.ClientId AND J.appType IN (APP.appTypeId, 0) UNION SELECT appId FROM #AppsBackupDisableForInstance UNION SELECT DISTINCT APP.id FROM APP_BackupSetName BS WITH (NOLOCK) JOIN APP_BackupSetProp PROP WITH (NOLOCK) ON PROP.componentNameId = BS.id JOIN APP_Application APP WITH(NOLOCK) ON BS.id = APP.backupSet WHERE PROP.attrName = 'OnDemand Backupset' AND PROP.attrVal = '1' AND PROP.modified = 0 CREATE TABLE #tempValidApps(appId INT, bsId INT, firstBackup BIGINT, lastBackup BIGINT, scCreationTime BIGINT) INSERT INTO #tempValidApps(appId, bsId, firstBackup, lastBackup, scCreationTime) SELECT DISTINCT APP.id, APP.backupSet, MIN(AF.cTime), MAX(AF.ctime), APP.refTime FROM APP_Application APP WITH(NOLOCK) JOIN ARCHFILE AF WITH(NOLOCK) ON AF.appId = APP.id and AF.isValid = 1 AND AF.fileType IN(2, 6) JOIN archFileCopy AFC WITH(NOLOCK) ON AF.id = AFC.archFileId AND AFC.isValid = 1 AND(AFC.flags & 256 = 0) LEFT JOIN #AppsDisabled INV ON APP.id = INV.appId WHERE APP.appTypeId != 137 AND APP.subclientStatus & 4214 = 0 --CV_STATUS_UNINSTALLED, CV_STATUS_DELETED, CV_STATUS_DUMMY, CV_STATUS_HIDDEN, CV_STATUS_CMD_LINE, CV_STATUS_LOGCMD_LINE AND INV.appId IS NULL GROUP BY APP.id, APP.backupSet, APP.refTime CREATE TABLE #tempSCIndexBS(bsId INT, scIdxPropCreationTime INT) INSERT INTO #tempSCIndexBS(bsId, scIdxPropCreationTime) SELECT DISTINCT APP.backupSet, PROP.created FROM APP_Application APP WITH(NOLOCK) JOIN APP_IDAName IDA WITH(NOLOCK) ON APP.clientId = IDA.clientId AND APP.appTypeId = IDA.appTypeId JOIN APP_IDAProp PROP WITH(NOLOCK) ON PROP.componentNameId = IDA.id WHERE PROP.attrName = 'Subclient Index' AND PROP.attrVal = '1' AND PROP.modified = 0 CREATE TABLE #tmpBackupHistory ( dbId INT, indexedItems BIGINT, applicationSize BIGINT ) CREATE TABLE #tmpIndexMigrations ( dbId INT, currentIdxServer INT, lastServerName NVARCHAR(255), migrationReason INT DEFAULT -1 ) IF @csReleaseId >= 16 BEGIN SET @dynQuery = NULL -- For index playback history, we need to check the existance of table IdxDbPlaybackStats in either CS db or HistoryDB IF @csSPVersion >= 18 AND @csSPVersion < 23 -- Between 11.0 SP18 AND 11.0 SP23 SET @dynQuery = 'SELECT DISTINCT DB.id, JH.indexedItems, JH.applicationSizeMB FROM App_IndexDBInfo DB WITH(NOLOCK) LEFT JOIN ( SELECT OH.dbId, OH.indexedItems, OH.applicationSizeMB FROM IdxDbPlaybackStats OH WITH(NOLOCK) INNER JOIN ( SELECT dbId, MAX(jobId) AS maxJobId FROM IdxDbPlaybackStats WITH(NOLOCK) GROUP BY dbId ) IH ON OH.dbId = IH.dbId AND OH.jobId = IH.maxJobId ) JH ON DB.id = JH.dbId' ELSE IF @csSPVersion >= 23 -- >= 11.0 SP23 SET @dynQuery = 'SELECT DISTINCT DB.id, JH.indexedItems, JH.applicationSizeMB FROM App_IndexDBInfo DB WITH(NOLOCK) LEFT JOIN ( SELECT OH.dbId, OH.indexedItems, OH.applicationSizeMB FROM HistoryDB..IdxDbPlaybackStats OH WITH(NOLOCK) INNER JOIN ( SELECT dbId, MAX(jobId) AS maxJobId FROM HistoryDB..IdxDbPlaybackStats WITH(NOLOCK) GROUP BY dbId ) IH ON OH.dbId = IH.dbId AND OH.jobId = IH.maxJobId ) JH ON DB.id = JH.dbId' IF @dynQuery IS NOT NULL -- Add to backup history table INSERT INTO #tmpBackupHistory(dbId, indexedItems, applicationSize) EXEC sp_executeSQL @dynQuery -- For migration history IF @csSPVersion >= 12 -- >= 11.0 SP12 BEGIN IF @csSPVersion < 23 SET @dynQuery = 'SELECT IM.indexId, IM.toClientId, IMT.name, -1 FROM ( SELECT indexId, fromClientId, toClientId, MAX(endTime) AS [lastEndTime] FROM IdxServerMigrations WITH(NOLOCK) GROUP BY indexId, fromClientId, toClientId ) TM JOIN IdxServerMigrations IM WITH(NOLOCK) ON TM.indexId = IM.indexId AND TM.fromClientId = IM.fromClientId AND TM.toClientId = IM.toClientId AND TM.lastEndTime = IM.endTime JOIN APP_Client IMT WITH(NOLOCK) ON IM.toClientId = IMT.id WHERE IM.fromClientId <> IM.toClientId' ELSE SET @dynQuery = 'SELECT IM.indexId, IM.toClientId, IMT.name, IM.reason FROM ( SELECT indexId, fromClientId, toClientId, MAX(endTime) AS [lastEndTime] FROM IdxServerMigrations WITH(NOLOCK) GROUP BY indexId, fromClientId, toClientId ) TM JOIN IdxServerMigrations IM WITH(NOLOCK) ON TM.indexId = IM.indexId AND TM.fromClientId = IM.fromClientId AND TM.toClientId = IM.toClientId AND TM.lastEndTime = IM.endTime JOIN APP_Client IMT WITH(NOLOCK) ON IM.toClientId = IMT.id WHERE IM.fromClientId <> IM.toClientId' -- Insert into index migrations history table INSERT INTO #tmpIndexMigrations(dbId, currentIdxServer, lastServerName, migrationReason) EXEC sp_executeSQL @dynQuery END END INSERT INTO #tmpDetails(ClientId, Client, AppType, Backupset, backupSetGUID, dbName, subclientName, idxEngineType, IndexServer, uptodate, lastTimeDbUptodate, lastPlayedJobTime, lastCompactionTime, LastBackupForDb, errorLvl, errorDesc, LastIdxErrorTime, FirstBackupForDb, ScIdxConversionTime, DatabaseSize, LogsSize, ReportsSize, MaintenanceSize, IndexedItems, ApplicationSize, LastIndexServer, MigrationReason) SELECT DISTINCT app.clientId, -- ClientId CC.name, -- Client ida.name, -- AppType bs.name, -- Backupset idx.backupSetGUID, -- backupSetGUID idx.DbName, -- dbName (CASE WHEN TBS.bsId IS NOT NULL THEN APP.subclientName ELSE '' END), -- subclientName idx.idxDbEngineType, -- idxEngineType mm.name, -- IndexServer st.dbUptoDate, -- uptodate ISNULL(st.properties.value('(/Indexing_DbStats/@lastCompletedBkpTime)[1]', 'BIGINT'), -1), -- lastTimeDbUptodate ISNULL(st.properties.value('(/Indexing_DbStats/@lastPlayedBkpTime)[1]', 'BIGINT'), -1), -- lastPlayedJobTime ISNULL(st.properties.value('(/Indexing_DbStats/@lastCompactionTime)[1]', 'BIGINT'), 0), -- lastCompactionTime (CASE WHEN TBS.bsId IS NULL THEN (SELECT MAX(lastBackup) FROM #tempValidApps TVA WHERE TVA.bsId = BS.id) ELSE APPS.lastBackup END), -- LastBackupForDb st.errorlvl, -- errorLvl ISNULL(st.errordesc, ''), -- errorDesc ISNULL(st.properties.value('(/Indexing_DbStats/@lastIdxErrorTime)[1]', 'BIGINT'), -1), -- LastIdxErrorTime (CASE WHEN TBS.bsId IS NULL THEN (SELECT MIN(firstBackup) FROM #tempValidApps TVA WHERE TVA.bsId = BS.id) ELSE APPS.firstBackup END), -- FirstBackupForDb (CASE WHEN (TBS.bsId is NOT NULL AND TBS.scIdxPropCreationTime > APPS.scCreationTime) THEN TBS.scIdxPropCreationTime ELSE -1 END), -- ScIdxConversionTime ISNULL(ISNULL(st.properties.value('(/Indexing_DbStats/@dbSize)[1]', 'BIGINT'), st.properties.value('(/Indexing_DbStats/@dbsize)[1]', 'BIGINT')), 0), -- DatabaseSize ISNULL(st.properties.value('(/Indexing_DbStats/@logsSize)[1]', 'BIGINT'), 0), -- LogsSize ISNULL(st.properties.value('(/Indexing_DbStats/@reportsSize)[1]', 'BIGINT'), 0), -- ReportsSize ISNULL(st.properties.value('(/Indexing_DbStats/@maintenanceSize)[1]', 'BIGINT'), 0), -- MaintenanceSize ISNULL(TBH.indexedItems, -1), -- IndexedItems ISNULL(TBH.applicationSize, -1), -- ApplicationSize ISNULL(TIM.lastServerName, ''), -- LastIndexServer ISNULL(TIM.migrationReason, -1) -- MigrationReason FROM App_IndexDBInfo idx WITH(NOLOCK) INNER JOIN Idxdbstate st WITH(NOLOCK) ON idx.id = st.dbId INNER JOIN APP_BackupSetName bs WITH(NOLOCK) on bs.id = idx.backupSetId INNER JOIN APP_Application app WITH(NOLOCK) on app.backupSet = bs.id INNER JOIN #tempValidApps apps ON APP.id = APPS.appId INNER JOIN APP_Client CC WITH(NOLOCK) on cc.id = app.clientId INNER JOIN app_client mm WITH(NOLOCK) on mm.id = idx.currentIdxServer INNER JOIN APP_iDAType ida WITH(NOLOCK) on APP.appTypeId = IDA.type LEFT JOIN #tempSCIndexBS TBS ON BS.id = TBS.bsId LEFT JOIN #tmpBackupHistory TBH ON idx.id = TBH.dbId LEFT JOIN #tmpIndexMigrations TIM ON TIM.dbId = idx.id AND TIM.currentIdxServer = idx.currentIdxServer WHERE (TBS.bsId is NULL OR IDX.dbName = APP.GUID) AND (IDX.type != 200107 OR @csSPVersion < 20) UPDATE T SET T.LastBackupTime = MAXAF.cTime FROM #tmpDetails T CROSS APPLY (SELECT TOP 1 cTime FROM archFile AF WITH(NOLOCK) WHERE AF.cs_afname = CHECKSUM('IdxCheckPoint_' + CAST(T.backupSetGUID AS NVARCHAR(36)) + ':' + CAST(T.dbName AS NVARCHAR(36))) AND AF.name = 'IdxCheckPoint_' + CAST(T.backupSetGUID AS VARCHAR(36)) + ':' + CAST(T.dbName AS VARCHAR(36)) and af.isValid = 1 ORDER BY cTime DESC) MAXAF WHERE T.idxEngineType = 1 SELECT t.clientId AS '@ClientId', REPLACE(t.Client, ',', '&comma&') AS '@Client', t.AppType AS '@AppType', REPLACE(t.Backupset, ',', '&comma&') AS '@Backupset', REPLACE(t.subclientName, ',', '&comma&') AS '@subclientName', t.dbName AS '@dbName', REPLACE(t.IndexServer, ',', '&comma&') AS '@IndexServer', t.uptodate AS '@uptodate', ISNULL(t.lastTimeDbUptodate,-1) AS '@lastTimeDbUptodate', ISNULL(t.lastPlayedJobTime,-1) AS '@lastPlayedJobTime', ISNULL(t.lastCompactionTime, 0) AS '@lastCompactionTime', ISNULL(t.errorlvl,-1) AS '@errorLvl', REPLACE(t.errordesc, ',', '&comma&') AS '@errorDesc', ISNULL(t.LastBackupTime,-1) AS '@LastBackupTime', ISNULL(t.LastBackupForDb,-1) AS '@LastBackupForDb', (CASE WHEN t.idxEngineType = 2 THEN 'Dynamic' ELSE 'Classic' END) AS '@IndexType', ISNULL(t.LastIdxErrorTime,-1) AS '@LastIdxErrorTime', ISNULL(t.FirstBackupForDb,-1) AS '@FirstBackupForDb', ISNULL(t.ScIdxConversionTime,-1) AS '@ScIdxConversionTime', t.DatabaseSize AS '@DatabaseSize', t.LogsSize AS '@LogsSize', t.ReportsSize AS '@ReportsSize', t.MaintenanceSize AS '@MaintenanceSize', t.IndexedItems AS '@IndexedItems', t.ApplicationSize AS '@ApplicationSize', t.LastIndexServer AS '@LastIndexServer', t.MigrationReason AS '@MigrationReason' FROM #tmpDetails t END ELSE BEGIN RETURN END --------- END SURVEY QUERY --------- --------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY --------- SET QUOTED_IDENTIFIER ON SET NOCOUNT OFF --------- END - GENERATED CODE ---------