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:
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.
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.
.png)
