Script to clean up SyncEntry_<GUID> and SubscriptionStatistics_<GUID> tables in the SQL database

Comments

# Script to clean up SyncEntry_ and SubscriptionStatistics_ tables in the SQL database | Microsoft Share Point said on May 7, 2009 6:22 AM:

PingBack from http://microsoft-sharepoint.simplynetdev.com/script-to-clean-up-syncentry_-and-subscriptionstatistics_-tables-in-the-sql-database/

# check said on June 2, 2009 6:32 AM:

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?

# ukdynsupport said on June 4, 2009 12:28 PM:

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