Question

audit trail database increasing

Audit Trail

Updated April 17, 2015

asked on April 16, 2015

Would removing the audit log files in Audit File location have any adverse affects on audit trail functionality? When looking at the date range that i loaded in AT configuration which is a month worth of logs it is only 2GB but the AT SQL DB is at 44GB and increasing dramatically, what would cause the SQL DB for AT to increase dramatically?

i was looking at articles in here and i see this:

https://answers.laserfiche.com/questions?q=audit+trail+database+increasing

I checked eventvwr and i see error:

An error occurred:
ERROR [40001] [Microsoft][SQL Native Client][SQL Server]Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at AuditDBService.Purger.DeleteEvents(OdbcCommand cmd, String where, Boolean oracle)
at AuditDBService.Purger.PurgeUnwanted(EventsDeleted callback)

I see error logs for this up until i stopped Audit Trail Services.

0 0

Replies

replied on April 16, 2015

scratch that link, its this link:

https://answers.laserfiche.com/questions/46623/Audit-trail-totals-are-constantly-changing

0 0

replied on April 16, 2015

Would removing the audit log files in Audit File location have any adverse affects on audit trail functionality?

Yes. There are reasons to move audit files around, but Audit Trail acting strangely is not one of them. Your stack trace indicates that audit trail is attempting to delete rows from the audit database but that it's deadlocking. Is this a recent version of Audit Trail? We improved the efficiency of our event deletion commands for 9.1 (IIRC), so if it's older than that I would recommend upgrading.

0 0

replied on April 16, 2015

The timeline that was provided to us by the client is only gives me a window of migration, the upgrade will be scheduled later.

Basically the DB for audit trail grew by 42GB in 11hrs, so this mean either turn off audit trail services or upgrade?

0 0

replied on April 16, 2015

The error message you posted is suggestive of something that might improve with an upgrade, but with so little information I wouldn't guarantee that upgrading would fix anything. But it also sounds like audit reports are not critical to the customer, so maybe stopping the service now until you have time to properly troubleshoot is a good idea.

If the error above happens regularly, I think you would see a lot of growth in the ldf file and that space could be reclaimed. If it's the mdf file that's taking up the space, I would investigate what events are currently loaded into the database. If it's not able to delete events it could contain far more than the 30 days that you have requested. In that case, the easiest solution might be to just delete the entire audit database and let it start over.

0 0

replied on April 17, 2015

i did that, and loaded only 3 days of audit data and 30mins later the mdf for AT DB ate 1.5GB already, so i had to stop the AT services.

same error logs in the eventvwr.

0 0

replied on April 17, 2015

The logs are usually compressed, so 2GB worth of logs may take a lot more space in SQL.

0 0

You are not allowed to follow up in this post.

Question

Question

audit trail database increasing

Replies

Sign in to reply to this post.