CRM 2011 Audit Partitioning

(0) Share

Report

Posted on by Resh

Hello CRM Experts,

I am using CRM 2011 and its connected to SQL Server Enterprise edition. The audit table is partitioned every quarter by CRM and we can see this from CRM web. As we want to retain the audit data, we have not deleted the old partitions till now. Now, the audit table has grown too large and causing some performance issue. What would be the best solution by which I can retain all the audit history and CRM performance also not affected?

thank you!

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

ScottDurow 21 on at

Like (0)

Report

Once you delete the audit partition the audit records will be completely removed for that date range. Perhaps you could create an archive organization by backing up and restoring the database as a different organization name, the importing it via deployment manager.

Was this reply helpful? Yes No
Suggested answer

Josh Thompson 1,115 on at

Like (0)

Report

If the audit table is physically partitioned as is possible with SQL EE, then really, it shouldn't be causing any performance issues. The only thing I could see is if you were having issues during the read (query) of old audit data. When you say you are having performance issues, what exactly seems to be the issue?

Scott's solution is a clever one, you could do this quarterly for purposes of looking at old audit data. that db is going to get big pretty quickly and if you did this periodically, the data would only be as fresh as the restore as the partitions would be lost after you did the restore. You could also look at pulling the audit data into another database altogether on a scheduled basis. You'd have to understand the layout of the data, it isn't that stright-forward but you'd have it forever.

Was this reply helpful? Yes No
Resh on at

Like (0)

Report

The AuditBase table properties shows that it is partitioned and I can see the number of partition it has in SQL server. We don't use Audit data read much but the application which is integrated to CRM is having performance issues. Last year, Microsoft guys who evaluated the system recommended to clear the old Audit History to fix the performance issue (which we did not implement at all). As we want to retain the Audit, I am looking for how to archive the old audit such a way that we can make it easily available whenever needed.

As you mentioned, is it possible to sync Audit table in one org to another in a different server?

Was this reply helpful? Yes No
Pablo J. Cosimo 50 on at

Like (0)

Report

You can try use MSCRM Toolkit tool from codeplex and download the audit info into excel file.

Was this reply helpful? Yes No