Trace database cleanup in an efficient way

The Trace parser is an excellent tool for troubleshooting business functionality within Microsoft Dynamics AX 2012 and Dynamics 365 for Finance and Operations. Your Trace database can quickly grow large and it does affect the tools’ performance adversely.

Removing the old trace collections one-by-one is time consuming. You could utilize a stored procedure for cleaning up your AX trace database efficiently in SQL:

-- Replace AXTrace with your database name
USE [AXTrace]
GO
 
-- Truncate transaction log to reduce size
DBCC SHRINKFILE (N'AXTrace_log' , 0, TRUNCATEONLY)
GO
 
-- Iterate through the list of traces in the database
DECLARE cur CURSOR FOR 
       SELECT [TraceId] FROM [dbo].[Traces]
 
DECLARE @Id AS int
 
OPEN cur;
FETCH NEXT FROM cur into @Id;
 
-- Remove all traces in the database with the DeleteTrace stored procedure
WHILE (@@FETCH_STATUS = 0)
BEGIN
       EXEC [dbo].[DeleteTrace] @TraceId = @Id
       FETCH NEXT FROM cur into @Id;
END
 
CLOSE cur;
DEALLOCATE cur;
 
-- Truncate transaction log to reduce size
DBCC SHRINKFILE (N'AXTrace_log' , 0, TRUNCATEONLY)
GO