Transactional replication creating a fixup index that's sleeping, but blocking other processes I've just migrated my ERP database server from SQL Server 2000 to SQL Server 2008 R2 (both Enterprise Edition) and that migration went fairly smoothly. 

However, now that I'm trying to recreate replication from the ERP to the Reporting environment, I have two publications that are trying to create a series of MSFixupNonUniqueIndex_{{datetimestamp}} nonunique indexes. The processes creating these indexes are all sleeping, but they are blocking other SQL processes that end-users are running. 

What is causing this fixup index creation and can I avoid it?


Using sp_whoisactive there is no wait_info for the index creation task.
I did not drop the replicated database in the Reporting environment.
The "Reporting environment" is also SQL Server 2008 R2 Enterprise.
This is 2008 R2 to 2008 R2 replication, now.
sysprocesses shows two procs blocked by the spid attempting to create the index. The spid itself has a waittype of 0x0000 and a lastwaittype of MISCELLANEOUS.
sys.locks shows there are 777 locks: 7 Intent, 761 Exclusive, 6 Shared, 2 Schema Mod, 1 Schema Stability. Resources are: 1 ALLOCATION_UNIT, 9 OBJECT, 3 DATABASE, 2 METADATA, 677 PAGE, 11 KEY, 1 HOBT, 73 EXTENT. 
select * from sysprocesses where open_tran &gt; 0 does return the spid with one open transaction
DBCC OPENTRAN has no open transactions.
MAXDOP is unconfigured
If replication is automatically creating indexes, then you probably can't avoid it.  The only way to do so might be to create a matching index on the subscriber.

Try running the merge agent from the command line with a more verbose logging level and see what information it returns. It might be bumping against something which isn't being returned as an error.