In SharePoint Server 2010, the profile synchronization database keeps growing after each sync. This seems to be expected behavior at the time of writing this post.

If you review the Database Types and Descriptions (http://technet.microsoft.com/en-us/library/cc678868.aspx) article on TechNet, it states the following for general size and growth factors:
“Medium to large. Growth factors include the number of users and groups, and the ratio of users to groups.”

However, it does not state that the database will grow forever with no cleanup process that will clean up the database.

One workaround would be to delete your UPA (keeping the Social and Profile DB) and recreate the UPA with a new Sync DB once a month, thus not allowing the sync DB to grow too large.

  • Reset profile synchronization database

The User Profile Synchronization database serves as a staging area for user profile information. User Profile information that is stored in the profile store and synchronization database is consumed by the User Profile service. By following the below steps, you can safely reset a User Profile Synchronization database without losing information in the profile store.

A few things to be careful about before you do this:

  1. Make screenshots of all settings (i.e. database names, My Site host location, etc.).
  2. Back up the User Profile service application. For more information, see Back up a service application (SharePoint Server 2010)
  3. If you are using the My Site cleanup timer job, you must DISABLE it before you reset the synchronization database. Otherwise, the job will delete all user profiles and My Sites from the farm. For information about this timer job, see the Timer job reference (SharePoint Server 2010). For information about the Windows PowerShell cmdlets that you use to enable and disable this timer job, see Timer jobs cmdlets (SharePoint Server 2010).
  4. Any custom properties that have been mapped to the user profile attributes will be lost.

To reset profile synchronization by using Windows PowerShell:

Verify that you meet the following minimum requirements:

  • You must be a member of the Farm Administrators group on the computer that is running the SharePoint Central Administration Web site.
  • The farm account, which is created during the SharePoint farm setup, must also be a Local Administrator on the server where the User Profile Synchronization service is deployed.

This is required to start the User Profile Synchronization service. After the User Profile Synchronization service is started you can remove the farm account from the Administrators group.

  1. As a precaution, back up the User Profile service application. For more information, see Back up a service application (SharePoint Server 2010).
  2. If you are using the My Site cleanup timer job, you must DISABLE it before you reset the synchronization database. Otherwise, the job will delete all user profiles and My Sites from the farm. For information about this timer job, see the Timer job reference (SharePoint Server 2010). For information about the Windows PowerShell cmdlets that you use to enable and disable this timer job, see Timer jobs cmdlets (SharePoint Server 2010).
  3. On the Start menu, click All Programs.
  4. Click Microsoft SharePoint 2010 Products.
  5. Right-click SharePoint 2010 Management Shell and then click Run as administrator.
  6. In the User Account Control dialog box, click Yes.
  7. At the Windows PowerShell command prompt, type the following command to stop the SharePoint 2010 Timer service:

Net stop SPtimerv4

Copy the following code and paste it into a text editor, such as Notepad:  

   1: $SyncDB=Get-SPDatabase –Id <GUID of User Profile Sync DB>
   2: $syncdb.Unprovision()
   3: $syncdb.Status='Offline'
   4: Get-SPServiceApplication
   5: # Copy the GUID associated with the User Profile Service and paste it after "Id" in the next command
   6: $UPA = Get-SPServiceApplication –Id <GUID of User Profile Service Application>
   7: $UPA.ResetSynchronizationMachine()
   8: $UPA.ResetSynchronizationDatabase()
   9: $syncdb.Provision()

Replace the following placeholders with values where:

  • <GUID of User Profile Sync DB> is the GUID of the synchronization database.
  • <GUID of User Profile Service Application> is the GUID of the User Profile Service application.

For more information, see Get-SPDatabase.

Note:  This script does not show any status or progress.

  1. Save the file as an ANSI-encoded text file and name the file ResetSyncDB.ps1.
  2. At the Windows PowerShell change to the directory where you saved the file.
  3. Type the following command:
   1: ./ResetSyncDB.ps1
  1. Add the User Profile Synchronization service account (farm account) as the dbowner on the Sync DB (using SQL Server Management Studio).

Perform the following steps to check whether you have granted  the correct permissions to the database access account:

  1. Connect to the computer that runs SQL Server by using an account that has Administrator permissions.
  2. In SQL Server Management Studio, Object Explorer navigation pane, expand the Security node, and then expand the Logins node. The name of the database access account indicates that it is a SQL login.
  3. If the account exists, in the Object Explorer navigation pane, expand the Databases node, expand the user profile Sync database node, expand the Security, and then click Roles.
  4. Expand the Database Roles node, right-click db_owner role and select Properties.
  5. In the Database Role Properties dialog box, check whether the database access account is in the Members of this role list. If the account is not listed, click Add.

For more information about db_owner role, please refer to the following article:

  1. Database-Level Roles:                            http://technet.microsoft.com/en-us/library/ms189121.aspx
  2. How to: Create a SQL Server Login:     http://go.microsoft.com/fwlink/?LinkId=211993
  3. How to: Create a Database User:          http://go.microsoft.com/fwlink/?LinkId=211994 

>  At the Windows PowerShell command prompt, type the following command to start the SharePoint 2010 Timer service:

Net start SPtimerv4

  • Start the Profile Synchronization service. For more information, see the Start the User Profile Synchronization service section of the "Configure profile synchronization" topic.
  • After the User Profile Synchronization Service is started, reset IIS.

IISreset

For more information about how to  Reset IIS , see the Reset IIS section of the "Configure profile synchronization" topic.

Note:  After you reset IIS, pages of the Central Administration Web site will take several seconds to load.

  1. Create the synchronization connections to the data sources in the Central Administration UI. For more information, see Configure connections and import data from directory services.
  2. Run full user profile synchronization. For more information, see the following articles:

I would recommend a full crawl on your search service application as well to make sure all People results are accurate.