Sunday, January 23, 2011

Moving the User Profile Service Application Synchronization Database

So, the problems with my Sync DB as I mentioned in a previous post finally caught up to me. Running low on disk space, we decided to add another block of disk space to the server and move the Sync DB over there.

Adding the volume on Amazon EC2 was very straightforward. It was just a matter creating a new volume, attaching it to the server, enabling and then formatting the drive. This is covered quite nicely in Attaching an EBS Volume to EC2.

Now with the drive ready, I needed to move the DB files over. The instructions for Moving User Databases are pretty straightforward. However, I ran into some hiccups - I had open connections to the database (from when I was troubleshooting the database size :-) ) and the command to take the database offline simply waits until those connections are closed and you're none the wiser. Also, the new files / locations need to have the right permissions otherwise you can't bring the database back online. So, here are my updated steps.

  1. Close all connections to your database. You can check for open connections by executing an sp_who2
  2. Take the database offline by running the following:
    ALTER DATABASE [Sync DB] SET OFFLINE
  3. Move the files to the new location
  4. Update the new location in the system catalog as follows (make sure you use your own location!)
    ALTER DATABASE [Sync DB] MODIFY FILE ( NAME = 'Sync DB', FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Sync DB.mdf' );
    ALTER DATABASE [Sync DB] MODIFY FILE ( NAME = 'Sync DB_log', FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Sync DB_log.LDF' );
  5. Make sure that the database user has the correct permissions to the database files. I just copied the original settings which is giving the SQLServerMSSQLUser group full control access to the DATA folder (and the new database files). 
  6. Bring the database back online by running the following:
    ALTER DATABASE [Sync DB] SET ONLINE
  7. Verify the file change by running the following:
    SELECT  name, physical_name FROM sys.master_files WHERE sys.master_files.database_id = DB_ID(N'Sync DB')
I still haven't gotten to the root cause of the database file being so large. However, I just needed to buy some time for now.


5 comments:

  1. According to http://technet.microsoft.com/en-us/library/ff830030(WS.10).aspx FIM 2010 ships with 4 SQL agent jobs:

    FIM_DeleteExpiredSystemObjectsJob
    FIM_TemporalEventsJob
    FIM_Maintain_GroupsJob
    FIM_Maintain_SetsJob

    One of the steps is specifically named:
    FIM_TruncateInstanceDataStep

    These SQL Agent jobs do not appear to be installed along with the FIM 2010 service installed as part of SharePoint 2010. It appears to me that these missing jobs are causing the enourmous SyncDB growth rate. IIRC the documentation indicated the SyncDB should be about 650 KB per profile, which is most certainly is not right now.

    ReplyDelete
  2. Interesting. On my servers, the SQL Server Agent is not even enabled. It does make sense that missing these jobs would cause the database to grow in size. However, I also have several other servers that are setup the same way, without these jobs, and they are running fine.

    Thanks for the tip, I will definitely do some more digging.

    ReplyDelete
  3. Have you figured out how to shrink the profile sync database? I don't have FIM and I have an ever growing sync db on my test farm.

    ReplyDelete
  4. I worked with Microsoft on this issue and they gave me a supported workaround which I blogged about here:

    http://paulliebrand.com/2011/05/26/user-profile-synchronization-database-growing-out-of-control/

    Hopefully they put a proper fix in at some point.

    Paul Liebrand

    ReplyDelete
  5. I found your article very useful, installing the Feb 2012 CU solved the problem somewhat, but I needed a way to reduce the database size further, I did this by changing a property on a timer job. 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

    ReplyDelete