Database Engine Tuning Wizard: "Failed to initialize MSDB database for tuning (exit code:

Error trying to tune a database:
Database Engine Tuning Wizard: "Failed to initialize MSDB database for
tuning (exit code: -1). (DTAClient)
Steps to Reproduce the Problem
1. Launch Database Engine Tuning Advisor
2. Enter connection info
Server Name: <The Server dns name>
Authentication: SQL Server Authentication
Login: sa
Password: <the sa password>
3. Click "Connect"
Notes:
a. The target server has an MSDB database
b. The server is Windows 2000
c. The server is SQL Server 2000
d. The sa login can connect and see the msdb database
e. My desktop machine has no application compatibility flags set for
explorer.exe
Full error log:
===================================
Failed to open a new connection.
===================================
Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)
--
Program Location:
at
Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at
Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo
connectionInfo, SqlConnection connection)
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo
connectionInfo, SqlConnection connection)
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object
sender, EventArgs e)
===================================
Failed to establish a connection to the server.With regard to the "Database Engine Tuning Wizard: "Failed to initializ
MSDB database for tuning (exit code: -1). (DTAClient)" error, I just ra
into this myself with a fresh install of MS SQL Server 2005 + SP1.
I ran SQL Profiler on another server, that was working, to see what wa
happening when firing up the Database Tuning Advisor, and found that i
was looking for the following in the MSDB database:
Tables:
'DTA_input',
'DTA_progress',
'DTA_output',
'DTA_tuninglog',
'DTA_reports_database',
'DTA_reports_partitionfunction',
'DTA_reports_partitionscheme',
'DTA_reports_table',
'DTA_reports_tableview',
'DTA_reports_query',
'DTA_reports_querytable',
'DTA_reports_querydatabase',
'DTA_reports_index',
'DTA_reports_queryindex',
'DTA_reports_column',
'DTA_reports_indexcolumn',
'DTA_reports_querycolumn'
Stored Procedures:
'sp_DTA_add_session',
'sp_DTA_delete_session',
'sp_DTA_help_session',
'sp_DTA_update_session',
'sp_DTA_get_tuninglog',
'sp_DTA_update_session',
'sp_DTA_get_session_report',
'sp_DTA_get_tuninglog',
'sp_DTA_get_session_tuning_results',
'sp_DTA_set_interactivestatus'
These objects were missing from the server that was failing, so
scripted them from the server that was working. Once I created th
objects in the MSDB database, Database Tuning Advisor now works.
I've attached a zip file with the SQL scripts
+---
|Filename: DTA_Objects.zip
|Download: http://unixadmintalk.com/attachment.php?attachmentid=2
+---
--
bkp_4
----
bkp_42's Profile: http://unixadmintalk.com/77
View this thread: http://unixadmintalk.com/showthread.php?t=18525sql