web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Manually purge AuditBa...
Microsoft Dynamics CRM (Archived)

Manually purge AuditBase table

(0) ShareShare
ReportReport
Posted on by Joe Woltering 12,163

The system I am currently working with had auditing enabled on way too many fields and now the logs have grown to enormous levels-- 65 million rows in some cases! I am unable to delete old logs using the interface as it will time out and the SQL logs will expand and consume the entire disk. How can I manually clear these logs?

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Josh Thompson Profile Picture
    Josh Thompson 1,115 on at

    I'm assuming you are trying to use the audit log cleanup built into the interface?

    I will also assume this is on-prem - you will probably have to manually start deleting data from those tables using SSMS. If your logs are a problem, I would look for ways to improve their performance and try to keep them in check. I would set the base size to 8096MB (or some factor of 8GB depending on your normal runnning log size). Set growth to 8096MB. This will limit the number of VLF's that are created if you're currently running with a smaller fixed-size autogrowth or are set to "grow by percentage".

    Take a full backup.

    Start by indentifying the data you want to clean up from auditbase and identify the rows and the query you'll need to accomplish that. I do not recall seeing an MSKB on auditbase cleanup but there is one for some of the other tables that grow. If you haven't cleaned those up prior to applying other rollup's here is the link for that: support.microsoft.com/.../968520

    This should give you a pretty good idea of what you'll want to do with auditbase. Keep in mind it is unsupported so make sure you have good, verified backups in place in the event something goes awry.

    Good Luck

  • Mohammad Atif Profile Picture
    Mohammad Atif on at

    you may want to increase the timeout for this operation like oledb and extended timeout settings in crm support.microsoft.com/.../918609

    Thank  you..

  • Josh Thompson Profile Picture
    Josh Thompson 1,115 on at

    The timeout would normally be my first suggestion but since the process that is running within the time alloted is filling the disk this isn't going to fix the issue. If you do extend the timeout, you'll need to increase those log files and get more disk space. Unfortunately there aren't any models where you can cut the logging of a delete operation, only the logging of bulk copy operations/inserts etc, all deletes are logged.

  • Verified answer
    Joe Woltering Profile Picture
    Joe Woltering 12,163 on at

    Well I solved my issue by using TRUNCATE on the AuditBase table. Obviously this is not receommended as it blows away the entire contents of your Audit logs but drastic situations called for drastic measures-- I also reduced my database by 80Gb!. Now I can go through and reduce the amount of auditing and bring it down to reasonable levels.

  • Josh Thompson Profile Picture
    Josh Thompson 1,115 on at

    Well yes, that is one way to accomplish it. TRUNCATE isn't logged, I left that off my answer as I had, incorrectly it seems, assumed you wanted to keep some Audit data in the table.

    You can do a DBCC SHRINKFILE on the database to reclaim that size in the database file which will reclaim a lot of that space and reduce the size of your backups etc. You'll also want to do index maintenance on the auditbase table and do an update statistics now that the table is "starting over". I would then recommend a full backup which should reset your log file as well and give you a clean slate going forward.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans