Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

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?

Edit:

A few notes:

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

share|improve this question
2  
What is the output for --- select * from master..sysprocesses where blocked > 0 --- and --- SELECT AVG(current_tasks_count) AS [Avg Task Count] ,AVG(runnable_tasks_count) AS [Avg Runnable Task Count] ,AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count] FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE scheduler_id < 255 OPTION (RECOMPILE) – Kin Apr 29 at 17:03
@Kin erm ... that's a lot of output ... for the first query i see two procs blocked by the spid attempting to create the index. as for the spid itself, it has a waittype of 0x0000 and a lastwaittype of MISCELLANEOUS. – swasheck Apr 29 at 17:41
I dont think you will get a lot of output for the 1st query as it will show you what is being blocked. Also, for the second query, it will tell you the average current tasks waiting for their turn to be on schedulers. What you have given is the wait type !! What is the Max dop setting on the server ? – Kin Apr 29 at 19:17
@Kin you've not yet told me what you're looking for. 32 columns in sysprocesses and then the three in the schedulers. I gave wait types as a shot in the dark. – swasheck Apr 29 at 19:49
@Kin MAXDOP is unconfigured – swasheck Apr 29 at 19:50

1 Answer

up vote 1 down vote

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.

What's the wait type of the CREATE INDEX statement?

share|improve this answer
Pulling from sp_whoisactive there is no wait_info for this task – swasheck Apr 29 at 15:41
Is there an open transaction for that session that's trying to create the index? Are you doing SQL 2008 R2 to SQL 2000 replication or SQL 2008 R2 to SQL 2008 R2? – mrdenny♦ Apr 29 at 15:44
DBCC OPENTRAN has no open transactions. This is 2008R2 to 2008R2, now. – swasheck Apr 29 at 15:47
If there's no open transaction then nothing should be holding a lock. Check sys.locks for that session. What locks are being held? – mrdenny♦ Apr 29 at 15:48
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. – swasheck Apr 29 at 15:55
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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