Prev: Server could not load dcom
Next: Long running Job - SQL Server Agent
From: bkp_42 on 10 Jan 2007 09:08

With regard to the "Database Engine Tuning Wizard: "Failed to initialize
MSDB database for tuning (exit code: -1). (DTAClient)" error, I just ran
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 was
happening when firing up the Database Tuning Advisor, and found that it
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 I
scripted them from the server that was working. Once I created the
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_42
------------------------------------------------------------------------
bkp_42's Profile: http://unixadmintalk.com/773
View this thread: http://unixadmintalk.com/showthread.php?t=185258

 | 
Pages: 1
Prev: Server could not load dcom
Next: Long running Job - SQL Server Agent