Enterprise Manager Locks Table

We have just moved a SQL Server database to a new server and since doing
this I have found that a lock is placed on a table whenever it is being
viewed in Enterprise Manager until the table is closed. This has the effect
of stopping our main program from updating the table whislt it is open in
Enterprise Manager. If the table is viewed by including WITH(NOLOCK) in the
select statment then our main program works okay. If the table is viewed
using SQL Anaylser then the table lock does not remain even with the result
set still open. I am not sure if this happens on all tables but the table in
question contains 125,000 rows.
I was not aware of this behavour before (when the database was on another
server) is this normal and is there a way of forcing Enterprise Manager to
include WITH(NOLOCK) by default or is something strange happening?
Thanks
Phil...
Make sure you have proper indexes on the table. Using EM to view data has
never been a recommended way. I have seen the behavior plenty of times when
clients use EM to view the data. Using QA and running regular select
statements is the best way to do that.
Andrew J. Kelly SQL MVP
"Phil Jenson" <phil@.jenson.co.uk.nospam> wrote in message
news:OlyPbPtXFHA.796@.TK2MSFTNGP09.phx.gbl...
> We have just moved a SQL Server database to a new server and since doing
> this I have found that a lock is placed on a table whenever it is being
> viewed in Enterprise Manager until the table is closed. This has the
> effect of stopping our main program from updating the table whislt it is
> open in Enterprise Manager. If the table is viewed by including
> WITH(NOLOCK) in the select statment then our main program works okay. If
> the table is viewed using SQL Anaylser then the table lock does not remain
> even with the result set still open. I am not sure if this happens on all
> tables but the table in question contains 125,000 rows.
> I was not aware of this behavour before (when the database was on another
> server) is this normal and is there a way of forcing Enterprise Manager to
> include WITH(NOLOCK) by default or is something strange happening?
>
> Thanks
> Phil...
>
|||Thanks Andrew.
At least you have confirmed what I susspected. Yes we will be sticking to QA
from know on.
Phil...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23a2iiCuXFHA.612@.TK2MSFTNGP12.phx.gbl...
> Make sure you have proper indexes on the table. Using EM to view data has
> never been a recommended way. I have seen the behavior plenty of times
> when clients use EM to view the data. Using QA and running regular select
> statements is the best way to do that.
> --
> Andrew J. Kelly SQL MVP
>
> "Phil Jenson" <phil@.jenson.co.uk.nospam> wrote in message
> news:OlyPbPtXFHA.796@.TK2MSFTNGP09.phx.gbl...
>
|||this is exactly why many I.T. organizations do NOT install EM on client
workstations.
I've ran into this problem at various work places.
Another Nemesis is MS Access. You'll have the "Power End Users" of your
organization create Linked Tables from Access to your source SQL data and
get all kinds of contention issues.
Cheers,
Greg Jackson
PDX, Oregon
|||Phil Jenson wrote:
> Thanks Andrew.
> At least you have confirmed what I susspected. Yes we will be
> sticking to QA from know on.
> Phil...
>
SQL EM does not fetch all results at once. It fetches on a as-needed
basis, leaving locks on the server. SQL EM is not recommended for this
type of activity, especially in production. You can verify this behavior
from Profiler.
David Gugick
Imceda Software
www.imceda.com

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 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...
> 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 from 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 ***
>

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 ***
>

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.developersdex.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.developersdex.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.developersdex.com ***
>

Enterprise manager

hi,
I used backup and restore moved a database from one server to another.
Everything works fine. But I got problem view the taskpad of the database. I
could view it from another server but not this server.
right after restore I could view it but after close EM and reopen , I got
this error when trying to view the taskpad of the database:
Internet Explorer script error:
An error has occurred in the script on this page:
Line: 307
Char: 2
Error: unspecified error
Code: 0
URL:
res://c:\Program%20Files\Microsoft%20SQL%20server\80\Tools\Binn\Resources\10
33\sqlmmc.rll/Tabs.html
Could you please help!!
Thanks
Kathy ChenHi Kathy.
Hopefully someone will give you a more precise answer to the actual problem
you're experiencing, but my 20c worth on this is that you probably shouldn't
become too dependant on the MMC SnapIn TaskPads. If you search the archive
of this newsgroup you'll find a trail of destruction from others trying to
use the TaskPad views - they simply don't work very reliably. There's lots
more on this in microsoft.public.management.mmc as well.
Another thing that often fails with Task Views is in the "Table Info" tab -
this view often won't let you scroll to the next page - often the next page
nav tools are missing. It's a really nice feature, but it just doesn't work
reliably, imho. My work around to that problem was simply to use profiler to
grab what the EM was sending to the server, saved it as a script & you get
the same information.. (c:
Regards,
Greg Linwood
SQL Server MVP
"Kathy" <kathy_chen@.hotmail.com> wrote in message
news:e8SWyCEyDHA.536@.tk2msftngp13.phx.gbl...
> hi,
> I used backup and restore moved a database from one server to another.
> Everything works fine. But I got problem view the taskpad of the database.
I
> could view it from another server but not this server.
> right after restore I could view it but after close EM and reopen , I got
> this error when trying to view the taskpad of the database:
>
> Internet Explorer script error:
> An error has occurred in the script on this page:
> Line: 307
> Char: 2
> Error: unspecified error
> Code: 0
> URL:
>
res://c:\Program%20Files\Microsoft%20SQL%20server\80\Tools\Binn\Resources\10
> 33\sqlmmc.rll/Tabs.html
> Could you please help!!
> Thanks
> Kathy Chen
>
>|||Hi Kathy,
There's a very simple workaround for the scripting error you'r
encountering in Enterprise Manager.
Just right-click on the DB, click view, then click list to display th
DB objects. Go back, right-click on the DB, click view, then clic
Taskpad. It will reset the web interface and it'll work again.
Yes, another stupid bug in MSSQL SQL Server.
Best Regards,
Chuck Lee
chucksql@.hotmail.co
-
chuckle
----
Posted via http://www.mcse.m
----
View this thread: http://www.mcse.ms/message205074.htm