Gaps of missing data and agent crashing in SQL Auditing (4369943)

Gaps of missing data and agent crashing in SQL Auditing

When the Change Auditor agent in SQL is capturing the Add, Update and Remove events that we configure for databases it works normally smoothly until a SQL Backup Transaction Log is executed, at that time, the Change Auditor agent stops capturing or displaying database events. After the transaction log backup ends, the agent running on the SQL Server fails to resume the LSN module to continue capturing events until the agent service is manually restarted.

SQL DLA auditing crashes after a transaction logs backup happens due to LSN pointers reset and the agent service has to be manually restarted to resume auditing operations. This process mainly depends on how SQL Server handles it.

As a best practice, make sure that the coordinator and agent are running the latest available release of Change Auditor and that both are in the same version (whenever possible)

Further testing in trying to reproduce the issue indicated that since the issue was occurring when a transaction log backup happened and this process is managed by SQL Server, it seems the LSN pointer reset was not being properly handled in versions 2017 RTM 14.0.1000.169 (2017) and below of SQL Server, causing the Change Auditor agent to stop from working correctly.

Therefore, it is likely that this process was improved in more recent versions of SQL Server, as per our test results, we did not experience the issue in a test environment using SQL Server 2017  CU31 version (14.0.3456.2) or newer.

https://learn.microsoft.com/en-us/troubleshoot/sql/releases/download-and-install-latest-updates