Script to clean up SyncEntry_<GUID> and SubscriptionStatistics_<GUID> 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 (9)

  1. check says:

    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?

  2. ukdynsupport says:

    Hi,

    I’ve tried importing an organization and I wasn’t able to reproduce this issue.

    I think that the only reason the script wouldn’t work is if you have a SystemUserId in the Subscription table that doesn’t exist in the SystemUserBase table.  You can identify these records by running the following script:

    select * from Subscription where SystemUserId not in (select SystemuserId from SystemUserBase)

    If you do have some records returned by this statement, you can run through the following steps to clean the remaining tables (it’s a slightly more manual process):

    1.)  Using SQL Management Studio, create "Drop Table" scripts for all SyncEntry_<guid> tables:

    A.)  Right click on your database and choose Tasks => Generate Scripts…

    B.)  Choose Next, and select your <CompanyName>_MSCRM database and choose next

    C.)  In the next window, change all options to false except "Script Drop" (this should be set to true).  Then choose next.

    D.)  Mark "Tables" as the object type, then hit next.

    E.)  Mark all SyncEntry_<guid> tables.  (It’ll probably be easier to choose "Select All", then unmark all tables except the SyncEntry tables).  Hit Next.

    F.)  Choose to script to a new query window, then hit finish.

    G.)  Once this is done, you should have a script that will drop all current existing SyncEntry tables.

    H.)  Execute this script against your <CompanyName>_MSCRM database

    2.)  Repeat Step 1, except when you get to part E, mark all SubscriptionStatistics_<guid> tables.

    3.)  Run the following SQL Script against the <CompanyName>_MSCRM database:

    delete SubscriptionSyncInfo

    GO

    delete SubscriptionClients

    GO

    delete Subscription

    GO

    Thank you,

    Justin

  3. Tony says:

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

  4. Greg Owens says:

    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)

  5. Steven says:

    Does this work for CRM 3.0 as well?

  6. Chad Rexin - Microsoft says:

    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.

  7. David says:

    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?

  8. Scott Jung - Hitachi says:

    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