Check AOS event logs and SQL Server error logs for key messages using DynamicsPerf
As part of general monitoring of Dynamics AX overall system health and/or troubleshooting, you’ll want to check the Windows event logs in your AOS server(s) and SQL Server error logs. If you have DynamicsPerf (Performance Analyzer for Microsoft Dynamics) installed, you can quickly check for key messages and if necessary, export and move them all together easily, through backing up and restoring DynamicsPerf.
Should the need arise, you can also then backup DynamicsPerf and send it to the relevant AX team(s) at Microsoft.
You can find an example script attached below, which is checking for some key messages in relation to performance; it works for the latest version, 2.0 and also 1.20 (as the two tables in question have not changed between versions). You can check for other messages (e.g. in relation to troubleshooting other scenarios) just by changing the criteria between the quotes and wildcards (%), i.e. the parts highlighted below.
/**** Disclaimer:
Microsoft provides programming examples for illustration only, without warranty either expressed or implied,
including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. This mail message assumes
that you are familiar with the programming language that is being demonstrated and the tools that are used to create and debug procedures.*/
SELECT *
FROM AOS_EVENTLOG
WHERE MESSAGE LIKE ‘%DELETE THE SPID%’
SELECT *
FROM SQLERRORLOG
WHERE LOGTEXT LIKE ‘%I/O requests taking longer than 15 seconds to complete on file%’
How it works in v2.0
You can find the installation steps here, these blog posts need to be followed in order:
https://blogs.msdn.microsoft.com/axinthefield/dynamicsperf-2-0-deployment-guide/
https://blogs.msdn.microsoft.com/axinthefield/dynamicsperf-2-0-installation-for-dynamics-ax/
The last 14 days of SQL Server error logs are collected using the stored procedure DYNPERF_COLLECT_SQL_ERRORLOG, which is part of the scheduling engine that is installed during the steps from the first blog post above (after the initial data collection, a delta is collected periodically during the day and records older than 14 days are purged by the stored procedure sp_purgestats as part of the scheduling engine). Further details: https://blogs.msdn.microsoft.com/axinthefield/dynamicsperf-2-0-scheduling-engine/
In the second blog post above, you’ll see there is a SQL Server Agent job ‘DYNPERF_COLLECT_AOS_CONFIG’, which collects the last 14 days of Windows event logs from each of the AOS servers, in addition to AOS registry values. By default, this SQL Agent job is disabled, however if enabled (right click>enable) it would run daily at 6am. You can also verify and change the schedule of this SQL Agent job as required (as per the standard SQL Server procedure, described here: https://msdn.microsoft.com/en-us/library/ms191439.aspx)
How it works in v1.20
In case you already have v1.20 installed and aren’t yet ready to upgrade to the latest version:
The last 14 days of SQL Server error logs are collected using the SQL Server Agent job DYNPERF_Capture_Stats, which executes the stored procedure SP_CAPTURESTATS, which in turn executes SP_CAPTURESTATS_CORE (after the initial data collection, a delta is collected daily and records older than 14 days are purged by the SQL Server Agent job DYNPERF_Capture_Stats_Purge).
The last 14 days of Windows event logs from each of the AOS servers are captured as explained in this blog post: https://blogs.msdn.microsoft.com/axinthefield/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide-dynamics-ax-installation/
The relevant part is extracted for you below.
“CAPTURE AOS SETTINGS AND EVENT LOGS
The Capture AOS Settings and Event Logs collector will capture AOS configuration and event logs from each active AOS Server in the environment. This collector is initiated through the AOSANALYSIS.VBS vb script. It works in conjunction with the AOSANLAYSIS.CMD batch script to populate tables in the DynamicsPerf database with registry settings and two weeks of event logs for each AOS instance currently connected to the AX database.
• AOSANALYSIS. VBS – This script will populate tables in the DynamicsPerf database with registry settings and two weeks of event logs for each AOS instance currently connected to the AX database.
• AOSANALYSIS.CMD – This is the batch script used to execute AOSANALYSIS.VBS. Two arguments must be passed:
o Database server and instance name
o AX database name
The Capture AOS Settings and Event Logs can be executed manually when needed or scheduled to run periodically.”
e.g. you could schedule the CMD batch script to run periodically using Windows task scheduler.
