Cleaning up CRM Sync Entry tables

UPDATE: DYNAMICS CRM 2011 UPDATE ROLLUP 15+ AUTOMATICALLY CLEANS UP SUBSCRIPTIONS OLDER THAN 90 DAYS – if you are running on CRM 2011 Update Rollup 15 or higher you DO NOT need to use this script.

If you’re CRM system has been up and running for a long period of time and your users have the CRM Outlook client installed (or have had it installed in the past) you’ll find many tables starting in SyncEntry_ as well as SubscriptionStatistics_ in your database. These tables are used to track items that are synchronized down to users client machines, however, when users get new client machines or leave the company the tables are left behind indefinitely.  This script has become a cleanup step we run with our customers prior to upgrading or periodically to prune out old sync data.  

Important NOTES about this script:

  • Always make a backup before running this script against your DB. 
  • Running scripts that modify your database are not supported, thus we’ve gone to great lengths to test this and make sure that all entries are cleaned out.  As of today this script can successfully run on:
    • CRM 4.0 UR7 and greater
    • CRM 2011 RTM through CRM 2011 Update Rollup 8
  • Do not modify any parameters or joins in this script or you could risk data damage
  • The script currently deletes all sync data older than 90 days
  • If a users sync entry data were to be deleted, the next time the user attempts to sync their client, the data would be rebuilt automatically
  • To test the script you can edit the “SET @execute = 1” and set it to a “0”, this will allow the script to run in a read-only mode and print out the SQL statements it would run to delete the tables.
      @SyncEnt    varchar(60),
      @syncId     uniqueidentifier,
      @SQL        nvarchar(MAX), 
      @execute bit

--To run the deletions set this to 1, if it is 0 it will only print the statements
SET @execute = 1 


SELECT Replace(SyncEntryTableName,'SyncEntry_','') as SyncEntryGUID, SubscriptionId from subscription 
where LastSyncStartedOn < GetDate()-90 or LastSyncStartedOn is NULL

