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.
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