I had the same problem some time ago with a hosting provider. SQL Server
2000 Enterprise Manager is enlisting all objects before you can access them.
In SQL Server Management Studio 2005 it has been improved so you can start
navigating while other objects are still retrieving.

While I could not find a way to limit the list to only my database, I did
the following:

- In SQL Server 2000 Enterprise Manager, under Tools menu, Options, under
the Advanced tab selected "Open the console tree to the last active node".
Then if you make sure each time to have your database selected on next start
Enterprise Manager will open and directly select your database. Note that
this will not shorted the time to open, it will just save you the trouble
looking through ~500 databases to find yours (and accidentally clicking on
the wrong database can delay you further).
- I started using Query Analyzer for all tasks. When connecting your user id
is mapped to your database by default and the connection is really fast. And
scripting is the better option in my opinion to do most changes.

Hope this helps.

Regards,

Plamen Ratchev
http://www.SQLStudio.com


[quoted text, click to view]

[quoted text, click to view]

Consider using Query Analyzer or Management Studio.

[quoted text, click to view]

EM is far from optimized for this kind of thing, and on top of that, your
host may have some databases set to auto-close, which may affect the amount
of time it takes to display the list.

[quoted text, click to view]

Well, what does "working with it" mean? Since most things can be
accomplished through a query window, I would wager that you can usually get
away with not expanding a database list at all. I rarely do it myself, and
one of the systems I manage has over 500 databases and they're all mine.
Typing "USE dbname" and hitting F5 is a much more gratifying experience than
expanding a graphical list (the same reason I rarely navigate to a database
using a drop-down).

A

FYI: my sincere apologizes if this group is not the right place for my
questions.

Hello,

1. My hosting provider hosts ~500 databases on the server where my DB
resides. To open my database I use SQL Server 2000 Enterprise Manager.

While server connection timeout is short enough (about 1-2 seconds), when I
click "Databases" folder in server hierarchy, trying to enlist all
databases, it takes and extremely long period of time to set up a list.
Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
and its process could be terminated only by Task Manager.

My first question is: why does EM take such a timeout to display a list of
databases and why in most cases it halts?

2. To open my database and to start working with it I each time have to
perform the above steps. However I find it rather silly - to enlist all
databases first and ONLY THEN select mine. Logical approach to the process
of database connection suggests NOT to enlist all databases (I really need
only one!), but configure server (client?) that way that upon connecting to
server it automatically defaults to my DB.

My second question is:
Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
not to enlist all databases upon connecting to server, but switch directly
to mine by default?

Thank you in advance.
Vsevolod.

Vsevolod Ukrainsky (vsevolod@ukrainsky.ru) writes:
[quoted text, click to view]

It could be that your web host has set the databases to autoclose. EM
queries all databases to see which you have permission to. The fact that
you do this over a slow network connection may be a problem in itself.
But autoclose is the real killer.

[quoted text, click to view]

Yes, it's silly, but you need to realise that Enterprise Manager which
was designed in 1997/98 was not designed with customers at web hosts
in mind. That was something quite unheard of at the time. Instead the
target user was a sysadmin on a local network which normally would
work over all databases.

I can give you two suggestions for alternate tools:

1) Use Query Analyzer that comes with SQL 2000. From QA you can run
queries, and you can create stored procedures and the lot. There is
no query designer, table designer or database diagrams. On the other
hand, all these tools are of dubious value with several flaws. What
may be a bigger drawback is that you don't have the DTS designer,
or a GUI to adminster Agent jobs if you need to that. But by using
QA, you can reduce how much time you need to use Enterprise Manager.

2) SQL Server Management Studio Express. Mgmt Studio is the follower to
both EM and QA in SQL 2005. Express is the free version of this tool,
and you can get it at
http://msdn.microsoft.com/vstudio/express/sql/download/. Mgmt Studio
Express has mnay of the features in EM, although not all. I don't
think it has any support for Agent, as SQL Express does not come with
Agent. Also, I don't know how well it works when working with a web
host, but since it's free, you could try it.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Thank you so much for your help guys!

[quoted text, click to view]

<SKIPPED>