log cleanup for crm problem

Log cleanup to solve erratic CRM problems

At a loss about how to solve Dynamics CRM performance issues and weird faults you can’t fathom out? 

By Jamie Wilbraham and Graham Hill @ Metisc

This article covers a potential solution to inconsistent problems our clients have experienced that have had quite an impact on Dynamics CRM; including the CRM in Outlook, the CRM through a Browser, Workflows and System Jobs.

log 1

Some of our clients have been reporting a variety of faults with CRM.  The faults were not consistent, or happening with specific people, and they were not around a specific entity or business process. For example one of our client reported issues with workflows not running all the time, and issues with form automation (Javascript) not working inconsistently.

In all cases where clients were having issues, we had tracked through the Microsoft Blogs and Forums, tried different solutions, logged operations and reviewed event logs, involved Microsoft on occasions and generally had a frustrating time trying to solve the issues.

We were struggling to identify the faults and overcome them, rebooting the CRM Servers seemed to provide a temporary solution; when we discovered a workaround that seems to provide a permanent solution for many of our clients.

It’s simple, and easy to accomplish.  Also has some side benefits of reducing the size of the CRM database (for those using CRM online this may be a cost benefit).

So what have we been doing?
Logs

  • In many CRM installations when we started having what I would call general instability, we checked out the size of the system logs and of the audit logs (I said it was simple).Reviewing the CRM systems logs can be done by a user with Administrative privileges, to review the system logs, please go to Settings>> System Jobs

log 2

In the logs, it shows 5000 records by default.  Scroll through these records to get an idea of how many records are be being created on a normal day (rough estimate is OK with some simple maths).

Multiply the number of workflows created in a normal day by about 220 work days a year (I know the workflows keep running on weekends, but just getting a rough guess here). And you have a very rough idea of how many entries you will create in a typical year.  This number can be huge!

If you are technically minded, you can see the number of records in the logs using the following Transact SQL statement connected to the CRM database that holds the logs. In a SQL Management Console, enter this command once you select your CRM tenant database and open a query window>

SELECT count(*) FROM [AsyncOperationBase]

When it’s completed you can see at the bottom of the query screen how long it took.  Don’t be surprised if your CRM Database/SQL server response time is slow for a short time whilst this works behind the screen. As a rough example, in one case we worked through, a standard SQL query on the Account entity table returned all the rows in about 4 seconds, but the log entity table query took two and a half minutes to get a count.  This is an indication why we decided to remove as many of these records as possible.

log 3

Workflows

We found that for large or small clients, if there was a lot of activity around workflows and auditing, the table size was in the hundreds of thousands or millions of records.  Now in itself, the number of records with todays current server systems should not cause an impact.  But what we found in all clients we removed old log entries from, was that that the size of the database dropped considerably, the speed of the CRM system improved and the weird instability disappeared in most of the cases.

But be careful with this method as some clients may need these log records for legislative or tracking needs.

So be cautious about removing log entries from the CRM system log history, another safer option is to copy the system log entries to a secondary table or database to ensure you comply with the record retention policies  in your organisation or CRM system before deleting them.  If you are running on CRM Online you don’t have access to the CRM logs, so a tool may be required to copy these records out to another database before running the CRM utility to delete the log entries.

We also don’t recommend using SQL management console/SQL Queries for the deletion of these log entries.  Dynamics CRM is a complicated piece of software so we prefer the system to perform this task using its own tools and business logic.  Using CRM to do its own clean-up helps prevent accidental issues arising from the manual deletion of records using SQL tools, we only use the utilities provided by Microsoft in CRM. We are currently in the final phase of the development of an add-on tool to remove and manage these logs in an easy to use graphical interface. If you are interested, shoot us an email at This email address is being protected from spambots. You need JavaScript enabled to view it. .

To delete the entries from within CRM we need you to go to Settings >> Data Management >> Bulk Record Deletion.

log 4

Select the new option from the top of the menu, and create the bulk deletion job using the wizard, we suggest a criteria to use in the following screen shot.

log 5Bulk Deletion Job in CRM

  • Please remember to select the “System Jobs” as the look for value in the top dropdown.
  • The criteria can be updated to match your particular requirements, the Status Reasons used in this example is just a suggested starting point and works for most of our clients leaving any problems/errors untouched.
  • Once you have finished with the selection criteria, you have the option to set this deletion to automatically execute every X days.

In this screen you can provide a filter by dates for the deletion. We recommend to our clients to review their internal and legislative requirements for data retention, and then set the bulk deletion job to clear out the older log entries every month, so for example we would only keep the current month + 2 previous months logging online. If you have a requirement to hold your logging for longer, then we have put together a process by which we fire off a monthly task that copies the older log entries to a history file, allowing the logging tables to remain lean and mean but still meet the requirement outlined in data retention rules. ( if you are interested, shoot us an email at This email address is being protected from spambots. You need JavaScript enabled to view it.)

 

Once the Bulk Deletion Job is running, for those CRM On Premise users, you can monitor the progress of the deletion, by running the SQL query described above (SELECT count(*) FROM [AsyncOperationBase]) where you can see the number of records within the log reduce in real time as they are removed by CRM.

 Speed and Other Improvements

The CRM response time/speed improved noticeably regardless of CRM being accessed from a browser or Outlook CRM Plugin; Outlook stopped randomly disconnecting; functions that erratically failed stopped failing; workflows that were misbehaving started working as we expected; basically we ended up with a well behaved Dynamics CRM system.

log 6

A great example of how this impacted one client; they began to see performance degradation in CRM and the odd email would error when tracking to the opportunity. This progressed to the Outlook connector timing out tracking emails, which progressed further to a general slowdown of the overall CRM performance from the web interface, what made it even more of a puzzle was it appeared randomly and would stop happening after about 5-10 minutes.

This made it hard to diagnose the issue initially, and it got worse with the CRM Asynchronous Server Service stopping responding, it was still running but not responding to request from Outlook or the web interface. The temporary solution was to restart the service. Initially this happened every monthly, but after a few months it become a weekly occurrence, and was starting to get to the point of daily restarts.

After we cleaned up the logging (about 1.5 million records, which to SQL is nothing really) and set it to only hold 2 months’ worth of unimportant logs, the server performance went back to normal, it has remained that way for the past 3 months and hasn’t required any CRM server restarts (except for one Server Restart to apply Microsoft server patches) which made a very pleasant change.

Now this isn’t a panacea to everything, but from practical experience, it does seem to be an effective strategy when all else fails (although we can’t put an exact reason on it) where all the other logical, suggested and recommended fixes failed, a reasonably simple left field idea has come through.

And it may not work for all clients and customers, but hey, if you are pulling your hair out with stuff you just can’t get resolved and don’t understand, it could be worth a try. And if you get a benefit from doing this, put it in your monthly processes and strip out/delete all system logs regularly.

And please let us know at This email address is being protected from spambots. You need JavaScript enabled to view it. or by leaving a comment in this Blog if this has helped you.

 

One thought on “Log cleanup to solve erratic CRM problems

  1. Diego

    It sounds like your recovery partition is corrupted. If you can get to it through my computer then right click on the drive and then select tools and then select check disk for errors and tick both boxes and try that. If it don’t fix the problem then you will need a Windows XP disk to restore the computer.Good luck

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>