SharePoint 2010 SyncDB keeps growing out of control


I’m sure there are several others that’s had this problem so I thougt I’d share a solution I found recently.
The SyncDB is filled with instance data for every sync that is made. If you run on an SQL Server Express as many propably are you will sooner or later run out of space for this database. In case you’re not running SQL Express you might want to implement this anyway. Since a database growing out of control is never good.

The link below contains a stored procedure that cleans the instance data that is no longer usable from the database. Something that should have been included oob in SharePoint but I guess Microsoft forgot about.
http://paulliebrand.com/2011/05/26/user-profile-synchronization-database-growing-out-of-control/

For my own sake I’ll include the script in this post aswell.

USE [Sync DB]
GO

/****** Object: StoredProcedure [fim].[TruncateInstanceData] Script Date: 08/10/2011 14:09:11 *****/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [fim].[TruncateInstanceData]
AS
BEGIN

--************************************************************
--* * *
--* Copyright (C) Microsoft. All rights reserved. *
--* *
--************************************************************
SET NOCOUNT ON;
DECLARE @truncationTime datetime;
SET @truncationTime = DATEADD(day, -1, GETUTCDATE());
DELETE FROM [dbo].[InstanceData]
WHERE ([created] < @truncationTime)
END

Before running this script you need to create a schema called FIM and change the DB name to match your own database.
and to quote Paul:

DISCLAIMER: Microsoft has told me that this script cannot be altered in anyway or I run the risk of being unsupported. Please use this at your own discretion.

Advertisements

%d bloggers like this: