There is a lot of information online about the MS CRM performance issues and very large database file sizes. Without going into MS CRM (you can Google that) it is by design. Can you believe that!
CRM is set to record everything that is going on, and for most people, most of the transaction logging in the AsyncOperationBase table is just junk. Now you would expect that there is a little in application sort of… clean up your junk… feature in CRM, but alas we are dreaming of some Apple product. For this reason, the database has to be manually cleared, or an auto process set to do this manually.
So the situation before me now is a massive database of 20GB with almost 17 million transactions listed.
This server I am looking at is Windows 2008, and the SQL server is also Windows 2008.
These are the steps to fix this problem in this case.
1. Log in to SQL Management Studio with an administrator account that has full access to the database in question.
2. The database will loos like ORGNAME_MSCRM and you right click and select New Query
3. Copy and paste the script below into the SQL Query window and select the Execute Button (that does not look like a button till you hover)
Select Count(*) from AsyncOperationBase
You will end up with a query result that show the number of records that will need to be addressed, this will give you a good indication of the time it will take. 17 million = a long time!
Because of this big number, I am going to take some steps to improve the performance that this query will run.
4. Backup the database to ensure you have a safe starting point.
5. Shrink the Database by right clicking and selecting tasks, shrink, database:
Then OK on the default settings.
6 Then improve performance by creating some indexes for the cleanup query. Paste this code over the Query Code in step 3, and execute.
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
CREATE NONCLUSTERED INDEX CRM_WorkflowLog_AsyncOperationID ON [dbo].[WorkflowLogBase] ([AsyncOperationID])
CREATE NONCLUSTERED INDEX CRM_DuplicateRecord_AsyncOperationID ON [dbo].[DuplicateRecordBase] ([AsyncOperationID])
CREATE NONCLUSTERED INDEX CRM_BulkDeleteOperation_AsyncOperationID ON [dbo].[BulkDeleteOperationBase]
This little baby may spin for some time:
7. Stop the Microsoft CRM Asynchronous Processing Service in the Server Services, found under the Administrator tools / Services
8. Set Simple Database Recovery. Right click the database and select Properties / Options / Recovery Model to simple
9. Run this command in the SQL Query, like in step 3
-- Rebuild Indexes & Update Statistics on AsyncOperationBase Table
ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
-- Rebuild Indexes & Update Statistics on WorkflowLogBase Table
ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
You will probably see your database almost double in size with this one.
10. Then finally run the command that will clean up the database
declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId
where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)
Select @rowCount = 0
Select @rowCount = count(*) from @DeletedAsyncRowsTable
select @continue = case when @rowCount <= 0 then 0 else 1 end
if (@continue = 1)
delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
where W.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
where B.AsyncOperationId = d.AsyncOperationId
delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where A.AsyncOperationId = d.AsyncOperationId
Executing just as we did in step 3.
11. Reset the database to Full Recovery Mode, reversing the step we took in step 8.
12. Shrink the database again exactly as we did in step 5.
13. Start the Service again, reversing step 7.
And you should be good to go. More information on this and DotNetNuke Modules.