Controlling Principal Object Access Growth in CRM 2011

After using Microsoft Dynamics CRM 2011 you may notice that the size of the SQL table PrincipalObjectAccess has grown in size and could be using a large percentage of entire database size.

When records are deleted in Microsoft Dynamics CRM the related PrincipalObjectAccess records where not removed.

Apply Update Rollup 6 and then execute the script documented in this KB article. This script needs to only be executed 1 time after applying Update Rollup 6. Update Rollup 6 and future Update Rollups will fix this known issue going forward.

SQL Script To Execute

/************************************************************************************************************
/************************************************************************************************************
NOTE: This script should be only used in smaller set of data that will take less than couple of min to execute.
  For larger dataset, we should run this query in batches.
************************************************************************************************************/
************************************************************************************************************/
-- Use the db name of the production org below, should look similar to: USE [Adventure_Works_Cycle_MSCRM]
USE [<replace_with_mscrm_database_name>]
GO


BEGIN TRY
 BEGIN TRAN t1 
 

  create table #ToDeletePoaEntries
  (
   ObjectId uniqueidentifier,
   Otc int
  )

  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]

  declare entity_cursor cursor local FORWARD_ONLY READ_ONLY
  for select distinct e.ObjectTypeCode, e.BaseTableName, a.PhysicalName from EntityView e
  inner join AttributeView a on e.EntityId = a.EntityId and a.IsPKAttribute = 1
  where e.IsReplicated = 0 and e.IsDuplicateCheckSupported = 0 and e.OwnershipTypeMask & 1 = 1

  open entity_cursor

  declare @baseTableName sysname
  declare @otc nvarchar(20)
  declare @primaryKey sysname
  declare @totalCollected int = 0
  declare @totalDeleted int = 0

  fetch next from entity_cursor
  into @otc, @baseTableName, @primaryKey

  while @@FETCH_STATUS = 0
  begin
   print 'Cleaning up POA for ' + @baseTableName


   declare @deletestatement nvarchar(max)

   -- Insert records to be deleted in [#ToDeletePoaEntries]
   set @deletestatement = 'insert into #ToDeletePoaEntries(ObjectId, Otc)
   select distinct poa.ObjectId, poa.ObjectTypeCode
   from PrincipalObjectAccess poa
   left join ' + @baseTableName + ' e on poa.ObjectId = e.' + @primaryKey +
   ' where e.' + @primaryKey + ' is null and poa.ObjectTypeCode = ' + @otc;
   
   print @deletestatement

   exec(@deletestatement)
   set @totalCollected = @@ROWCOUNT
   print CAST(@totalCollected as nvarchar(20)) + ' records collected for deletion for ' + @baseTableName

   fetch next from entity_cursor
   into @otc, @baseTableName, @primaryKey
  end

  close entity_cursor
  deallocate entity_cursor

  print CAST(@totalCollected as nvarchar(20)) + ' total records collected'

  -- Delete query


  -- delete all records of the current entity type which don't have corresponding object in the base table
  delete from PrincipalObjectAccess
  from PrincipalObjectAccess poa
  join #ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc

  -- delete PrincipalObjectAccess records

  set @totalDeleted = @@ROWCOUNT
  print CAST(@totalDeleted as nvarchar(20)) + ' records deleted for ' + @baseTableName

 COMMIT TRAN t1
 PRINT 'EXECUTION SUCCEED'
END TRY
BEGIN CATCH
 ROLLBACK TRAN t1
 PRINT 'EXECUTION FAILED :' + ERROR_MESSAGE()
END CATCH

 

This script only needs to be run once after installing Update Rollup 6 or a future Update Rollup. Once the script is run to clean up the PrincipalObjectAccess table it does not need to be run after any future Update Rollup installs.

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use (http://go.microsoft.com/fwlink/?LinkId=151500) for other considerations.