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

re: Script to clean up SyncEntry_ and SubscriptionStatistics_ tables in the SQL database

David — Fri, 02 Sep 2011 11:59:04 GMT

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?

re: Script to clean up SyncEntry_ and SubscriptionStatistics_ tables in the SQL database

Chad Rexin - Microsoft — Wed, 22 Dec 2010 15:35:30 GMT

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)

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.

re: Script to clean up SyncEntry_ and SubscriptionStatistics_ tables in the SQL database

Steven — Thu, 26 Aug 2010 12:54:02 GMT

Does this work for CRM 3.0 as well?

re: Script to clean up SyncEntry_ and SubscriptionStatistics_ tables in the SQL database

Greg Owens — Tue, 24 Aug 2010 14:12:03 GMT

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)

re: Script to clean up SyncEntry_ and SubscriptionStatistics_ tables in the SQL database

Tony — Thu, 24 Jun 2010 16:18:25 GMT

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

re: Script to clean up SyncEntry_ and SubscriptionStatistics_ tables in the SQL database

ukdynsupport — Thu, 04 Jun 2009 19:28:13 GMT

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

delete SubscriptionClients

delete Subscription

Thank you,

Justin

re: Script to clean up SyncEntry_ and SubscriptionStatistics_ tables in the SQL database

check — Tue, 02 Jun 2009 13:32:49 GMT

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?

Script to clean up SyncEntry_ and SubscriptionStatistics_ tables in the SQL database | Microsoft Share Point

Thu, 07 May 2009 13:22:33 GMT

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