1

This is occurring during the end of the analysis when the DTA tries to drop the indexes it created. Unfortunately, with the database in use, this is causing terrible blocking issues. Is there a way to prevent this?

Using SQL Server 2008

Brian
  • 211
  • 1
  • 14
  • I wish there was a way to force the DTA to not block any other processes, even if it meant things took a little longer. Alas, there is not! – SqlRyan Sep 08 '11 at 06:39

1 Answers1

3

the only way to get rid of this is to not run the DTA on a live db.
create a backup of the live db, restore it, and run DTA on that.

Mladen Prajdic
  • 15,457
  • 2
  • 43
  • 51
  • Really no way around this? Or an easy way to full stop the dropping of test indexes? Since it will spawn a new process for each index, killing them is beyond tedious. – Brian Nov 10 '10 at 18:24
  • hmm... you might try putting the database into snapshot isolation mode. this will put pressure on your tempdb though. – Mladen Prajdic Nov 10 '10 at 23:34
  • Could you explain how operating in snapshot isolation mode puts pressure on tempdb? – Brian Nov 11 '10 at 16:48
  • here you go: http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx basically the versioned rows are copied to the tempdb and operated on there – Mladen Prajdic Nov 11 '10 at 18:11