Quick Scan Report – Leftover DTA Tables

Microsoft Database Tuning Advisor (DTA) is a tool that comes with Microsoft SQL Server, which helps database administrators and developers analyze SQL Server workloads and recommend ways to improve the database performance.

DTA analyzes SQL Server traces or workload files, which contain a record of all the database operations that occurred during a specified time period. Based on the workload analysis, DTA recommends changes to the database schema, indexes, and queries that can improve query performance.

The Database Tuning Advisor commonly left behind monitoring tables in the msdb database. These can safely be removed unless you are actively using the database tuning advisor. Worst case if you are it will just put the tables back again next time you run it.

To remove the DTA (Database Tuning Advisor) left-over tables from the msdb database in SQL Server, you can use the following steps:

1. Open SQL Server Management Studio and connect to your SQL Server instance.
2. Open a new query window and make sure you are connected to the msdb database.
3. Execute the following script to drop the DTA tables:

USE [msdb]

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dtproperties')
BEGIN
	DROP TABLE dtproperties;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_reports')
BEGIN
	DROP TABLE dta_reports;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_sessions')
BEGIN
	DROP TABLE dta_sessions;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_stats_internal')
BEGIN
	DROP TABLE dta_stats_internal;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_tuning_log')
BEGIN
	DROP TABLE dta_tuning_log;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_output')
BEGIN
	DROP TABLE dta_output;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_progress')
BEGIN
	DROP TABLE dta_progress;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_indexcolumn')
BEGIN
	DROP TABLE DTA_reports_indexcolumn;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_partitionscheme')
BEGIN
	DROP TABLE DTA_reports_partitionscheme;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_querycolumn')
BEGIN
	DROP TABLE DTA_reports_querycolumn;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_querydatabase')
BEGIN
	DROP TABLE DTA_reports_querydatabase;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_queryindex')
BEGIN
	DROP TABLE DTA_reports_queryindex;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_querytable')
BEGIN
	DROP TABLE DTA_reports_querytable;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_tableview')
BEGIN
	DROP TABLE DTA_reports_tableview;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_tuninglog')
BEGIN
	DROP TABLE DTA_tuninglog;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_tuningresults')
BEGIN
	DROP TABLE DTA_tuningresults;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_tuningresults_part')
BEGIN
	DROP TABLE DTA_tuningresults_part;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_reports_column')
BEGIN
	DROP TABLE dta_reports_column;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_index')
BEGIN
	DROP TABLE DTA_reports_index;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_partitionfunction')
BEGIN
	DROP TABLE DTA_reports_partitionfunction;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_query')
BEGIN
	DROP TABLE DTA_reports_query;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_table')
BEGIN
	DROP TABLE DTA_reports_table;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_database')
BEGIN
	DROP TABLE DTA_reports_database;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_output_recommendation')
BEGIN
	DROP TABLE DTA_output_recommendation;
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_input')
BEGIN
	DROP TABLE DTA_input;
END


IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_wkld_analysis_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_wkld_analysis_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_wkld_analysis_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_wkld_analysis_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_view_table_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_view_table_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_view_table_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_view_table_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_update_tuninglog_errorfrequency')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_update_tuninglog_errorfrequency]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_update_session')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_update_session]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_table_access_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_table_access_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_table_access_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_table_access_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_start_xmlprefix')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_start_xmlprefix]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_tuningresults_part')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_set_tuningresults_part]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_tuningresults')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_set_tuningresults]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_tuninglogtablename')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_set_tuninglogtablename]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_progressinformation')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_set_progressinformation]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_outputinformation')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_set_outputinformation]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_interactivestatus')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_set_interactivestatus]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_indexrelations_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_query_indexrelations_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_indexrelations_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_query_indexrelations_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_detail_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_query_detail_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_detail_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_query_detail_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_costrange_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_query_costrange_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_costrange_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_query_costrange_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_cost_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_query_cost_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_cost_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_query_cost_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_tableview')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_tableview]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_table')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_table]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_querytable')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_querytable]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_queryindex')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_queryindex]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_querydatabase')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_querydatabase]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_querycolumn')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_querycolumn]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_query')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_query]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_partitionscheme')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_partitionscheme]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_partitionfunction')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_partitionfunction]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_indexcolumn')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_indexcolumn]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_index')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_index]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_database')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_database]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_column')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_column]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_progressinformation')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_progressinformation]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_DTA_tuninglog')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_DTA_tuninglog]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_usage_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_index_usage_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_usage_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_index_usage_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_recommended_detail_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_index_recommended_detail_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_detail_recommended_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_index_detail_recommended_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_detail_current_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_index_detail_current_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_current_detail_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_index_current_detail_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_help_session')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_help_session]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_tuninptions')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_tuninptions]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_tuninglog')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_tuninglog]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_tableids')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_tableids]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_session_tuning_results')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_session_tuning_results]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_session_report')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_session_report]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_pstableids')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_pstableids]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_pftableids')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_pftableids]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_interactivestatus')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_interactivestatus]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_indexableids')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_indexableids]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_databasetableids')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_databasetableids]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_columntableids')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_columntableids]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_event_weight_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_event_weight_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_event_weight_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_event_weight_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_end_xmlprefix')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_end_xmlprefix]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_delete_session')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_delete_session]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_database_access_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_database_access_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_database_access_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_database_access_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_column_access_helper_xml')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_column_access_helper_xml]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_column_access_helper_relational')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_column_access_helper_relational]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_cleanup_hypothetical_metadata')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_cleanup_hypothetical_metadata]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_check_permission')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_check_permission]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_add_session')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_add_session]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'fn_DTA_unquote_dbname')
BEGIN
	DROP FUNCTION [dbo].[fn_DTA_unquote_dbname]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_tuningoptions')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_get_tuningoptions]
END

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_output_recommendation')
BEGIN
	DROP PROCEDURE [dbo].[sp_DTA_insert_output_recommendation]
END

Note: Before dropping any tables, it is recommended that you back up your msdb database to ensure that you can restore it in case of any issues. Additionally, ensure that you have the necessary permissions to drop tables in the msdb database.

If you partially delete dts, you may end up with an error message: Database Engine Tuning Advisor
——————————

Failed to open a new connection.

——————————
ADDITIONAL INFORMATION:

Invalid object name ‘msdb.dbo.DTA_input’. (Microsoft SQL Server, Error: 208)

If you are seeing that message, just run the entire script above and then reconnect to DTA.