Database Engine Tuning Advisor (DTA, _dta) errors out leaving hypothetical stats/indexes

I have encountered an issue when running the database tuning advisor against SQL Server 2005 that if the advisor errors out while performing its analysis, it will leave the hypothetical stats/indexes in your database.  These should be removed as they may result in additional processing for SQL Server.

 The below statement will generate the scripts to drop these stats/indexes. 

SELECT 'drop statistics [' + object_name(i.[object_id]) + '].['+ i.[name] +

']'
FROM sys.stats as i
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND i.[name] LIKE
'_dta%'
ORDER BY i.name

If you receive the below error change the script to drop index:

Msg 3739, Level 11, State 1, Line 1

Cannot DROP the index  because it is not a statistics collection. 

Add comment