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

database copy wizard HELP

I have been using SQL Server 2000 but not am not an expert on it. I recently
copied databases into a new server using Database Copy Wizard. The previous
databases were copied with no problems. This problem occured when I selected
5 databases at the same time although I doubt the # of databases had anything
to do with the problem. It still copied into the new server but the
databases on the "source" server disappeared! I tried "re-copying" same
databases back into the source server but I am getting a 'name conflict'
error. From my research, this meant the mdf and ldf files are still there.
Note that I "copied" and NOT "moved" these databases. Can you please tell me
how I can restore these databases? I also tried re-creating the missing
database so I can export data back into it but I got "Error 5170: Cannot
create file 'e:\data\MSSQL\...\.MDF' because it already exists. etc" Thank
you for helping.
--
_________________
Freedom is nothing else but a chance to be better. (Albert Camus)Hi
Right click on databases in Enterprise Manager, All Tasks,
Attach. Browse for the *.mdf. If it's there, it will re-
attach it. The DB copy wizard detaches the DB, copies it
and then reattaches it. You said you selected Copy and not
Move so I guess something went wrong and it didn't re-
attach it as a final step.
Regards
Jono|||You are correct. Thank you very much!
"Jono" wrote:
> Hi
> Right click on databases in Enterprise Manager, All Tasks,
> Attach. Browse for the *.mdf. If it's there, it will re-
> attach it. The DB copy wizard detaches the DB, copies it
> and then reattaches it. You said you selected Copy and not
> Move so I guess something went wrong and it didn't re-
> attach it as a final step.
> Regards
> Jono
>