Sync DB Grows Indefinitely

Joel Plaut

March 28, 2012

All Post

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 row count:

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())