Posts

Sync DB grows indefinitely

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

SharePoint Farm Trusted Certificate

We all want our SharePoint farms to be blindingly fast.  Here’s an easy step you can take to improve performance, by reducing the traffic from your SharePoint servers that check on farm certificate validity.  Even though you may be using Classic NTLM as authentication for your Web Applications, inside SharePoint the components rely on Claims Authentication.

First, let’s export the Farm Certificate to C: with these two PowerShell commands:

$rootCert = (Get-SPCertificateAuthority).RootCertificate
$rootCert.Export("Cert") | Set-Content C:SharePointRootAuthority.cer -Encoding byte

Now, let’s import the certificate into the Microsoft Management Console (MMC):

1. Click Start, Run, MMC

2. Add the Certificates snap-in:

3. Select “Computer Account”

4. Then import the Certificate: