Sync DB grows indefinitely

156

Sync DB is a database that is a part of the User Profile Service related to Active Directory synchronization.   Unfortunately it tends to grow in an unconstrained fashion.  SharePoint has no built-in cleanup mechanism.  The table that grows without bounds is “InstanceData”.  The followed Stored Procedure should be first run in waves, so as not to overload the transaction logs or tempDB.  Running on at least a monthly basis is recommended.

The following is a report that shows monthly growth in rowcount:

SELECT COUNT ([lastUpdated]) as countOfInstancedata, month ([created]) as month, YEAR([created]) as year
       FROM [Sync DB].[dbo].[InstanceData]
  group by month ([created]), YEAR ([created]) order by YEAR ([created]), month ([created])

Here’s the stored procedure:

USE [Sync DB]
GO
CREATE SCHEMA FIM
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [fim].[TruncateInstanceData]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @truncationTime datetime;
SET @truncationTime = DATEADD(day, -1, GETUTCDATE());
DELETE FROM [dbo].[InstanceData]
WHERE ([created] < @truncationTime)
END

Alternatively, I’ve been running this loop to gradually delete rows.  You can adjust the loop number, delete size (rowcount) and delay to taste:

EXEC sp_spaceused N'dbo.Instancedata';
 -- Declare local variables
DECLARE @NumberOfLoops AS int;
SET @NumberOfLoops = 500;
DECLARE @CurrentLoop AS int;
SET @CurrentLoop = 0

WHILE @CurrentLoop < @NumberOfLoops BEGIN
set rowcount 10000
delete from [Sync DB].[dbo].[InstanceData] 
where [Sync DB].[dbo].[InstanceData].created <CONVERT(DATETIME,'2012-02-01 00:00:00', 102)

WAITFOR DELAY '00:00:01:00';
SET @CurrentLoop = @CurrentLoop + 1;END
-- Check space used by table after we are done
EXEC sp_spaceused N'dbo.BigLoggingTable';

A more generic WHERE clause I’ve successfully used is:
WHERE [Sync DB1].[dbo].[InstanceData].created <= DATEADD(day, -60, GETUTCDATE())

Share this entry

5 Responses

    1. Wow, that’s a really great find. Note that the timer job name depends on the name of the User Profile Service. For me the name was different, here’s the commands for anyone interested.

      To get the name:

      $a=Get-SPTimerJob | ? { $_.Name -like "User Profile Service Applicatio*"}
      $a | fw

      Then apply using your name:

      $timerJob = (Get-SPTimerJob | ? { $_.Name -eq "User Profile Service Application v01_ProfileImportJob" })
      $timerJob.KeepHistoryInterval
      $timerJob.KeepHistoryInterval = 1
      $timerJob.Update()
      $timerJob | Start-SPTimerJob

      1. Ah great point!

        I’ve updated my blog now to use
        $timerJob = (Get-SPTimerJob | ? { $_.TypeName -eq “Microsoft.Office.Server.UserProfiles.UserProfileImportJob” })

        that way it doesn’t matter what the name is.

        Obviously if there is more than one Import Profile Job (E.g. if you are in a multi tenancy environment) my script would need to be adjusted as it only works for single tenancy.

Leave a Reply

Your email address will not be published. Required fields are marked *

Table of Contents

Categories

Categories