1m table cannot be dropped during a rollup in a SQL Server based PI repository causing the Repository database to grow very large (4228866)

An obsolete 1m table cannot be deleted during rollups in the SQL Server-based PI (MSSQL PI) repository. This can cause the repository database to grow very large in size.

An error similar to the following may appear in the domain agent manager log:

WORKAROUND

Run the attached script in the SQL Server-based PI (MSSQL PI) repository.

This script is not maintained or supported by Quest Support.

--------------------------------------------
-- Create 1m tables created 3 days ago
--------------------------------------------

--Drop funcion if exsits
USE [PI Repository Database]

IF EXISTS
(
    SELECT * FROM sysobjects WHERE id = object_id(N'ConstructDropObsoleteTableSQL')
    AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION ConstructDropObsoleteTableSQL
GO

--Define function to prepare drop table SQL
CREATE FUNCTION ConstructDropObsoleteTableSQL(@tableName varchar(100)) RETURNS varchar(256) AS
BEGIN
	DECLARE @sql VARCHAR(256);
	DECLARE @index int;
	DECLARE @dateTimeString VARCHAR(100);
	DECLARE @tableDate DATE;
	DECLARE @dayDiff int;

	SET @index = (SELECT PATINDEX('%1m_%', @tableName));
	SET @dateTimeString = (SELECT SUBSTRING (@tableName, 6, 12));
	SET @dateTimeString = (SELECT REPLACE (@dateTimeString, '1m_', ''));
	SET @dateTimeString = (SELECT SUBSTRING (@dateTimeString, 0, 9));
	SET @tableDate = (select convert(datetime, @dateTimeString, 112));
	SET @dayDiff = (SELECT DATEDIFF(DAY, @tableDate, GETDATE()));

	IF @dayDiff >= 3
		SET @sql = FORMATMESSAGE('DROP TABLE %s;', @tableName);
	ELSE
		SET @sql = '';

	RETURN @sql;
END
GO

--Get all 1m table and compare date to determine if it is obsoleted (1m table created 3 days ago).
DECLARE @tableName VARCHAR(100);
DECLARE @sqlToDropTable NVARCHAR(256);
DECLARE @table_name_cursor as CURSOR;

SET @table_name_cursor = CURSOR FOR
SELECT
	[Tables].name AS [TableName]
FROM sys.tables AS [Tables]
WHERE [Tables].name like N'%_1m_%'
ORDER BY [Tables].name;

OPEN @table_name_cursor;
FETCH NEXT FROM @table_name_cursor INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sqlToDropTable = dbo.ConstructDropObsoleteTableSQL(@tableName);
	PRINT @sqlToDropTable;
	exec sp_executeSql @sqlToDropTable;
	FETCH NEXT FROM @table_name_cursor INTO @tableName;
END
CLOSE @table_name_cursor;
DEALLOCATE @table_name_cursor;

STATUS

The SQL Server PI repository requires a minimum Service Pack 1 or Service Pack 2 for SQL Server 2016 Standard edition. Please consult the appropriate Foglight for Databases deployment guide for the product requirements for your installed cartridge version.

Upgrade the SQL Server PI repository instance if it is below the minimum required level.