We have seen an issue with Audit Records that can cause some performance issues especially around creating versions. We will address this long-term, but for now, there is a simple workaround that can be used to clean up the Audit records.
1. First, we want to run the script below against the DESTINIData database (Projects database) to see the size of each table, if the Audit record table is in the 10 of the list returned, then you can follow the steps below.
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB DESC, t.Name
2. Next run this stored Procedure against the DESTINIData (Projects) database and then right-click to Refresh the database.
The Data in bold can be adjusted:
- -60 = number of days to keep for all the AUdit records for all the estimates
- 200000 = the number of records that are deleted at a time
Depending on the amount of records, this can run for a while
/****** Object: StoredProcedure [dbo].[sp_DeleteAuditRecords60DaysOld] Script Date: 4/14/2022 5:36:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Son Nguyen
-- Create Date: 4/6/2022
-- Description: Deletes [AuditRecords] older than 60 days
-- =============================================
CREATE PROCEDURE [dbo].[sp_DeleteAuditRecords60DaysOld]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
-- Insert statements for procedure here
DECLARE @DaysToKeep int = -60;
DECLARE @DeletedRows int = 1;
WHILE (@DeletedRows > 0)
BEGIN
-- Delete some small number of rows at a time
DELETE TOP (200000) [AuditRecords]
WHERE [CreateDatetime] < DATEADD(DAY, @DaysToKeep, GETDATE())
SET @DeletedRows = @@ROWCOUNT;
END
END
GO
Open the Programmability > Stored Procedure folder and you will see the stored Procedure we just created - sp_DeleteAuditRecords60DaysOld
3. Right Click the stored procedure and click Execute Stored Procedure
4. Click the OK button and the Stored Procedure will run until it cleans up that table.
When the script is completed you will see the results as Value 0. You can also rerun the script in step 1 to check the size of the table.
