Home › Server Overview › SQL Server Quick Scan Report › Quick Scan Report – Leftover DTA tables.

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]
GO

GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dtproperties')
   DROP TABLE dtproperties;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_reports')
   DROP TABLE dta_reports;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_sessions')
   DROP TABLE dta_sessions;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_stats_internal')
   DROP TABLE dta_stats_internal;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_tuning_log')
   DROP TABLE dta_tuning_log;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_output')
   DROP TABLE dta_output;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_progress')
   DROP TABLE dta_progress;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_indexcolumn')
	DROP TABLE DTA_reports_indexcolumn;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_partitionscheme')
	DROP TABLE DTA_reports_partitionscheme;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_querycolumn')
	DROP TABLE DTA_reports_querycolumn;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_querydatabase')
	DROP TABLE DTA_reports_querydatabase;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_queryindex')
	DROP TABLE DTA_reports_queryindex;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_querytable')
	DROP TABLE DTA_reports_querytable;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_tableview')
	DROP TABLE DTA_reports_tableview;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_tuninglog')
	DROP TABLE DTA_tuninglog;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_tuningresults')
	DROP TABLE DTA_tuningresults;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_tuningresults_part')
	DROP TABLE DTA_tuningresults_part;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_reports_column')
   DROP TABLE dta_reports_column;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_index')
	DROP TABLE DTA_reports_index;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_partitionfunction')
	DROP TABLE DTA_reports_partitionfunction;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_query')
	DROP TABLE DTA_reports_query;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DTA_reports_table')
	DROP TABLE DTA_reports_table;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_reports_database')
   DROP TABLE dta_reports_database;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dta_input')
   DROP TABLE dta_input;


GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_wkld_analysis_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_wkld_analysis_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_wkld_analysis_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_wkld_analysis_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_view_table_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_view_table_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_view_table_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_view_table_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_update_tuninglog_errorfrequency')
	DROP PROCEDURE [dbo].[sp_DTA_update_tuninglog_errorfrequency]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_update_session')
	DROP PROCEDURE [dbo].[sp_DTA_update_session]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_table_access_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_table_access_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_table_access_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_table_access_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_start_xmlprefix')
	DROP PROCEDURE [dbo].[sp_DTA_start_xmlprefix]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_tuningresults_part')
	DROP PROCEDURE [dbo].[sp_DTA_set_tuningresults_part]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_tuningresults')
	DROP PROCEDURE [dbo].[sp_DTA_set_tuningresults]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_tuninglogtablename')
	DROP PROCEDURE [dbo].[sp_DTA_set_tuninglogtablename]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_progressinformation')
	DROP PROCEDURE [dbo].[sp_DTA_set_progressinformation]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_outputinformation')
	DROP PROCEDURE [dbo].[sp_DTA_set_outputinformation]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_set_interactivestatus')
	DROP PROCEDURE [dbo].[sp_DTA_set_interactivestatus]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_indexrelations_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_query_indexrelations_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_indexrelations_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_query_indexrelations_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_detail_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_query_detail_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_detail_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_query_detail_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_costrange_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_query_costrange_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_costrange_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_query_costrange_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_cost_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_query_cost_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_query_cost_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_query_cost_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_tableview')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_tableview]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_table')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_table]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_querytable')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_querytable]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_queryindex')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_queryindex]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_querydatabase')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_querydatabase]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_querycolumn')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_querycolumn]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_query')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_query]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_partitionscheme')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_partitionscheme]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_partitionfunction')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_partitionfunction]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_indexcolumn')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_indexcolumn]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_index')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_index]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_database')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_database]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_reports_column')
	DROP PROCEDURE [dbo].[sp_DTA_insert_reports_column]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_progressinformation')
	DROP PROCEDURE [dbo].[sp_DTA_insert_progressinformation]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_insert_DTA_tuninglog')
	DROP PROCEDURE [dbo].[sp_DTA_insert_DTA_tuninglog]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_usage_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_index_usage_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_usage_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_index_usage_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_recommended_detail_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_index_recommended_detail_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_detail_recommended_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_index_detail_recommended_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_detail_current_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_index_detail_current_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_index_current_detail_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_index_current_detail_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_help_session')
	DROP PROCEDURE [dbo].[sp_DTA_help_session]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_tuningoptions')
	DROP PROCEDURE [dbo].[sp_DTA_get_tuningoptions]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_tuninglog')
	DROP PROCEDURE [dbo].[sp_DTA_get_tuninglog]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_tableids')
	DROP PROCEDURE [dbo].[sp_DTA_get_tableids]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_session_tuning_results')
	DROP PROCEDURE [dbo].[sp_DTA_get_session_tuning_results]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_session_report')
	DROP PROCEDURE [dbo].[sp_DTA_get_session_report]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_pstableids')
	DROP PROCEDURE [dbo].[sp_DTA_get_pstableids]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_pftableids')
	DROP PROCEDURE [dbo].[sp_DTA_get_pftableids]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_interactivestatus')
	DROP PROCEDURE [dbo].[sp_DTA_get_interactivestatus]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_indexableids')
	DROP PROCEDURE [dbo].[sp_DTA_get_indexableids]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_databasetableids')
	DROP PROCEDURE [dbo].[sp_DTA_get_databasetableids]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_get_columntableids')
	DROP PROCEDURE [dbo].[sp_DTA_get_columntableids]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_event_weight_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_event_weight_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_event_weight_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_event_weight_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_end_xmlprefix')
	DROP PROCEDURE [dbo].[sp_DTA_end_xmlprefix]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_delete_session')
	DROP PROCEDURE [dbo].[sp_DTA_delete_session]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_database_access_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_database_access_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_database_access_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_database_access_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_column_access_helper_xml')
	DROP PROCEDURE [dbo].[sp_DTA_column_access_helper_xml]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_column_access_helper_relational')
	DROP PROCEDURE [dbo].[sp_DTA_column_access_helper_relational]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_cleanup_hypothetical_metadata')
	DROP PROCEDURE [dbo].[sp_DTA_cleanup_hypothetical_metadata]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_check_permission')
	DROP PROCEDURE [dbo].[sp_DTA_check_permission]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_DTA_add_session')
	DROP PROCEDURE [dbo].[sp_DTA_add_session]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'fn_DTA_unquote_dbname')
	DROP FUNCTION [dbo].[fn_DTA_unquote_dbname]

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.


Enroll Today!
SteveStedman5
SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!