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.
A fix to reduce POA table growth was released in CRM 2011 Update Rollup 6: http://support.microsoft.com/kb/2664150.
Apply Update Rollup 6 and then execute the script documented in the 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.
NOTE: If you install any Update Rollup after Update Rollup 6, without first installing Update Rollup 6, and you have not yet ran the script documented in the resolution steps you will need to execute this script after applying that Update Rollup.
USE []
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
A fix to reduce POA table growth was released in CRM 2011 Update Rollup 6: http://support.microsoft.com/kb/2664150.
Apply Update Rollup 6 and then execute the script documented in the 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.
NOTE: If you install any Update Rollup after Update Rollup 6, without first installing Update Rollup 6, and you have not yet ran the script documented in the resolution steps you will need to execute this script after applying that Update Rollup.
USE [
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
