Symptoms
After you use Microsoft Dynamics CRM 2011, you may notice that the size of the SQL table PrincipalObjectAccess grows and could be using a large percentage of the database size.
Cause
When records are deleted in Microsoft Dynamics CRM, the related PrincipalObjectAccess records are not removed.
Resolution
Apply Update Rollup 6, and then execute the script that is documented here. This script must be executed only one time after you apply Update Rollup 6. Update Rollup 6 and future Update Rollups will fix this known issue.SQL Script To Execute--Replace the text "Replace With DatabaseName" with your database name--For Example--USE [AdventureWorksCycle_MSCRM]USE [<Replace With DatabaseName>]GOBEGIN TRYBEGIN TRAN t1 IF NOT EXISTS (SELECT * FROM sys.sysobjectsWHERE id = object_id(N'[dbo].[ToDeletePOAEntries]')AND ObjectProperty(id, N'IsUserTable') = 1)create table ToDeletePoaEntries(ObjectId uniqueidentifier,Otc int)IF NOT EXISTS (SELECT * FROM sys.sysindexes siINNER JOIN sys.sysobjects so ON si.id = so.idWHERE so.id = OBJECT_ID(N'[dbo].[ToDeletePoaEntries]')AND OBJECTPROPERTY(so.id, N'IsUserTable') = 1AND si.name LIKE '%mainindex%')CREATE UNIQUE NONCLUSTERED INDEX [mainindex] ON [dbo].[ToDeletePoaEntries] ([ObjectId] ASC,[Otc] ASC)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]----- Insert records to be deleted in ToDeletePoaEntries-- go through all user-owned entities which are not replicated and don't support duplicate detectiondeclare entity_cursor cursor local FORWARD_ONLY READ_ONLYfor select distinct e.ObjectTypeCode, e.BaseTableName, a.PhysicalName from EntityView einner join AttributeView a on e.EntityId = a.EntityId and a.IsPKAttribute = 1where e.IsReplicated = 0 and e.IsDuplicateCheckSupported = 0 and e.OwnershipTypeMask & 1 = 1open entity_cursordeclare @baseTableName sysnamedeclare @otc nvarchar(20)declare @primaryKey sysnamedeclare @totalCollected int = 0declare @currentCollected intdeclare @tempRowCount int = 0declare @collectstatement nvarchar(max)fetch next from entity_cursorinto @otc, @baseTableName, @primaryKeywhile @@FETCH_STATUS = 0beginprint 'Cleaning up POA for ' + @baseTableNameset @currentCollected = 0set @collectstatement = 'insert into ToDeletePoaEntries(ObjectId, Otc) select distinct poa.ObjectId, poa.ObjectTypeCode from PrincipalObjectAccess poaleft join ' + @baseTableName + ' e on poa.ObjectId = e.' + @primaryKey + ' where e.' + @primaryKey + ' is null and poa.ObjectTypeCode = ' + @otc;print @collectstatementexec(@collectstatement)set @tempRowCount = @@ROWCOUNTset @currentCollected = @currentCollected + @tempRowCountprint CAST(@currentCollected as nvarchar(20)) + ' records collected for ' + @baseTableNameset @totalCollected = @totalCollected + @currentCollectedfetch next from entity_cursorinto @otc, @baseTableName, @primaryKeyendclose entity_cursordeallocate entity_cursorprint CAST(@totalCollected as nvarchar(20)) + ' total records collected'-- Delete query-- This scripts cleans up orphaned POA records for selected entitiesdeclare @deleteBatchSize int = 50000declare @deleteBatchSizeNVarChar nvarchar(10) = CAST(@deleteBatchSize as nvarchar(10))declare @totalDeleted int = 0declare @currentDeleted intdeclare @deletestatement nvarchar(max)set @currentDeleted = 0set @tempRowCount = 0-- delete all records of the current entity type which don't have corresponding object in the base tableset @deletestatement = 'delete top (' + @deleteBatchSizeNVarChar + ') from PrincipalObjectAccessfrom PrincipalObjectAccess poajoin ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc'print @deletestatement-- delete PrincipalObjectAccess records in batchesexec(@deletestatement)set @tempRowCount = @@ROWCOUNTset @currentDeleted = @currentDeleted + @tempRowCountwhile @tempRowCount = @deleteBatchSizebegin exec(@deletestatement) set @tempRowCount = @@ROWCOUNT set @currentDeleted = @currentDeleted + @tempRowCountprint CAST(@currentDeleted as nvarchar(20)) + ' records deleted ' + cast(getUtcdate() as nvarchar(50))--cleanupendCOMMIT TRAN t1-- CleanupDROP Table [dbo].[ToDeletePoaEntries]PRINT 'EXECUTION SUCCEED'END TRYBEGIN CATCHROLLBACK TRAN t1-- CleanupDROP Table [dbo].[ToDeletePoaEntries]PRINT 'EXECUTION FAILED :' + ERROR_MESSAGE()END CATCH
More Information
This script only needs to be run one time after Update Rollup 6 or a future Update Rollup is installed. After the script is run to clean up the PrincipalObjectAccess table, the script does not need to be run after any future Update Rollup installations.
