Enterprise Manager Hangs

Our company recently moved to a corporate Windows 2000 Network and AD
from Novell and all our servers were moved from our little domain. We
are seeing a wierd problem with SQLSever since our move. The sever that
has SQL Server is working fine and we are able to register the server in
Enterprise Manager. However, it sits and hangs when trying to browse
the Databases or Management nodes (all other nodes are ok). We can also
get to Query Analyzer and queries against any of the the database tables
work just fine .. UNLESS Query Analyzer tries to browse for the
databases (to populate that database dropdown or display the Object
browser) .. in that case, it sits and hangs.
We have installed Service Pack 4 and this did not resolve the issue.
The hotfix for RAM over 2 gig on the server does not apply in our case.
Has anyone experienced this problem before? We rebooted SQL Server
yesterday and after a couple of reboots, it worked (the Query Analyzer
issue remained), but we are now back to the same issue.
Any clues, suggestions, and of course fixes would be much appreciated!
Thank you!
Artie
*** Sent via Developersdex http://www.codecomments.com ***Can you try executing this sp from QA and tell us how much time it takes?
exec master.dbo.sp_MShasdbaccess
AMB
"Art" wrote:

> Our company recently moved to a corporate Windows 2000 Network and AD
> from Novell and all our servers were moved from our little domain. We
> are seeing a wierd problem with SQLSever since our move. The sever that
> has SQL Server is working fine and we are able to register the server in
> Enterprise Manager. However, it sits and hangs when trying to browse
> the Databases or Management nodes (all other nodes are ok). We can also
> get to Query Analyzer and queries against any of the the database tables
> work just fine .. UNLESS Query Analyzer tries to browse for the
> databases (to populate that database dropdown or display the Object
> browser) .. in that case, it sits and hangs.
> We have installed Service Pack 4 and this did not resolve the issue.
> The hotfix for RAM over 2 gig on the server does not apply in our case.
> Has anyone experienced this problem before? We rebooted SQL Server
> yesterday and after a couple of reboots, it worked (the Query Analyzer
> issue remained), but we are now back to the same issue.
> Any clues, suggestions, and of course fixes would be much appreciated!
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Alejandro,
"exec master.dbo.sp_MShasdbaccess" has been churning for 2 minutes now
with no result. Hope this means that the plot thickens! Thanks for
your help!
Artie
*** Sent via Developersdex http://www.codecomments.com ***|||" minutes seems to be a long time. Do you have many databases? Do you have a
utoclose turned on?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Art" <anonymousnospam@.devex.com> wrote in message news:uZrgpiHjFHA.1464@.TK2MSFTNGP14.phx.gb
l...
> Alejandro,
> "exec master.dbo.sp_MShasdbaccess" has been churning for 2 minutes now
> with no result. Hope this means that the plot thickens! Thanks for
> your help!
> Artie
> *** Sent via Developersdex http://www.codecomments.com ***|||We have about 20 databases. Thing is, we had absolutely no problems
until we moved to this new environment (Windows / AD) a few days ago -
Looks like this may be an access/permissions issue? I looked at the
autoclose option for a couple of the databases on the server and looks
like it is false (I used SELECT DATABASEPROPERTYEX('<DBNAME>',
'IsAutoClose')).
Thank you!
Artie
*** Sent via Developersdex http://www.codecomments.com ***|||Hi,
If the owner of one of your databases is an AD account, it may cause the
problem. Set "sa" as the owner of all of your DBs
Erdal
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGbVkrHjFHA.3316@.TK2MSFTNGP14.phx.gbl...
> " minutes seems to be a long time. Do you have many databases? Do you have
autoclose turned on?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Art" <anonymousnospam@.devex.com> wrote in message
news:uZrgpiHjFHA.1464@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]|||Normally, I would suspect ODBC tracing turned on at the client machine, but
the fact that the query
also ran slow using QA got me suspicious. Perhaps you can run that query fro
m QA again and use
Profiler to trace the statements inside that procedure to see which takes a
long time?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Art" <anonymousnospam@.devex.com> wrote in message news:%23KeceaIjFHA.3316@.TK2MSFTNGP14.phx.
gbl...
> We have about 20 databases. Thing is, we had absolutely no problems
> until we moved to this new environment (Windows / AD) a few days ago -
> Looks like this may be an access/permissions issue? I looked at the
> autoclose option for a couple of the databases on the server and looks
> like it is false (I used SELECT DATABASEPROPERTYEX('<DBNAME>',
> 'IsAutoClose')).
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.codecomments.com ***|||May be what Erdal is saying, is what is hurting the sp. Try executing the
only statement inside the sp [sp_mshasdbaccess] and you will get same
problem, but if you comment the line:
-- owner = substring(suser_sname(sid), 1, 24),
then everything will be normal.
Try reporting this to Microsoft.
AMB
"Art" wrote:

> We have about 20 databases. Thing is, we had absolutely no problems
> until we moved to this new environment (Windows / AD) a few days ago -
> Looks like this may be an access/permissions issue? I looked at the
> autoclose option for a couple of the databases on the server and looks
> like it is false (I used SELECT DATABASEPROPERTYEX('<DBNAME>',
> 'IsAutoClose')).
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Tibor
sp_MsHasDbAccess does not return any result at all .. it simply hangs.
So when I ran profiler while trying to execute it, it shows a bunch of
stuff under my login name and then sits on sp_MsHasDbAccess. (btw, some
of the statements before that was select IS_SRVROLEMEMBER ('sysadmin'),
which returns a 1; SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) correctly
returns my login name).
Thanks!
Aarti
*** Sent via Developersdex http://www.codecomments.com ***|||I was referring to tracing the TSQL code *inside* MsHasDbAccess. You need to
capture the right
events for this. See Alejandro's most recent post in this thread.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Art" <anonymousnospam@.devex.com> wrote in message news:%23gP7y1IjFHA.1412@.TK2MSFTNGP09.phx.
gbl...
> Tibor
> sp_MsHasDbAccess does not return any result at all .. it simply hangs.
> So when I ran profiler while trying to execute it, it shows a bunch of
> stuff under my login name and then sits on sp_MsHasDbAccess. (btw, some
> of the statements before that was select IS_SRVROLEMEMBER ('sysadmin'),
> which returns a 1; SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) correctly
> returns my login name).
> Thanks!
> Aarti
>
> *** Sent via Developersdex http://www.codecomments.com ***
>