Hanging or lockup issue with SQL Server and Terminal Services 2000 on NT 4 Domain Server

[ACP]

Hi all,

Have a situation that my company has never run across before. Client
is running NT4 for the domain server, using terminal services 2000 and
running an application with a SQL Server backend and they are
experiencing locking problems. Once one person gets locked out then
everyone trying to access that tables is also locked out as a result.
It is not specific to a certain User, or module within the
application. It's not a specific time of the day (like when a backup
would be running) and sometimes it's in the middle of the night when
there are actually less Users on the system.

We have 500 customers using this application. Most are using SQL
Server backend, alot of the newer customers are using Terminal
Services, and the number of Users is not accessive as compared to our
other customers. THe only difference is that I do not specifically
know of another client with an NT4 Domain server in the mix.

We actually switched to SQL Server as the recommended back end due to
locking issues using SQLBase because SQL Server is row locking and
SQLBase is page locking. Since making this change we have stopped
seeing the locking for years until now. Is this a SQLServer issue or
issue with the NT Domain server?

Anyone have any ideas???

Thanks
A

[Simon Hayes]

"ACP" <Ak*******@aol.com> wrote in message
news:f8**************************@posting.google.c om...

Hi all,

Have a situation that my company has never run across before. Client
is running NT4 for the domain server, using terminal services 2000 and
running an application with a SQL Server backend and they are
experiencing locking problems. Once one person gets locked out then
everyone trying to access that tables is also locked out as a result.
It is not specific to a certain User, or module within the
application. It's not a specific time of the day (like when a backup
would be running) and sometimes it's in the middle of the night when
there are actually less Users on the system.

We have 500 customers using this application. Most are using SQL
Server backend, alot of the newer customers are using Terminal
Services, and the number of Users is not accessive as compared to our
other customers. THe only difference is that I do not specifically
know of another client with an NT4 Domain server in the mix.

We actually switched to SQL Server as the recommended back end due to
locking issues using SQLBase because SQL Server is row locking and
SQLBase is page locking. Since making this change we have stopped
seeing the locking for years until now. Is this a SQLServer issue or
issue with the NT Domain server?

Anyone have any ideas???

Thanks
A

It's hard to say what's going on without more information - what version of
MSSQL do you have and which OS is it running on? What is the application and
how does it connect to MSSQL (ODBC, OLE DB)? Also, since it used to work
fine, has anything changed recently such as a new servicepack installation?

As for the locking itself, what do you mean that someone is "locked out"? Do
you mean their connection times out, that they're the victim of a deadlock,
or something else? Have you checked what sp_who2 and sp_lock say about which
connections are blocked and what objects are locked by the blocking
connection(s)? Is there anything unusual in the MSSQL log at the time the
problem happens?

It sounds like one connection locks a table, then doesn't release it
(perhaps an uncommitted transaction - you can check with DBCC OPENTRAN), but
you will have to look into what the connection is actually doing when the
issue occurs.

Simon

[ACP]

It's definitely a lock on the SQL Server database. The problem is it
is across the board as far as what Users are doing when it happens.
It is grabbing a lock on a table and then locking others out in a
chain reaction. But it's difficult to pinpoint since it's happening
to Users performing different transactions and hitting different
tables in the database. So I'll check using sp_lock and try and
narrow it down a little more. And then I'll look into the option of
using DBCC OPENTRAN if I can narrow it down.

Thanks for the help

[Ryan]

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

This may be of some help to you.

Ak*******@aol.com (ACP) wrote in message news:<f8*************************@posting.google.c om>...

It's definitely a lock on the SQL Server database. The problem is it
is across the board as far as what Users are doing when it happens.
It is grabbing a lock on a table and then locking others out in a
chain reaction. But it's difficult to pinpoint since it's happening
to Users performing different transactions and hitting different
tables in the database. So I'll check using sp_lock and try and
narrow it down a little more. And then I'll look into the option of
using DBCC OPENTRAN if I can narrow it down.

Thanks for the help