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