How to reduce the size of the CRM Database

There are several tables within each CRM Organization database that can become very large. The size of the CRM database shouldn't be an issue within your production environment, as adequate space should be provisioned for this growth. 

These are as follows:

• AsyncOperationBase
• AuditBase
• WorkflowWaitSubscriptionBase
• PrincipleObjectBase

The problems typically occur in QA, UAT or DEV environments where you need to restore a prod like copy in order to work against real life data. Disk space can be a premium in these environments, thus having the ability to trim down the CRM database can be very useful.

You should leave the PrincipleObjectBase table alone as this deals with many aspects of record access, sharing etc.

Steps to Reduce your CRM Database size:

1. Execute the following SQL statement to remove redundant records from the AsyncOperationBase table:

IF EXISTS (SELECT name from sys.indexes
WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

while(1=1)
begin
declare @DeleteRowCount int = 10000
declare @rowsAffected int
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
where 
  OperationType in (1, 9, 12, 25, 27, 10) 
  AND StateCode = 3 
  AND StatusCode in (30, 32)

 select @rowsAffected = @@rowcount 
 delete poa from PrincipalObjectAccess poa 
   join WorkflowLogBase wlb on
    poa.ObjectId = wlb.WorkflowLogId
   join @DeletedAsyncRowsTable dart on
    wlb.AsyncOperationId = dart.AsyncOperationId
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 BulkDeleteOperationBase From BulkDeleteOperationBase O, @DeletedAsyncRowsTable d
where 
  O.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where 
  WS.AsyncOperationId = d.AsyncOperationID 
 delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where 
  A.AsyncOperationId = d.AsyncOperationId
/*If not calling from a SQL job, use the WAITFOR DELAY*/
if(@DeleteRowCount > @rowsAffected)
  return
else
  WAITFOR DELAY '00:00:02.000'

end

2. Execute the following SQL statement to remove redundant records from the WorkflowWaitSubscriptionBase table:

Delete from workflowwaitsubscriptionbase 
where asyncoperationid in(Select asyncoperationidfrom AsyncOperationBase
where OperationType in (1, 9, 12, 25, 27, 10) 

AND StateCode = 3 AND StatusCode IN (30,32))

3. Delete the Audit Log partitions from within the CRM User Interface. 

Go to Settings>Auditing and then click on Audit Log Management

Select and delete each partition, the larger ones are indicated by the number of rows.

3. Finally you need to Shrink the CRM Organization SQL Database itself from within SQL Management Studio. This will recover all available space within the database file and reduce it's footprint on disk.

The summary screen will indicate how much available space will be recovered, by which the database file size also being reduced by the same amount. Then click OK and wait for the process to finish.