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