PrincipalObjectAccess table size issues.

Let’s say that you are a CRM Consultant or a SQL DBA and you have either been asked to investigate why CRM’s performance is extremely slow or you are just noticing that one of your CRM databases is growing quickly and you have no idea as to why, well then I might have the solution for you…

First what you need to do is to ensure that this is the case, so what you can do is run the below script to check the table sizes:

P.S. – Run this on the CRM database that has the performance issue.

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ‘dt%’
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceKB descS

You will then get this result, if your issue is the PrincipalObjectAccess table you should see that the size is bigger than usual and if this is the case then you can carry on with my solution, otherwise keep on trying but the above script might give you an indication of where the problem is:

Before we can get to clearing out the POA table, we first need to clear the ASYNC table by running the below script, this script will go through the ASYNC table to check and delete all Completed Workflow system jobs.

P.S. – You need to ensure that you have taken a full backup of your CRM database and also do not run this during business hours if this is a Production database.

IF EXISTS (SELECT name from sys.indexes
WHERE name = N’CRM_AsyncOperation_CleanupCompleted’)
DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO

CREATE NONCLUSTERED INDEX CRM_WorkflowLog_AsyncOperationID ON [dbo].[WorkflowLogBase] ([AsyncOperationID])
GO

CREATE NONCLUSTERED INDEX CRM_DuplicateRecord_AsyncOperationID ON [dbo].[DuplicateRecordBase] ([AsyncOperationID])
GO

CREATE NONCLUSTERED INDEX CRM_BulkDeleteOperation_AsyncOperationID ON [dbo].[BulkDeleteOperationBase]
(AsyncOperationID)
GO

CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
begin tran
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 @rowCount = 0
Select @rowCount = count(*) from @DeletedAsyncRowsTable
select @continue = case when @rowCount <= 0 then 0 else 1 end
if (@continue = 1) begin
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 WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where WS.AsyncOperationId = d.AsyncOperationID
delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where A.AsyncOperationId = d.AsyncOperationId
delete @DeletedAsyncRowsTable
end
commit
end

–Drop the Index on AsyncOperationBase
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

Now what you will need to do is to run the below script to check the total records in the POA table.

select COUNT(*) from PrincipalObjectAccess (nolock)
where ObjectTypeCode = 4706
and ObjectId not in (select WorkflowLogId from WorkflowLogBase (nolock))

Then if you confirm that this is in fact what you need to clear then you can execute the below script to clear the records:

P.S – This script can take a while depending on your record count, also ensure that if this is a Production database to make a full backup first and also try not to run this during business hours as it will cause even more performance issues.

USE [master]
GO
ALTER DATABASE [***YOU CRM DATABASE***] SET RECOVERY SIMPLE WITH NO_WAIT
GO

USE [***YOU CRM DATABASE***]
GO

SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON

DECLARE @I BIGINT;
SET @I = 0;

declare @stmt nvarchar(4000)
declare @batchsize BIGINT

select @batchsize = 50000
— Go through all user-owned entities which are not replicated and don’t support duplicate detection

DECLARE @Rows INT;
DECLARE @MaxLoops BIGINT;
SET @MaxLoops = 30000000;
WHILE @I < @MaxLoops — loop will exit once the deletes stop finding rows
BEGIN
SET @I = @I + 1;
–DECLARE entity_cursor cursor local FORWARD_ONLY READ_ONLY
–FOR

–SET @stmt = ‘set rowcount 10000 delete poa from PrincipalObjectAccess poa left join WorkflowLogBase b WITH(NOLOCK) on poa.ObjectId = b.WorkflowLogId where b.WorkflowLogId is null and poa.ObjectTypeCode = 4706’;

–exec sp_executesql @stmt

set rowcount @batchsize
delete poa from PrincipalObjectAccess poa WITH(NOLOCK)
left join WorkflowLogBase b WITH(NOLOCK) on poa.ObjectId = b.WorkflowLogId
where b.WorkflowLogId is null and poa.ObjectTypeCode = 4706;

