DSP: Clearing the sync report tables manually. (4311965)

Issue

Optional but recommended: Clear the old logs and / or audit entries via the Directory Sync Console.

1. Select View Logs and Auditing

2. Click on Clear

3. Select the criteria and set clear automatically.

Select a number and time unit (Weeks, Days, or Hours) from the drop-down list to clear the log or audits of older entries and then click OK.  Also for Log entries, you have the option to clear the log entries of all profiles older than the selected time automatically by selecting Clear log automatically.

*** The logs can also be cleared via an SQL Query as documented below. See "Optional - Clear the BTlog2 table manually." 

Clearing the Sync Reports and (Optional) Log.

1. Start the SQL Server Management Studio as an Administrator.

2. Sign on using the correct credentials.

3. Open the server and traverse down to Databases -- BTCodex -- Tables.

4. Traverse down to dbo.DirSync_SyncReport.

5. Click on Select Top 1000 Rows.

6. The SQLQuery command will return the top 1000 lines from the dbo.DirSync_SyncReport table

*** Please note the BT_Config_FK value.

In this example:

BT_Config_FK=1 -- User

BT_Config_FK=2 -- Group

BT_Config_FK=3 -- Rooms and Resources

BT_Config_FK=4 -- Mail-In

The queries below executed one at a time, will clear your Sync Report logs. 

Note: The BT_Config_FK value is obtained from your Directory Sync Pro console in which the number to the leftmost portion/column of the profiles view (also known as) the ID column will be the value to insert into the below commands 

7. Create a new SQL Query

8. Copy and Paste the (1) command below into the new window and press (2) ! Execute

Now clear the Users, Groups, Rooms and Resources and Mail in databases

DELETE FROM [BTCODEX].[dbo].[DirSync_SyncReport] WHERE BT_Config_FK =  1

When complete you can go back to the original query (top 1000 rows) and run the report again. You will notice that all user entries have been cleared. 

Continue by clearing Groups, Rooms and Resources and Mail in Databases

DELETE FROM [BTCODEX].[dbo].[DirSync_SyncReport] WHERE BT_Config_FK =  2

DELETE FROM [BTCODEX].[dbo].[DirSync_SyncReport] WHERE BT_Config_FK =  3

DELETE FROM [BTCODEX].[dbo].[DirSync_SyncReport] WHERE BT_Config_FK =  4

9. Now clear the SyncReportMembership table

Click on (1) dbo.DirSync_SyncReportMembership and select the top 1000 rows (2) query. Notice the data and take note of BT_Config_FK

Copy and paste the command below into a new Query and execute. This command can take anywhere from a few minutes to hours depending on the amount of data.

DELETE FROM [BTCodex].[dbo].[DirSync_SyncReportMembership] WHERE BT_Config_FK = 2

Run the top 1000 query again to confirm (Optional)

The table should now be empty.

10. Now clear and reset the DirSync_SyncReportProfile. 

select the top 1000 rows query. Notice the data and take note of BT_Config_FK.  Copy and paste the command below into a new Query and execute. 

DELETE FROM [BTCodex].[dbo].[DirSync_SyncReportProfile] WHERE BT_Config_FK = 1

DELETE FROM [BTCodex].[dbo].[DirSync_SyncReportProfile] WHERE BT_Config_FK = 2

DELETE FROM [BTCodex].[dbo].[DirSync_SyncReportProfile] WHERE BT_Config_FK = 3

DELETE FROM [BTCodex].[dbo].[DirSync_SyncReportProfile] WHERE BT_Config_FK = 4

Optional - Clear the BTlog2 table manually.

1. Select BTCodexLog -- dbo.BT_Log2 and run the Top 1000 Query.

Issue the following SQL Query command:

Select * from BTCodexLog.dbo.BT_log2

Truncate table BTCodexLog.dbo.BT_log2

Results:

The Sync report is now empty.

The log