The Scenario
It was Microsoft Dynamics CRM 2011 and the partner was reporting their database was rapidly increasing in size. On a good day, it would usually sit around 30 GB steadily growing as you would expect……until a few months ago.
The database was first brought to our attention when it reached 80 GB and it continued to climb well past the 100gb mark. In fact, at one point the problem was so severe, CRM was nearly shut down just to save everything else.
Auditing
They have had problems with auditing previously so it seemed the obvious place to start looking. Auditing is great if set up well but if not, free space can quickly disappear.
The article Managing Audit Logs in Microsoft Dynamics CRM explains auditing in more detail but cut a long story short, auditing was enabled at the entity level including all fields.
However, this was not the problem. Even though auditing was enabled for many fields and entities, there was a process in place to clear out audit records older than a certain age. This was confirmed when we checked the size of the AuditBase table.
So where was the space being used?
Disk Usage by Table
In SQL Server Management Studio, the report is Disk Usage by Table lists the size and row count of each table. From the report, the clear winner by a huge amount was the AsyncOperationBase table. This table is used to store details of system jobs including workflows and is well known for the amount of space it uses.
AsyncOperationBase table part 1
Its well known the AsyncOperationBase table can increase in size and is well documented. It can be particularly troublesome if an organisation has many custom workflows running.
Our partner does have custom workflows so it makes sense the table would be large. Microsoft have published KB968520 which contains a SQL script to clean up the AsyncOperationBase table. We ran the script. The database returned to a sensible size and everyone was happy. It had worked!!! ……but not for long.
AsyncOperationBase table part 2
It wasn’t long before they were banging on the door again with an extremely large database. What could it be? We knew which table was causing the problem but couldn’t find out why. It was time to interrogate the AsyncOperationBase table a little further.
We did two things. First, we checked for common entries. This produced some interesting results. Over half of the rows were generated from a particular plugin provided by another ISV. It was originally installed to test a particular feature but was never fully implemented or removed. The plugins were deactivated and the clean-up script was run. The still database continued to grow at an alarming rate. Although it was still a problem, the plugin was not the root cause.
The second thing we checked was the size of individual rows. Identifying these rows proved challenging. We used LEN() on specific columns and were able to identify a few thousand suspect rows. However, this was another dead-end.
SQL Server 2008 R2 Service Pack 3
To try and catch the problem in real-time, we monitored a 24 hour period with some interesting results. During this period, the table increased by 6 GB, however, there were only an additional 171 rows. This didn’t make any sense. Maybe the problem isn’t related to CRM related at all. Perhaps something more sinister is going on. Perhaps the problem is with the hard disk or with SQL server its self. Time to bring in Microsoft.
The first thing Microsoft checked was the service pack level. We were using the RTM version of SQL Server 2008 R2. After installing the latest service pack, we saw a huge improvement. Was this problem all along? As it happens, there are known issues with SQL 2008 R2 RTM and how it releases unused space. Finally, success.
Conclusion
The journey was long and was compounded by the threat of their systems grinding to a halt. The partner was losing faith in the product and we were struggling to make sense of what was in front of us. But after lots of hard work and persistence, we finally got there. What did we learn from the experience?
We learnt
- Auditing can be a great tool if set up and managed correctly.
- Remove any old solutions or customisations if they are no longer required. They may still be active in the background.
- Keep software up to date and beware of any known issues with the versions you have.
- Don’t be afraid to get Microsoft involved. They are there to help.
For more information, check our website or email info@qgate.co.uk
