So I noticed one my SharePoint database files had grown to 15 GB recently.  I found this strange because we’ve only got about 100 users and I couldn’t conceive how a user profile database could possibly be this big.  Upon further inspection, when looking at the tables within the sync database I found the InstanceData table to be the culprit.  A search produced a well written article explaining the issue found here http://paulliebrand.com/2011/05/26/user-profile-synchronization-database-growing-out-of-control/

The only thing I would add to this post is it is advisable to split up the workload into multiple batch jobs if there are A LOT of rows that will need to be truncated.  To fully reclaim the disk space, I ran DBBC SHRINKDATABASE on my sync database.  Here’s what my script ends up like –> SharePointSyncDB_maintenance

  • Also, your tempdb may grow while you are deleting all the records from the InstanceData table.  You’ll likely be using more disk space than before you started.  If you restart SQL Server it will clear out your tempdb.  Running the shrinkdatabase command will take care of the Sync DB size