Script to clean up SyncEntry_ and SubscriptionStatistics_ tables in the SQL database

SyncEntry_<GUID> and SubscriptionStatistics_<GUID> tables are created in the <CompanyName>_MSCRM SQL Database when using the CRM-outlook clients.  Generally you will have 2 of each table for each Online Client, and 3 of each table for each Offline Client.  However, these tables are created per user per machine, so in a citrix farm environment or if users have recently changed hardware, you may find that you have an extraordinary amount of these tables and would like to remove them from the database.

Below is a script that removes all SyncEntry_<GUID> and SubscriptionStatistics_<GUID> tables, along with corresponding records in other tables:

Declare @SyncEnt char(60),

@sql nchar(100),

@sqlSync nchar(100),

@DN char(50)

Declare User_cursor CURSOR for

select DomainName from SystemUserBase

OPEN User_cursor

FETCH NEXT FROM User_cursor INTO @DN

WHILE @@Fetch_Status = 0

BEGIN

DECLARE CRMSync_cursor CURSOR FOR

select substring(SyncEntryTableName,11,42) as SyncEntryGUID from subscription where systemuserid in

(select systemuserid from systemuserbase where domainname =@DN)

OPEN CRMSync_cursor

FETCH NEXT FROM CRMSync_cursor INTO @SyncEnt

WHILE @@Fetch_Status = 0

BEGIN

SELECT @sql = 'DROP TABLE SubscriptionStatistics_' +(@SyncEnt)

SELECT @sqlSync = 'DROP TABLE SyncEntry_' +(@SyncEnt)

EXEC sp_executesql @sql

EXEC sp_executesql @sqlSync

FETCH NEXT FROM CRMSync_cursor INTO @SyncEnt

END

CLOSE CRMSync_cursor

DEALLOCATE CRMSync_cursor

delete from subscriptionclients where subscriptionid in

(select subscriptionid from subscription where systemuserid in

(select systemuserid from systemuserbase where domainname = @DN))

delete from Subscriptionsyncinfo where subscriptionid in

(select subscriptionid from subscription where systemuserid in

(select systemuserid from systemuserbase where domainname = @DN))

-- Please Uncomment The 3 lines below if you are on UR7 or Higher

-- delete from SubscriptionManuallyTrackedObject where subscriptionid in
-- (select subscriptionid from subscription where systemuserid in
-- (select systemuserid from systemuserbase where domainname = @DN))

delete from subscription where systemuserid in

(select systemuserid from systemuserbase where domainname = @DN)

FETCH NEXT FROM User_cursor INTO @DN

END

CLOSE User_cursor

DEALLOCATE User_cursor

Best Regards,

Justin Thorp

CRM Senior Support Engineer