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())
5 thoughts on “Sync DB Grows Indefinitely”
Leave a Reply
Want to talk?
Drop us a line. We are here to answer your questions 24*7.
Hi Joel,
I found your article very useful, but I’ve read that running that SQL is unsupported. That procedure gets created as apart of the Feb 2012 CU, which will reduce the database size dramatically, but I needed a way to reduce the database size further,. I’ve written a post about it at http://troyvssharepoint.blogspot.co.uk/2012/10/sharepoint-user-profile-sync-database.html
Hopefully this helps someone else
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:
[sourcecode language=””””]
$a=Get-SPTimerJob | ? { $_.Name -like “User Profile Service Applicatio*”}
$a | fw
[/sourcecode]
Then apply using your name:
[sourcecode language=””””]
$timerJob = (Get-SPTimerJob | ? { $_.Name -eq “User Profile Service Application v01_ProfileImportJob” })
$timerJob.KeepHistoryInterval
$timerJob.KeepHistoryInterval = 1
$timerJob.Update()
$timerJob | Start-SPTimerJob
[/sourcecode]
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.
basically you’re wiping the table, right?
Yes, but preserving most recent records, and deleting the records gradually, to avoid overloading transaction logs.
Joel