Huge confluence database

[thomas.merkt@mt.com]

Hi,

I realized that the size of our confluence database increased a lot during the last months. The current size is about 63 GB and it increases daily at a rate of about 10 GB though it doesn't contain a lot of content.

I when I checked the top tables on SQL server side I saw that the most spaced is used by tables "AUDITRECORD", "AUDIT_AFFECTED_OBJECT" and "AUDIT_CHANGED_VALUE" (about 62 GB!). I had a closer look at the "AUDITRECORD"-table and found many "Users and groups"-events that referred to changes in our AD. It seems that is cause I configured our whole AD-forest as user directories in Confluence.

After that I changed the Audit Log Settings the way that the events should get kept only one day instead 3 years and I hoped that most of the records would get deleted then. Unfortunately that didn't happen. Though I saw that some 10000 records got deleted the next day there were even more records than before. How can I get rid of these audit records ? Could I just delete them in the SQL-Server Management Studio or is there a setting in the confluence administration to deactivate the auditing completely and force the deletion of the existing auditing-records from the table ?

Best regards,

 Thomas Merkt

Mettler-Toledo (Albstadt) GmbH

Unter dem Malesfelsen 34

72458 Albstadt

Phone         + 49-74 31-14-3 82

[thomas.merkt@mt.com]

Hi Ann,

thanks ! Unfortunately I was still not able to delete the records from the AUDITRECORD-table. After executing "DELETE FROM AUDITRECORD" the command was running for several hours and I even realized that the number of records decreased step by step until 0. But directly after that the record-number increased again until it reached the original number of 98 million records ! Then I got this error message in the SQL Management Studio:

Msg 547, Level 16, State 0, Line 1

The DELETE statement conflicted with the REFERENCE constraint "FK_CHANGED_VALUE_RECORD". The conflict occurred in database "Confluence_Dev", table "dbo.AUDIT_CHANGED_VALUE", column 'AUDITRECORDID'.

The statement has been terminated.

So it seems that a rollback was performed and at the end no records have been deleted at all.

Beside that the size of the TEMPDB.DF-file also increased drastically up to 185 GB !

Do you have any other hints how I could delete all records from the AUDITREORD-table ? Now that begins to be a real pain on our SQL server :-(.

Regards,

Thomas

[Benjamin Wiens]

Hello,

I believe the large AUDITRECORD table is a result of not having an ldap filter in your user directory. You can disable the constraints involved with the auditrecord table (such as foreign keys) and then truncate the tables mentioned above. Afterwards enable the constraints again. It should not cause any issues to your confluence installation & data.

Ben 

[thomas.merkt@mt.com]

Hi Ann,

thanks again ! The truncate-statement worked fine for all AUDIT-tables except table AUDITRECORD. I got an error message there which stated that it has referenced tables and so it cannot be truncated. I just executed a DELETE FROM AUDITRECORD command then to get rid of all these records (about 98 million !), but while this statement was executed I realized that the transaction log of the confluence database increased heavily (though the recovery model of the database is simple). When it reached the size of about 100GB after some hours I canceled the command because otherwise our logfile-volume would have run full and our SQL server would stop working. To minimize the risk I added another volume to our SQL-Server, changed the confluence logfile location to this drive and ran the delete command again. So I hope that on Monday the deletion will be finished and the space out the new volume will be sufficient for that. But at least I don't put our other databases at risk with that approach ...

Do you know a better way to clean the AUDITRECORD table without this immense logfile size increase ?

About the AD limitations you mentioned I will have a closer look at that after the database size issue is solved. Especially the user and group filters you mentioned sound interesting.

I wish you a nice weekend !

Regards,

Thomas

[AnnWorley]

Hi Thomas,

I did some research on SQL server transaction logging (Turn off Transaction Log?) and it doesn't seem like you can temporarily disable it. Your strategy of pointing the log at a larger volume is a good workaround.

I am hoping this deletion procedure is a one time deal and you can limit the audit records in the future by reducing the LDAP events that are being logged.

I wish you a great weekend as well!

Cheers,

Ann

[thomas.merkt@mt.com]

Hello Ann,

thank you very much for your quick reply !

That sounds good that I could just delete the obsolete records from the audit tables.

Since we don't need any of the log entries I would delete all entries via the TSQL "TRUNCATE TABLE"-command. So I would execute the following commands:

TRUNCATE TABLE AUDITRECORD

TRUNCATE TABLE AUDIT_AFFECTED_OBJECT

TRUNCATE TABLE AUDIT_CHANGED_VALUE

Would that be OK or do you see a risk doing that ?

I didn't understand your last question regarding our AD-configuration. What did you exactly mean with "Have you changed your AD configuration to reduce the entries .." ?

Best regards,

Thomas

[AnnWorley]

Hi Thomas,

The truncate statements look fine, if you want to delete all the entries. Please do back up your database first for obvious reasons.

I brought up AD because you mentioned:  "It seems that is cause I configured our whole AD-forest as user directories in Confluence."

There are several ways to limit how many users and groups Confluence "sees" from AD, including, but not limited to setting the Base DN further down in the directory tree, and setting up user and group filters. This article is a good place to get started if you want to pursue it: How to change the number of users synchronized from LDAP to Confluence

I look forward to hearing whether you get the database down to a manageable size. Also, I am curious about the possibility of reducing AD changes you are seeing in the audit records.

Cheers,

Ann

[AnnWorley]

Hello,

Yes, you may delete the obsolete records in the SQL-Server Management Studio. In the future, you should not have to keep deleting them manually because of the setting you changed, which deletes them after a day.

Our documentation does not mention a way to disable audit logging altogether: Audit log

Have you changed your AD configuration to reduce the entries you report that are AD changes?

Thanks,

Ann