delete historical Audit records for few object type code using SQL query

(0) Share

Report

Posted on by Community Member

Hi all, we have huge Audit history table and most of the time Audit history view timeout. I tried to delete Audit Log using CRM Audit Log Management section. but it keep crashing and CRM not responding around 75% of deletion process.

I know SQL deletions not supportive way we have to take responsibility of possible data loss or system crash. but we can easily delete record by entity type or by created date older than x number of months etc.... also we have sync process with another system and this sync user update huge amount of data every night. so using SQL I can delete audit history for this user as well.

but I just need to know how risky is this ?

Would like to get experts ideas please

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

Hello,

I would extremely recommend not go SQL Deletion way.

If OOB deletion feature crashes you should get in touch with MS and open a ticket. MS Support engineers will try to help you with your issue.

Was this reply helpful? Yes No
Suggested answer

tw0sh3ds 5,600 on at

Like (0)

Report

Hi,

If I were you I would try to do this programatically:

msdn.microsoft.com/.../microsoft.crm.sdk.messages.deleteauditdatarequest.aspx

And if this is not working also - please enable traces and check what exactly is time-outing - maybe time-out can be increased. That's a start.

As for the SQL deletion - officially you should not do this. But unofficially (it may look strange on the Internet where everything is official :P) I did that many times in the systems with Audit logs so big that the "Delete" button did not work and I never encountered any problems. This table has no relationships with most important CRM tables, so you can delete whatever you want, it will just not show up in the audit logs.

Of course I'm only speaking from experience here - you will not find any documentation for that.

If you have trouble removing audit logs in supported way, then you are probably auditing too much data - try to narrow the amount of audited fields and entities.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Andri and Pawel....

Yes way too much data in Audit table, we have a synchronizer service which update crm records from our billing engine , which update humongous data and consequently add to audit history and its too large.

I have already started go through each entity where audit enable and disabling unwanted fields.

I might take a back up and then delete very old records from sql and see what happens if all ok then I can clear bit more and reduce the size of Audit table.

Thank you for sharing ideas

Was this reply helpful? Yes No
tw0sh3ds 5,600 on at

Like (0)

Report

Hi,

Have you tried to do this using SDK like I suggested first? Was it time-outing the same way as it was from UI?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Pawel no I didn't try delete from sdk , need to try it too.

I checked the event viewer, I cannot see time out error, it says thread was being aborted.

Failed to drop Partition on AuditBase. Exception message Thread was being aborted.

DeleteAuditData

Microsoft.Crm.Extensibility.InternalOperationPlugin, Microsoft.Crm.ObjectModel, Version=8.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

Microsoft.Crm.Extensibility.InternalOperationPlugin

Unhandled Exception: System.Threading.ThreadAbortException: Thread was being aborted. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.Web.Services.Protocols.LogicalMethodInfo.Invoke(Object target, Object[] values) at Microsoft.Crm.Extensibility.InternalOperationPlugin.Execute(IServiceProvider serviceProvider) at Microsoft.Crm.Extensibility.V5PluginProxyStep.ExecuteInternal(PipelineExecutionContext context) at Microsoft.Crm.Extensibility.VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context

Was this reply helpful? Yes No
Suggested answer

tw0sh3ds 5,600 on at

Like (0)

Report

Hi,

Looks like standard ASP .NET error

I would try to change executionTimeout for your CRM in web.config file:

stackoverflow.com/.../7920473

Was this reply helpful? Yes No