• Email
• Print

SYMPTOMS

CAUSE

RESOLUTION

SQL Script To Execute

--Replace the text "Replace With DatabaseName" with your database name

--For Example

--USE [AdventureWorksCycle_MSCRM]

USE [<Replace With DatabaseName>]

GO

BEGIN TRY

BEGIN TRAN t1 

IF NOT EXISTS (SELECT * FROM sys.sysobjects

WHERE 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 si

INNER JOIN sys.sysobjects so ON si.id = so.id

WHERE so.id = OBJECT_ID(N'[dbo].[ToDeletePoaEntries]')

AND OBJECTPROPERTY(so.id, N'IsUserTable') = 1

AND 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 detection

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 @currentCollected int

declare @tempRowCount int = 0

declare @collectstatement nvarchar(max)

fetch next from entity_cursor

into @otc, @baseTableName, @primaryKey

while @@FETCH_STATUS = 0

begin

print 'Cleaning up POA for ' + @baseTableName

set @currentCollected = 0

set @collectstatement = '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 @collectstatement

exec(@collectstatement)

set @tempRowCount = @@ROWCOUNT

set @currentCollected = @currentCollected + @tempRowCount

print CAST(@currentCollected as nvarchar(20)) + ' records collected for ' + @baseTableName

set @totalCollected = @totalCollected + @currentCollected

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

-- This scripts cleans up orphaned POA records for selected entities

declare @deleteBatchSize int = 50000

declare @deleteBatchSizeNVarChar nvarchar(10) = CAST(@deleteBatchSize as nvarchar(10))

declare @totalDeleted int = 0

declare @currentDeleted int

declare @deletestatement nvarchar(max)

set @currentDeleted = 0

set @tempRowCount = 0

-- delete all records of the current entity type which don't have corresponding object in the base table

set @deletestatement = 'delete top (' + @deleteBatchSizeNVarChar + ') from PrincipalObjectAccess

from PrincipalObjectAccess poa

join ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc'

print @deletestatement

-- delete PrincipalObjectAccess records in batches

exec(@deletestatement)

set @tempRowCount = @@ROWCOUNT

set @currentDeleted = @currentDeleted + @tempRowCount

while @tempRowCount = @deleteBatchSize

begin

exec(@deletestatement)

set @tempRowCount = @@ROWCOUNT

set @currentDeleted = @currentDeleted + @tempRowCount

print CAST(@currentDeleted as nvarchar(20)) + ' records deleted ' + cast(getUtcdate() as nvarchar(50))

--cleanup

end

COMMIT TRAN t1

-- Cleanup

DROP Table [dbo].[ToDeletePoaEntries]

PRINT 'EXECUTION SUCCEED'

END TRY

BEGIN CATCH

ROLLBACK TRAN t1

-- Cleanup

DROP Table [dbo].[ToDeletePoaEntries]

PRINT 'EXECUTION FAILED :' + ERROR_MESSAGE()

END CATCH

MORE INFORMATION

Article ID: 2664150 - Last Review: 06/27/2012 12:54:00 - Revision: 6.0