5

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 > 0 does return the spid with one open transaction
  • DBCC OPENTRAN has no open transactions.
  • MAXDOP is unconfigured
1

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.

  • I never really got anything meaningful from the output of the agent. I noticed that the data itself is triplicated and wondered if that was causing the CREATE INDEX to choke. It's nonclustered and I just took the text and created the index myself in 5 seconds. – swasheck Apr 29 '13 at 17:31
  • Did everything get working after you created the index? – mrdenny May 5 '13 at 10:27
  • No, actually. I ended up recreating the publication with the GUI and everything worked for that particular publication – swasheck May 5 '13 at 13:50
  • That's all very strange. At least it's working in the end. – mrdenny May 5 '13 at 14:09
0

Make sure that your subscription database is new one. Issue will be resolved if you use new database for subscription. This worked for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.