Sharepoint 2010: User Profile Sync DB Size needs to be reduce

Category: sharepoint 2010 setup

Question

K.Mohan on Wed, 20 Feb 2013 16:09:48


Hi All,

            I am looking to reduce the size of user profile sync database currently  having 33GB in our sharepoint 2010 production environment.Is there any Hot fixes  or scripts are available to resolve this issue ,Could you please let me know.Thanks in advance.


k.Mohan

Replies

Trevor Seward on Wed, 20 Feb 2013 16:25:38


There is a fix in the Feb 2012 CU that addresses table size in the Sync database:

http://support.microsoft.com/kb/2597150

Also validate that if you're using the Full Recovery Model, that you're doing proper trimming of the log file.  If high availability of the database isn't required, then change the recovery model to Simple and shrink the log file.


SharePoint - Nauplius Applications
Microsoft SharePoint Server MVP
MCITP: SharePoint Administrator 2010

-----------------------
This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.


Matis Kofoed Petersen on Wed, 20 Feb 2013 22:45:47


If high availability of the database isn't required, then change the recovery model to Simple and shrink the log file.



It will not help to shrink the DB in the long run it will give your SQL server more workload, as the DB will grow again, http://technet.microsoft.com/en-us/library/cc298801.aspx.

Trevor Seward on Wed, 20 Feb 2013 22:46:58


It will actually allow the original poster to shrink the TRN (which is what I would assume is taking up a majority of space).  Given the Sync database is disposable anyways, generally it shouldn't be part of HA.

Matis Kofoed Petersen on Wed, 20 Feb 2013 23:46:06


When you are shrinking your syncdb the tempdb will grow, and then you will need to do the same job on the tempdb.

He should apply the CU http://support.microsoft.com/kb/2597150

Or reset the UPS

Trevor Seward on Wed, 20 Feb 2013 23:55:53


Hence already pointing the original poster to the CU, as that would likely yield the most benefit.  However, tempdb growth is temporary.  If the SyncDb is in the Full Recovery Model, then a Simple + shrink would be a one-time operation and is something to consider.

Matis Kofoed Petersen on Thu, 21 Feb 2013 09:25:04


Trevor tempdb will not be shrink automaticly, tempdb will be recreated upon restart of SQL instance where it would be shrunked.



Average Admin on Tue, 25 Jun 2013 19:34:21


You need to apply the hotfix mentioned, which will add procs that will keep it cleaned up.  However, you should also set the "days of history".  For my Dev, I set it to 1 day.  Once you set it, run it to clean it up.

$timerJob = Get-SPTimerJob | ? { $_.TypeName -eq "Microsoft.Office.Server.UserProfiles.UserProfileImportJob" }
$timerJob.KeepHistoryInterval = 1 
$timerJob.Update()   
$timerJob | Start-SPTimerJob