SET @Rows = @@ROWCOUNT;

Print CAST((@I * (@batchsize / 1000)) AS NVARCHAR(50)) + ‘k ‘ + CAST(@Rows AS NVARCHAR(10));

if (@Rows <= 0)
SET @I = @MaxLoops;
END;
GO

USE [master]
GO
ALTER DATABASE [***YOU CRM DATABASE***] SET RECOVERY FULL WITH NO_WAIT
GO

When the script ran successfully you can run the script we ran before this to check the total records, this should now be 0.

You should now notice that the database size might still be a problem, that is because when we deleted these records it didn’t delete everything and it left orphaned records behind so what we now need to do is to run the below script to see if that is in case the problem.

–Script to determine if you have orphaned records and how many:

SELECT
NumOrphans,
100.0 * NumOrphans / TotalRecordCount as PercentOrphaned,
TotalRecordCount – NumOrphans as NotOrphans,
TotalRecordCount
FROM (
SELECT
SUM(CASE WHEN b.WorkflowLogId is null and poa.ObjectTypeCode = 4706 THEN 1 ELSE 0 END) as NumOrphans,
COUNT(*) as TotalRecordCount
from PrincipalObjectAccess poa WITH(NOLOCK)
left join WorkflowLogBase b WITH(NOLOCK) on poa.ObjectId = b.WorkflowLogId
) AS X

It should give you a good indication of the amount of orphaned records in your database, if there are orphaned records then you need to run the below script to clear them.

USE [master]
GO
ALTER DATABASE [Your CRM Database] SET RECOVERY SIMPLE WITH NO_WAIT
GO

USE [Your CRM Database]
GO

SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON

DECLARE @I BIGINT;
SET @I = 0;

declare @stmt nvarchar(4000)
declare @batchsize BIGINT

select @batchsize = 50000
— Go through all user-owned entities which are not replicated and don’t support duplicate detection

DECLARE @Rows INT;
DECLARE @MaxLoops BIGINT;
SET @MaxLoops = 30000000;
WHILE @I < @MaxLoops — loop will exit once the deletes stop finding rows
BEGIN
SET @I = @I + 1;
–DECLARE entity_cursor cursor local FORWARD_ONLY READ_ONLY
–FOR

–SET @stmt = ‘set rowcount 10000 delete poa from PrincipalObjectAccess poa left join WorkflowLogBase b WITH(NOLOCK) on poa.ObjectId = b.WorkflowLogId where b.WorkflowLogId is null and poa.ObjectTypeCode = 4706’;

–exec sp_executesql @stmt

set rowcount @batchsize
delete poa from PrincipalObjectAccess poa WITH(NOLOCK)
left join WorkflowLogBase b WITH(NOLOCK) on poa.ObjectId = b.WorkflowLogId
where b.WorkflowLogId is null and poa.ObjectTypeCode = 4706;

SET @Rows = @@ROWCOUNT;

Print CAST((@I * (@batchsize / 1000)) AS NVARCHAR(50)) + ‘k ‘ + CAST(@Rows AS NVARCHAR(10));

if (@Rows <= 0)
SET @I = @MaxLoops;
END;
GO

USE [master]
GO
ALTER DATABASE [Your CRM Database] SET RECOVERY FULL WITH NO_WAIT
GO

So herewith a summary of what we have done:

Analize:

POA table grew to a ridiculous size.

Action plan:

Our recommendation in this case will be to.

• Clear workflow log history: “Unless you have a valid business reason for keeping the workflow history, you might consider just turning it off.
• This can be done from the admin tab of each workflow (checkbox at the bottom). Then in the More Actions menu of the workflow history, you can choose Bulk Delete and remove the complete history.”
• After this we ran the AsyncOperationBase cleanup script. – http://support.microsoft.com/kb/968520
• Then we ran the script to clear the POA table and the Orphaned records after that.

So when checking now, your database should have come down quite a bit but again if this was your issue.

Hope you came right, if not then I hope you find it.