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.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center