-- ??
--Delete from SubscriptionTrackingDeletedObject where TimeStamp <= convert(timestamp, 55527527);
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
