Description of the Database Tuning Advisor (DTA) cleanup script for SQL Server 2005 (899634)


The information in this article applies to:

  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium Based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Workgroup

INTRODUCTION

You may experience problems if you use a pre-June Community Technology Preview (CTP) version of Microsoft SQL Server 2005 Database Tuning Advisor (DTA) client to tune Microsoft SQL Server 2000 or pre-June CTP versions of SQL Server 2005. These pre-June CTP versions of SQL Server 2005 include SQL Server 2005 Beta 2 DTA servers.

This article lists the DTA cleanup script that you can use to prevent such problems.

MORE INFORMATION

To run the following Transact-SQL script on the server, you must use an account that is a member of the sysadmin fixed server role. This script removes some tables and stored procedures from the msdb database.

Warning If you run this script, any previous DTA tuning sessions that are on the server will be deleted.
/*
	Purpose of the Script
	This script cleans up objects created by DTA client on the target server 
	(server being tuned). DTA creates support tables and stored procedures on the target server. 
	The schema of the DTA tables and the DTA SP interfaces changed from Beta 2. 
	
	When to use it
	If a Beta 2 DTA client was used to tune/evaluate against the target server then
	this script needs to be executed (against the target server) for later versions 
	of DTA to function properly.
	
	Impact
	Previous session details are lost.

*/
go
use msdb
go
-- Drop DTA msdb Tables
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_indexcolumn') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_indexcolumn
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_querycolumn') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_querycolumn
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_querytable') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_querytable
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_tableview') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_tableview
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_querydatabase') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_querydatabase
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_queryindex') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_queryindex
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_column') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_column
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_index') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_index
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_table') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_table
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_query') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_query
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_partitionscheme') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_partitionscheme
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_partitionfunction') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_partitionfunction
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_tuninglog') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_tuninglog
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_database') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_reports_database
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_progress') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_progress
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_output')and (type = 'U')and (uid = user_id('dbo'))))
	drop table dbo.DTA_output
if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_input') and (type = 'U') and (uid = user_id('dbo'))))
	drop table dbo.DTA_input
	
-- Drop DTA msdb SP's
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_check_permission' and type = 'P') 
	drop procedure dbo.sp_DTA_check_permission 
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_add_session' and type = 'P') 
	drop procedure dbo.sp_DTA_add_session 
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_delete_session' and type = 'P')
	drop procedure dbo.sp_DTA_delete_session 
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_session_tuning_results' and type = 'P')
	drop procedure dbo.sp_DTA_get_session_tuning_results
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_set_interactivestatus' and type = 'P')
	drop procedure dbo.sp_DTA_set_interactivestatus
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_help_session' and type = 'P')
	drop procedure dbo.sp_DTA_help_session
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_update_session' and type = 'P')
	drop procedure dbo.sp_DTA_update_session
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_tuninglog' and type = 'P')
	drop procedure dbo.sp_DTA_get_tuninglog
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_index_usage_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_index_usage_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_index_usage_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_index_usage_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_database_access_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_database_access_helper_xml
if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_database_access_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_database_access_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_table_access_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_table_access_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_table_access_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_table_access_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_column_access_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_column_access_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_column_access_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_column_access_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_query_costrange_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_query_costrange_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_query_costrange_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_query_costrange_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_query_cost_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_query_cost_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_query_cost_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_query_cost_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_event_weight_helper_xml' and type = 'P') 
	drop procedure dbo.sp_DTA_event_weight_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_event_weight_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_event_weight_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_query_detail_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_query_detail_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_query_detail_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_query_detail_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_query_indexrelations_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_query_indexrelations_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_query_indexrelations_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_query_indexrelations_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_index_current_detail_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_index_current_detail_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_index_recommended_detail_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_index_recommended_detail_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_index_detail_current_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_index_detail_current_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_index_detail_recommended_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_index_detail_recommended_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_view_table_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_view_table_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_view_table_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_view_table_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_wkld_analysis_helper_xml' and type = 'P')
	drop procedure dbo.sp_DTA_wkld_analysis_helper_xml
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_wkld_analysis_helper_relational' and type = 'P')
	drop procedure dbo.sp_DTA_wkld_analysis_helper_relational
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_session_report' and type = 'P')
	drop procedure dbo.sp_DTA_get_session_report
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_set_tuninglogtablename' and type = 'P')
	drop procedure dbo.sp_DTA_set_tuninglogtablename
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_tuningoptions' and type = 'P')
	drop procedure dbo.sp_DTA_get_tuningoptions
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_interactivestatus' and type = 'P')
	drop procedure dbo.sp_DTA_get_interactivestatus
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_progressinformation' and type = 'P')
	drop procedure dbo.sp_DTA_insert_progressinformation
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_set_progressinformation' and type = 'P')
	drop procedure dbo.sp_DTA_set_progressinformation
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_set_outputinformation' and type = 'P')
	drop procedure dbo.sp_DTA_set_outputinformation
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_database' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_database
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_partitionscheme' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_partitionscheme
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_partitionfunction' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_partitionfunction
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_column' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_column
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_tableview' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_tableview
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_query' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_query
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_index' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_index
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_table' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_table
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_queryindex' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_queryindex
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_indexcolumn' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_indexcolumn
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_querytable' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_querytable
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_querydatabase' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_querydatabase
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_reports_querycolumn' and type = 'P')
	drop procedure dbo.sp_DTA_insert_reports_querycolumn
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_insert_DTA_tuninglog' and type = 'P')
	drop procedure dbo.sp_DTA_insert_DTA_tuninglog
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_databasetableids' and type = 'P')
	drop procedure dbo.sp_DTA_get_databasetableids
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_pftableids' and type = 'P')
	drop procedure dbo.sp_DTA_get_pftableids
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_pstableids' and type = 'P')
	drop procedure dbo.sp_DTA_get_pstableids
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_tableids' and type = 'P')
	drop procedure dbo.sp_DTA_get_tableids
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_columntableids' and type = 'P')
	drop procedure dbo.sp_DTA_get_columntableids
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_get_indexableids' and type = 'P')
	drop procedure dbo.sp_DTA_get_indexableids
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_update_tuninglog_errorfrequency' and type = 'P')
	drop procedure dbo.sp_DTA_update_tuninglog_errorfrequency
	
-- Drop unused SP's if they exist.		
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_columnaccesshelper' and type = 'P')
	drop procedure dbo.sp_DTA_columnaccesshelper
if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_databaseaccesshelper' and type = 'P')
	drop procedure dbo.sp_DTA_databaseaccesshelper
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_indexusagehelper' and type = 'P')
	drop procedure dbo.sp_DTA_indexusagehelper
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_queryCRhelper' and type = 'P')
	drop procedure dbo.sp_DTA_queryCRhelper
if exists (select name from msdb.dbo.sysobjects	where name = 'sp_DTA_tableaccesshelper' and type = 'P')
	drop procedure dbo.sp_DTA_tableaccesshelper
Modification Type:MinorLast Reviewed:11/1/2005
Keywords:kbScript kbhowto kbinfo KB899634 kbAudITPRO kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.