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 locks on expanding databases folder

I'm having a very irritating situation occur with
Enterprise Manager.
I expand the databases folder after logging in to our
server and the application goes into 'not responding' mode.
There are about 200 databases to list, so I tried just
putting it in background... for four hours !!!!!
I killed the EM and tried this repeatedly, to no avail.
Does anyone know why this happens and what to do about it?Randy,
Are you on the latest service pack? Assuming its SQL2000, see
FIX: Opening the Database Folder in SQL Server Enterprise Manager 2000 Takes
a Long Time
http://support.microsoft.com/default.aspx?scid=kb;en-us;282416
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"randyv" <randy.volters@.whaleyfoodservice.com> wrote in message
news:061001c361d3$4bd83480$a101280a@.phx.gbl...
> I'm having a very irritating situation occur with
> Enterprise Manager.
> I expand the databases folder after logging in to our
> server and the application goes into 'not responding' mode.
> There are about 200 databases to list, so I tried just
> putting it in background... for four hours !!!!!
> I killed the EM and tried this repeatedly, to no avail.
> Does anyone know why this happens and what to do about it?|||> Does anyone know why this happens and what to do about it?
http://www.aspfaq.com/2469

Enterprise Manager Current Activity not displaying locks / process

I have a problem in Enterprise Manager and need some help.
When trying to identify possible locks via the locks/process ID screen under
the Current Activity option in the Management folder there are no items shown
at all.
At the same time there are around 500 active processes shown under the
process info screen which is in the same Current Activity area in the
Management folder.
I believe that some option has been switched off preventing me seeing the
entries on this screen, so I am looking for a way of switching it back on.
Any ideas would be gratefully appreciated
--
Steven FergusonSteven
All the info you can get from the current activity is a snapshot of activity
at the instant it checks. Even with 500 active processes, you are unlikely to
catch more than a handful at any one time. Processes page in and out very
quickly. If there is blocking occuring, you will see it this way. If you are
trying to get a handle on what locking is occuring, you will not get a very
good picture this way.
Use profiler if you want more details. Here is a link to a very good article
on using profiler.
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
Hope this helps
John
"fergusons" wrote:
> I have a problem in Enterprise Manager and need some help.
> When trying to identify possible locks via the locks/process ID screen under
> the Current Activity option in the Management folder there are no items shown
> at all.
> At the same time there are around 500 active processes shown under the
> process info screen which is in the same Current Activity area in the
> Management folder.
> I believe that some option has been switched off preventing me seeing the
> entries on this screen, so I am looking for a way of switching it back on.
> Any ideas would be gratefully appreciated
> --
> Steven Ferguson

Enterprise Manager Current Activity not displaying locks / process

I have a problem in Enterprise Manager and need some help.
When trying to identify possible locks via the locks/process ID screen under
the Current Activity option in the Management folder there are no items shown
at all.
At the same time there are around 500 active processes shown under the
process info screen which is in the same Current Activity area in the
Management folder.
I believe that some option has been switched off preventing me seeing the
entries on this screen, so I am looking for a way of switching it back on.
Any ideas would be gratefully appreciated
Steven Ferguson
Steven
All the info you can get from the current activity is a snapshot of activity
at the instant it checks. Even with 500 active processes, you are unlikely to
catch more than a handful at any one time. Processes page in and out very
quickly. If there is blocking occuring, you will see it this way. If you are
trying to get a handle on what locking is occuring, you will not get a very
good picture this way.
Use profiler if you want more details. Here is a link to a very good article
on using profiler.
http://www.sql-server-performance.co...filer_tips.asp
Hope this helps
John
"fergusons" wrote:

> I have a problem in Enterprise Manager and need some help.
> When trying to identify possible locks via the locks/process ID screen under
> the Current Activity option in the Management folder there are no items shown
> at all.
> At the same time there are around 500 active processes shown under the
> process info screen which is in the same Current Activity area in the
> Management folder.
> I believe that some option has been switched off preventing me seeing the
> entries on this screen, so I am looking for a way of switching it back on.
> Any ideas would be gratefully appreciated
> --
> Steven Ferguson

Enterprise Manager Current Activity not displaying locks / process

I have a problem in Enterprise Manager and need some help.
When trying to identify possible locks via the locks/process ID screen under
the Current Activity option in the Management folder there are no items show
n
at all.
At the same time there are around 500 active processes shown under the
process info screen which is in the same Current Activity area in the
Management folder.
I believe that some option has been switched off preventing me seeing the
entries on this screen, so I am looking for a way of switching it back on.
Any ideas would be gratefully appreciated
Steven FergusonSteven
All the info you can get from the current activity is a snapshot of activity
at the instant it checks. Even with 500 active processes, you are unlikely t
o
catch more than a handful at any one time. Processes page in and out very
quickly. If there is blocking occuring, you will see it this way. If you are
trying to get a handle on what locking is occuring, you will not get a very
good picture this way.
Use profiler if you want more details. Here is a link to a very good article
on using profiler.
http://www.sql-server-performance.c...ofiler_tips.asp
Hope this helps
John
"fergusons" wrote:

> I have a problem in Enterprise Manager and need some help.
> When trying to identify possible locks via the locks/process ID screen und
er
> the Current Activity option in the Management folder there are no items sh
own
> at all.
> At the same time there are around 500 active processes shown under the
> process info screen which is in the same Current Activity area in the
> Management folder.
> I believe that some option has been switched off preventing me seeing the
> entries on this screen, so I am looking for a way of switching it back on.
> Any ideas would be gratefully appreciated
> --
> Steven Ferguson