trace

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
By |2020-03-23T13:21:53+01:00March 31st, 2019|Categories: AX 2012, Dynamics 365 for Finance and Operations|Tags: , , , , |0 Comments

Improve AX performace by fixing bad Query plans

Sometimes the Purchase invoicing department has complained that posting took a very long time to complete. We could correlate this with the slow performing SQL statements alert explained in my previous post to identify the cause, now it is time to improve AX performace by fixing bad Query plans. My colleague, Peter Prokopecz provided some great insights on resolving this problem.

Either an AX trace, or the SQL alert could reveal the bad code. In our case it was the standard functionality of matching Tax transactions with the General journal entries, which can be found in \Data Dictionary\Tables\TaxTransGeneralJournalAccountEntry\Methods\create. It is a very large insert_recordset statement with multiple inner and exists joins:

taxtransgeneraljournalaccountentry

(more…)

By |2016-01-08T17:57:45+01:00January 8th, 2016|Categories: AX 2012|Tags: , , , , , , , , |4 Comments
Go to Top