How to reindex all of the tables in the backend FIMSynchronizationService Database

PURPOSE / GOAL

We often times recommend re-indexing the backend tables in the FIMSynchronizationService Database. The goal of this article is to provide the steps on how to re-index all of the tables in the backend FIMSynchronizationService Database.

STEPS TO RE-INDEX

Stop the FIM Synchronization Service via Services MMC
1. Click Start > Run and type services and then click Enter Or Navigate to Administrative Tools, and select Services
On the SQL Server, open SQL Server Management Studio
Connect to the SQL Server hosting the FIMSynchronizationService database
1. *NOTE: If you are using a previous identity product, then the database name will be MicrosoftIdentityIntegrationServer.
Click the New Query button
In the white space paste the text from the script section
Once you paste the script, then click the Execute button
Start the FIM Synchronization Service via Services MMC

SCRIPT

USE FIMSynchronizationService (*NOTE: If you are using a previous identity product, then the database name will be MicrosoftIdentityIntegrationServer.)

DECLARE @table_name varchar(1000)

declare c1 cursor for SELECT name

FROM sysobjects

WHERE xtype = 'U'

open c1

fetch next from c1 into @table_name

while @@Fetch_Status = 0

begin

DBCC DBREINDEX (@table_name, '')

fetch next from c1 into @table_name

end

close c1