Enterprise manager - databases - "No items"

Hi all,
I'm using Enterprise Manager to connect to several remote SQL Server
Databases (in different locations).
It works fine for all servers except the one that shows "No items" when I
click on "databases". After "refresh" it successfully receives list of
databases but after a few hours...
When I right click on databases and select "Export data" then I can select
my database from the list but when I specify "Copy objects and data between
SQL server databases" I receive somthing like :
"User 'aaa' is not valid user in database
'some_database_but_different_then_I_have_selected' ".
When I try to create new DTS Package that should "copy SQL Server objects
task" I receive similar error.
I can work with Query Analyzer but don't know how to make backup copy of the
database to my local database since I have only db_owner rights (I'm not
member of sysadmin server role).
I have send the following article
http://support.microsoft.com/default...b;en-us;315523 to my
database povider but he says they have everything set properly...
Can anybody help me, please?
Thank You
George
Hi George,
From your description, I understand that, as a remote client, your SQL
Server Enterprise Manager would occasionally not display the your database.
When refreshing it, the database would display but in a long time. Have I
fully understood you? If there is anything I misunderstood, please feel
free to let me know.
Based on my understanding, the Server has a significant number of databases
and a few of them were marked as autoclose. This meant when user connected
using SQL Server Enterprise Manager, the database would need to be started
before the queries to enumerate tables and data could be run. This delay
was causing the queries to fail and resulted in the databases to not be
displayed. Refreshing allowed the databases to be started while the query
was resubmitted.
To resolve the issue, you should contact your database provider to remove
the autoclose settings form all of your databases. He could do in this way
EXEC sp_MSforeachdb @.command1="sp_dboption '?','autoclose',false"
Autoclose option is not recommended for production systems since it can be
a significant performance impact.
It the above doesn't resovle the issue, You shoud have a look at the size
of tempdb. You could try using one of the methods documented in the
following article to shrink tempdb back to it's original size
307487 HOW TO: Shrink the Tempdb Database in SQL Server
http://support.microsoft.com/?id=307487
Would you please tell me that how large is tempdb currently, and how large
was it when SQL Server Enterprise Manager experienced the failure to
enumerate the databases? Was the autogrowth option turned off for tempdb
previously?
If the above two doesn't resolve your issue, would you please run the
following queries in your Query Analyzer when the problem happens and show
me the results?
select name, DATABASEPROPERTY(name, N'IsDetached'),(case when
DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else
DATABASEPROPERTY(name, N'IsShutdown') end), DATABASEPROPERTY(name,
N'IsSuspect'),
DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'),
(case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1 else
DATABASEPROPERTY(name, N'IsInRecovery') end), (case when
DATABASEPROPERTY(name,
N'IsNotRecovered') is null then -1 else
DATABASEPROPERTY(name, N'IsNotRecovered') end), DATABASEPROPERTY(name,
N'IsEmergencyMode'),
DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status,
category,
status2
from master.dbo.sysdatabases
Moreover, detailed information about autoclose option of database could be
found at
Avoiding the Autoclose and Autoshrink Options
http://www.microsoft.com/sql/techinf.../autoclose.asp
AutoClose Property
http://msdn.microsoft.com/library/de...us/sqldmo/dmor
ef_p_a_5cl5.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Hi George,
I wanted to post a quick note to see whether you have apply my suggestion
and your Enterprise manager works fine. If you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Hi Michael,
sorry for delay. I have send Your information to my database provider. He
has promissed that he checks it in few days. Now I have tried to connect to
my database and IT WORKS!!! I can see databases, I can work with my
database, I have succesfully run my DTS packages too and everything is
without delay.
Thank You very much for Your help!!!
Best regards
George
""Michael, Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:z3vX9N2KEHA.3088@.cpmsftngxa10.phx.gbl...
> Hi George,
> I wanted to post a quick note to see whether you have apply my suggestion
> and your Enterprise manager works fine. If you would like additional
> assistance or information regarding this particular issue. We appreciate
> your patience and look forward to hearing from you!
> Sincerely yours,
> Michael Cheng
> Microsoft Online Support
> ************************************************** *********
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks.
>