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

Comments

  • Anonymous
    May 06, 2009
    PingBack from http://microsoft-sharepoint.simplynetdev.com/script-to-clean-up-syncentry_-and-subscriptionstatistics_-tables-in-the-sql-database/

  • Anonymous
    June 01, 2009
    Hello Justin, This is a very usefull query you've developed here and works very well indeed!  However, I've noticed that it doesn't remove sync tables in a imported organisation, where the tables were created prior to the import.  I assume that this is because there's no domain history in the CRM database?

  • Anonymous
    June 04, 2009
    The comment has been removed

  • Anonymous
    June 24, 2010
    Thanks for this, is there any risk in deleting the tables for active users? Will running this mess up user sync's going forward?

  • Anonymous
    August 23, 2010
    Hi Tony - for the record, this CAN break user records for active users - I followed the additional steps on in the comments (ukdynsupport 4 Jun 2009 12:28 PM) on my development system and found that simply accessing CRM Options in Outlook caused an error. This was easily overcome by re-mapping my users (go to user record, change domain logon name to another record, save, then change it back to original logon name and re-save). This would be a bit disasterous on a live system though! To be clear: the additional steps should only be carried out on records that are returned by the query: select * from Subscription where SystemUserId not in (select SystemuserId from SystemUserBase)

  • Anonymous
    August 25, 2010
    Does this work for CRM 3.0 as well?

  • Anonymous
    December 21, 2010
    Note that if you wanted to be more selective and only delete syncentry and subscription tables for ones that haven't been synced in the last 90 or more days, you could change the following lines in the script at this site. DECLARE CRMSync_cursor CURSOR FOR select substring(SyncEntryTableName,11,42) as SyncEntryGUID from subscription where systemuserid in      (select systemuserid from systemuserbase where domainname =@DN) To DECLARE CRMSync_cursor CURSOR FOR select substring(SyncEntryTableName,11,42) as SyncEntryGUID from subscription where systemuserid in      (select systemuserid from systemuserbase where domainname =@DN) AND (LastSyncStartedOn < GetDate()-90 or LastSyncStartedOn is NULL) As with any SQL scripts like this, I'd strongly recommend making a backup first before running them and preferably running them on a Dev or Test environment first before implementing in production.

  • Anonymous
    September 01, 2011
    Dittoing a prior question I'd like considered prior to my doing an upgrade to 4.0. Does this work for CRM 3.0 as well?

  • Anonymous
    August 25, 2014
    Be sure if you choose to qualify your initial query that you qualify the other 3-4 at the end with a similar (LastSyncStartedOn < GetDate()-90 or LastSyncStartedOn is NULL) AND