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 diagram support objects cannot... Attaching sql 2000 db to sql server 2005

Hello,

I've seen threads concerning this problem. I have followed the prescribed steps to fix the problem. I've set the db owner to sa, I've set compatibility to 2005. Still I can't get the diagram to function.

Can anyone out there help me?

Thanks, Bill

Hello Bill -

Can you describe your problem a little more? Are you saying that you have a 2K database that you've attached/restored/transported to 2K5 and you can't create a database diagram on it?

Thanks -

Buck Woody

Database diagram support objects cannot... Attaching sql 2000 db to sql server 2005

Hello,

I've seen threads concerning this problem. I have followed the prescribed steps to fix the problem. I've set the db owner to sa, I've set compatibility to 2005. Still I can't get the diagram to function.

Can anyone out there help me?

Thanks, Bill

Hello Bill -

Can you describe your problem a little more? Are you saying that you have a 2K database that you've attached/restored/transported to 2K5 and you can't create a database diagram on it?

Thanks -

Buck Woody

Database Crash

Hi All,
What are the steps of recovering SQL Server 2000 database after it crashes?
Is it documented somewhere?Depends on the crash. If the server was simply powered down, you can usually just power it on. If SQL Server will not start, then you have to go to the errorlog to see why. If a database will not recover....|||Let's say the database was accidentally deleted.|||Let's say the database was accidentally deleted.

Hope you have a backup...

You can...

...lookup up RESTORE in SQL BOL

...from enterprise manager, right click on the databases node, select all tasks and then 'Restore Database' (this assumes you had a backup to restore)

Regards,

hmscott|||Can we say there is a backup? Hypothetically, of course.|||these are my favorite posts. i can alsmost here the guy quivering and smell him sweating. I have only had one professional moment where I thought I lost everything. I thought I was going to die right there. But I put it back together.|||What about the database server loss? What are the steps then?|||Care to tell us what the hell is actually going on, or is it fun to watch us speculate?|||update resume
set experience = experience + ' Practiced disaster recovery'
where recent_job = (select max(recent_job) from resume)

As I said before, it depends on the crash. Still, the first step is going to be inventory. List out what you have (servers, backups, working software, etc.)|||could this be homework?|||No it is not a homework and no it is not fun to watch you speculate. I need to document the steps on how to recover from the database server loss. All I need for you is to tell me if there is a documentation that would help with this. That's all.|||inka,

Let's go back to your original question:
Hi All,

What are the steps of recovering SQL Server 2000 database after it crashes?
Is it documented somewhere?

That's not very much information to go on. What kind of crash? Where to begin depends on what has gone wrong. If you look up at the stickies, somewhere in one of them it will say something like, "the quality of the answer which you receive will depend greatly on the quality of the information that you provide."

In short, you have provided almost nothing.

Use google, it's your friend. You can find tons of information on recovery procedures (not all of it is right, but a lot of it is very good). You can buy a book (I like SAM's DBA survival guide). Read SQL BOL. Search some more. Set up a test instance and practice recovery on your own. There are lots of things that you can do to be better informed.

Don't get mad. Just do your homework before asking others to do it for you.

Regards,

hmscott|||Disaster recovery is a big subject. You can start by reading BOL.

Contents -->Administering SQL Server-->Backing up and Restoring Databases.

Once you have backups you should move them to tape and then you need to move your tapes offsite and there are company's that specialize in this.

Maybe you want Failove Clustering as well. Log shipping or even replication to attain maximum up time.|||Thank you.