OPEN CRMSync_cursor
FETCH NEXT FROM CRMSync_cursor INTO @SyncEnt, @syncId
WHILE @@Fetch_Status = 0
      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('subscriptionStatistics_'+@SyncEnt) AND type in (N'U'))
            SET @SQL = 'DROP TABLE SubscriptionStatistics_' +(@SyncEnt)
            IF @execute=1 EXEC sp_executesql @SQL
            IF @execute=0 PRINT @SQL
            IF @execute=1 PRINT 'Dropped table: SubscriptionStatistics_'+(@SyncEnt)+ ' with error: ' + CAST(@@ERROR as varchar(255))
            IF @execute=1 PRINT 'SubscriptionStatistics table does not exist for subscriptionID: ' + CAST(@syncId as varchar(50))
      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('SyncEntry_'+@SyncEnt) AND type in (N'U'))
            SET @SQL = 'DROP TABLE SyncEntry_' +(@SyncEnt)
            IF @execute=1 EXEC sp_executesql @SQL
            IF @execute=0 PRINT @SQL
            IF @execute=1 PRINT 'Dropped table: SyncEntry_'+(@SyncEnt) + ' with error: ' + CAST(@@ERROR as varchar(255))
            IF @execute=1 PRINT 'SyncEntry table does not exist for subscriptionID: ' + CAST(@syncId as varchar(50))
      SET @SQL = 
            'delete from SubscriptionManuallyTrackedObject where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''';' +
            'delete from subscriptionclients where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''';' +
            'delete from Subscriptionsyncinfo where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''';' +
            'delete from subscription where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''''
      IF @execute=1 EXEC sp_executesql @SQL
      IF @execute=0 PRINT @SQL
      IF @execute=1 PRINT 'Dropped subscription table data for subscriptionId: ' + CAST(@syncId as varchar(50)) + ' with error data: ' + CAST(@@ERROR as varchar(255))
      FETCH NEXT FROM CRMSync_cursor INTO @SyncEnt, @syncId
CLOSE CRMSync_cursor

When the script runs you’ll be left with output such as the following showing you what was deleted and if there were any errors:

Dropped table: SubscriptionStatistics_6e64fbf450a1de11932b00155d88bd02 with error: 0
Dropped table: SyncEntry_6e64fbf450a1de11932b00155d88bd02 with error: 0

(0 row(s) affected)

(0 row(s) affected)

(766 row(s) affected)

(1 row(s) affected)
Dropped subscription table data for subscriptionId: 6E64FBF4-50A1-DE11-932B-00155D88BD02 with error data: 0
Comments (10)

  1. DotNetMatt says:

    Hi Sean,

    Thanks for posting this script. However, I am wondering i executing this script would break (or not) any guarantee provided by Microsoft on the CRM product?

  2. Thanks for the comment @DotNetMatt!  Deleting from DB in many cases will cause concerns for supportability, in the case of sync tables this data is used to track users subscriptions to various data within CRM and is recreated by CRM if deleted – however, you'll only want to truly delete a users subscription if they're not longer using a given subscription (take for instance the user has left the company or they re-installed windows or are using a new PC).  The primary intent for the script is to be run before upgrades or to be run in cases where you have many old subscriptions that need cleanup.  In a worst case scenario situation where deleting a sync table did break a sync – the users subscription will be recreated by the outlook client.  We certainly do not recommend changing the script or applying this same logic to any other tables.  

  3. J says:

    Hi Sean,

    Do we need to reconfigure CRM for Outlook client after running this script or only sync with CRM if we need old info to be visible in CRM for Outlook?



  4. No, you do not need to reconfigure your outlook client. This is very similar to the script implemented after update rollup 15 (for 2011). In fact, I have a note to update this script to use the same conditions as the cleanup script in CRM today.  Thanks for reading!

  5. Todd says:

    Is this script still relevant for CRM 2013 if we have an excessive number of these tables? How does this impact performance?

  6. Hi Todd,

    A very similar script runs automatically now as part of CRM 2011 UR15 and higher including CRM 2013, 2015, etc.  Any subscription that's older than 90 days (by default) will be deleted by the async service.  These tables should start to drop off as they age – if they're not being used.  Also, if you have a lot of outlook clients you'll have a lot of these tables – but they'll be getting used.  

    As far as perf, this isn't much of a concern since the reference table itself is indexed and can handle many subscriptions over time.  Where the auto delete job will help you the most is if you do a mass replacement of hardware or if a large number of users are no longer syncing their data those tables will get cleaned up (automatically now) and your storage space will be returned in SQL.

    Bottom line – if you're 2011 UR15 or higher in versions – you don't need to run the script.



  7. Todd says:

    Hi Sean,

    Thanks for the quick reply.  Is there a supported way perhaps through the Org. DB Settings tool you development to adjust the 90 days if we wanted this clean up time to be more frequent?



    1. @Todd – Yes you can change the subscription expiration, the organization entity attribute that controls this is: ExpireSubscriptionsInDays which defaults to 90 days. If you are using CRM 2013 or higher you can use the orgdborgsettings tool ( If you’re using CRM 2011 Update rollup 15 or higher you can update the organization.ExpireSubscriptionsInDays via the API.

  8. Marc says:

    Hi Sean,
    if I query our CRM 2011 system (RU18) I can see a lot of subscriptions that have not been deleted by the system automatically. Where can I check this deleting “job”.

    Best regards,

    1. Hey Marc,
      The deletion service should be running and deleting this info – you’re welcome to run the script in addition to see if it catches subscriptions that weren’t deleted for some reason. You can also run a sql trace to monitor the deletion job to see why it’s not deleting the records. If that’s not working make sure to apply the latest rollup, if not you could open a support a case to ask for some assistance.

Skip to main content