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/techinfo/tips/administration/autoclose.asp AutoClose Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-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.
