You are viewing limited content. For full access, please sign in.

Question

Question

Audit Trail database increasing

asked on May 18, 2020

Hello,

Customer just recently moved from Avante to Rio and update LF to 10.4.

They are having an issue where their AT MDF file is growing exponentially.

DB was 135GB when they noticed it.  We disabled the AT Service.

We checked and Date Range is set to 30days.

We deleted the previous DB and turned the service back and withing 5 Hours the DB file had grown to almost 7GB. 

Customer disabled the service again.

I have looked in the Application Event Logs on the SQL server and I do not see any errors pointing to AT.

Any pointers greatly appreciated.

Jeff Curtis

0 0

Answer

SELECTED ANSWER
replied on June 22, 2020

No, deleting the log files would destroy the audit data. A large number of log files could be an indicator of a busy repository. Or it could be a sign that the rollover size for the logs has been set too low.

It's probably best that you open a support case so we can take a closer look rather than guessing from screenshots.

1 0

Replies

replied on May 18, 2020

Remember that - depending on your configuration - you are asking Audit Trail to load into a database information about every action that takes place in the repository over the course of an entire month. Without knowing anything about the customer, I don't see why that wouldn't take 135 GB of storage. If you want to track down the largest usage, SQL server can give you breakdowns on a per-table basis. Since the tables each handle a class of events this will suggest what actions are leading to the most storage usage.

1 0
replied on May 19, 2020

Hello Brian,

Thanks for the reply.  

Customer config is pretty straightforward from what I can see:

1. Just Repository no added Folders

2. Date Range- 30 days

I will see about getting a output of the Tables

Thanks,
Jeff Curtis

0 0
replied on May 19, 2020

The easiest way to log too much data is to record view events. You should check if that's part of what you are recording and consider if you need them.

0 0
replied on May 20, 2020

Hello Brian,

Thanks for the information

Customer replied back with the output and it looks like the dbo.audit_entry, dbo.audit_event, and dbo.audit_parent are the three tables with large amounts of records, 5 Million+ for each.

I am also seeing the dbo.audit_event has a high amount of indexes, 2.2 Million.

In looking at my AT DB, the same tables are showing most of the data.

Is this correlated to the Auditing Tab/Audit Events for users/groups in the LF Admin Console for Named users and the Auditing Configuration in LFDS?

I believe this is the case and in order to resolve this we should at trimming down the audit events correct?

Appreciate your assistance,

Jeff Curtis

0 0
replied on May 20, 2020

Redo the count and group it by event type to check which ones are more frequent. That should give you more insight into whether your repository gets a lot of actions or your audit settings are too broad.

0 0
replied on May 21, 2020

Hello Miruna

Thanks for the tip.  I checked my Tables and found that 81% of the Event_Type were logon/logoff entries.

If I find this to be the same for the customer, what is the best route to set the logging to keep the DB from growing too large?

Thanks,
Jeff Curtis

0 0
replied on May 21, 2020

You can break it down more by application and user. For your case, I expect you're seeing Audit Trail logging in to retrieve logs. You can create a dedicated user (doesn't need a license) and turn off auditing for it.

0 0
replied on May 22, 2020

Hello Miruna,

When you say dedicated user, could we create a local account on the LF server and use that to run AT? 

I looked and I am not sure how to disable auditing for the dedicated user.

Appreciate your help,

Jeff Curtis

0 0
replied on May 26, 2020

Have you run a report on your Audit Trail database? As Miruna says, I expect you'll see that most of the events are the Audit Trail service logging in with the account you configured for it so that it can get new events. This is pretty common in non-production repositories - there is no real activity in the system, so the logs are dominated by system events like that.

Either the logged events or the configuration page can show you what account AT is using, and you can adjust your auditing settings to not log events for that account. It often makes sense to do that for a test or demo system, for production it depends on how complete the end user needs or wants the logs to be.

All that said, production systems do enough other work that login and logoff events don't significantly impact log size. To answer the user's questions, you need to look at the user's data.

0 0
replied on June 2, 2020

Hello Brian and Miruna,

I connected with the customer and it appears it is the account running AT that is causing the issue.

Customer is on 10.4.2 and for some reason, even though, I am logged in as ADMIN to their LF Admin Console, there is no Auditing Tab for the users, only for the Everyone Group.  ADMIN has Manage Audit Settings.  I am also on 10.4.2 and I have the Auditing Tabs for users.

Am I missing something here, as without the auditing tab, the new account we want to create will pull all of the Auditing from the Everyone Group.

Thanks again for the help,

Jeff Curtis

0 0
replied on June 2, 2020

It sounds like they're using the Starter edition of Audit Trail.

0 0
replied on June 3, 2020

But, 135GB still sounds like way too much disk space for Audit Trail logging in and out in the past 30 days.

How many login events did you find in the database? How many events are in the database overall? What are the sizes of the MDF and LDF files on disk? Is the database set to simple recovery mode or full?

0 0
replied on June 9, 2020

Hello Miruna,

Below are the three top table entries. As you can see it drops off after the Parent table

LF_Audit.mdf is 6.4gb 

LF_Audit_log.ldf is 2.5gb

They sizes are set at this as the customer stopped the service after a 5 hour period after they noticed the excessive growth.

Also-Customer is on RIO, but how can I tell what version of AT they are using. I thought RIO customer had AT Advanced.

I am checking with the customer on the recovery mode.

Thanks Again,

Jeff Curtis

0 0
replied on June 9, 2020

Most of the audit events are entry events, which is normal for production repositories.

I think I'm seeing the misunderstanding. Are you thinking that the 10 GB of disk is for 5 hours of audit events? You have told AT that you want to be able to run reports on 30 days of audit data, and it is loading data from the last 30 days into the database. The configuration UI should show what kind of progress it is making through those logs, and from there you can extrapolate. It's not perfect, but you should have an idea if it's going to get close to 135 GB again.

0 0
replied on June 10, 2020

Hello Brian,

Thank you for your reply.

I have looked in the Configuration UI, but I do not see where it would show the progress for the logs.  Should this be in the Data Ranges or Database sections?

Appreciate your feedback,

Jeff Curtis

0 0
replied on June 10, 2020

It would be under Date Range. You should see a node for each repository and underneath it the ranges you specified and the number of logs that cover that range and how many of them have been loaded in the DB.

1 0
replied on June 16, 2020

Hello Miruna/Brian,

Customer sent a screenshot and all I see is Last 30 Days and it is Queued.  There are now sub entries.

Now since the service is turned off, we would not see anything loading correct? So to truly check this, should we start the service and see if we get a loading entry with %?

Appreciate the feedback,

Jeff Curtis

0 0
replied on June 16, 2020

Yes, the service is responsible for retrieving the list of logs and loading them. You won't get any progress if it's not running.

0 0
replied on June 19, 2020

Hello Miruna,

After restart of the AT Service below is what he shows after a 90min run

So it looks like a lot of log files being created that need to be deleted, correct?

Appreciate the feedback,

Jeff Curtis

AT Queue.png
AT Queue.png (29.03 KB)
0 0
SELECTED ANSWER
replied on June 22, 2020

No, deleting the log files would destroy the audit data. A large number of log files could be an indicator of a busy repository. Or it could be a sign that the rollover size for the logs has been set too low.

It's probably best that you open a support case so we can take a closer look rather than guessing from screenshots.

1 0
replied on June 26, 2020

Thanks Miruna..I have opened an LF Support case

Jeff

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.