Audit log not getting archived

[tester22]

Hi,

 

We have an problem that our audit log isn't getting archived:

 

Our setting is defined so it should archive after 2 million rows.

 

I havn't seen anything in the logs regarding this.

Any pointers of where to look?

[support]

Hello tester22,

 

We might need to look at the health of your system, and cannot request that on public forums.

 

If you have an active maintenance contract for our software, could you please log your support request via the following page - https://www.clickstudios.com.au/support.aspx

Thanks

Click Studios

 

[tester22]

Hi,

 

Managed to solve it.

If the archiving service crashes while copyting data from audit to auditarchive tables it creates conflicts and the process crashes.

 

After removing the primarykey from auditarchive the archiving works as expected but I am getting duplicates.

The running:

 

WITH cte AS (SELECT[AuditID], [UserID], 2 row_number() OVER(PARTITION BY AuditID, UserID ORDER BY AuditID) AS [rn] 3 FROM [passwordstate].[dbo].[AuditingArchive] 4 )DELETE cte WHERE [rn] > 1

 

Cleans the table and I could re-enable the primary key in the archiving table.