_dta_mv_ how do i get rid of them

SQL Server Administration
1

HI all,

SQL created tons of these
_dta_mv_xxx of these viewa.
i changed a lot in the DB.
I was wondering how do i get rid of the ones that are out of date...

thanks a lot

2

This sounds like it is something specific to your database and/or environment. What is causing these views to be generated? If you are referring to views under the System Views folder, don't remove them.

3

no these are not system views.
management job or sql optimizer

4

I am not aware of any SQL Server internal or system processes that would create views in your database. My guess is that it is probably some job that is specific to your environment. You might be able to get a clue as to who/what is creating that by looking at the create_time ( select * from sys.views should give you that info) and comparing the creation time and frequency of creation with the scheduled jobs that run on your system.

5

I would bet they are from the Database Engine Tuning Advisor which uses the dta prefix for its objects it recommends to be created. I don't use the DTA. :wink:

6

It is the DTA.
okay how do i delete views and indexes and all references to them?

7

You delete them the same way you'd delete any object...DROP syntax. But please do this in a test environment first.

8

i mean delete them all at the same time! we talking about couple 100

9

Write a script to generate the drop statements like shown below and run this query. Copy the output of the query to a new query window, inspect it to make sure you are dropping only the views that you want and run it.

SELECT 'DROP VIEW ' + NAME FROM sys.views
WHERE name LIKE 'dtamv%'