PLEASE HELP !>> 2145 Locks, displayed from SP_LOCK 143

HI, Can someone PLEASE HELP ME to better understand what is happening in the
SQL2000 SP4 database when someone runs a posting process that creates 2145
locks. There are about 300 users connected to this database, entering
transactions all day and some through the night, and most of the time
everything runs along smoothly, I've seen 300 - 600 locks on normal OPs, but
when a posting is run (I know little about that actual application) it
creates thousands of locks and transactions grind to a halt. I've been
monitoring the locks while writing this and that one SPID has a death grip
on 2145 locks, this has been for at least 15 minutes. I've been the SLQ DBA
here for just under a year, and this is one of many issues that needs
attention. IS THIS AN APPLICATION THING, IS THIS SOMETHING THAT I NEED TO
CHANGE ON THE SQL CONFIGURATION, >>> WHAT CAN I DO? IS THIS AN
ENGINEERING QUESTION?
Check the Query from that particular SPID (user) via Profiler.
to immideatly resolve the issue: kill the SPId that is Blocking:
(Carefully examine that this particular SPID for other blocking
scenarios)
Read more about NOLOCK and ISOLATION LEVELS.
Get the developers to use this often if there are heavy transactions,
specially for "select" stuff.
Get a nolock on all SPs that this application or the Posting Process
uses.
More about blocking in BOL:
Maninder
MCDBA
|||THANK YOU!! It took about 20 minutes for this process to complete, and then
all locks cleared. The Developers are another Company and discussing how
there app works, is not easy. Would you have any ideas as to why a process
that effects maybe 15,000 entries, would lock 2145 records (am I correct to
assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
that process?) and takes 20 minutes.
Server is Win2003 x64, 4g Ram, Xeon 3.0 GHz Dual Proc, with HyperThreading
On(shows 4 Proc's in TaskManager), Dual gigabit NICs, SQL2000 SP4.
==========================================
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1170345674.343261.97400@.a75g2000cwd.googlegro ups.com...
> Check the Query from that particular SPID (user) via Profiler.
> to immideatly resolve the issue: kill the SPId that is Blocking:
> (Carefully examine that this particular SPID for other blocking
> scenarios)
> Read more about NOLOCK and ISOLATION LEVELS.
> Get the developers to use this often if there are heavy transactions,
> specially for "select" stuff.
> Get a nolock on all SPs that this application or the Posting Process
> uses.
> More about blocking in BOL:
> Maninder
> MCDBA
>
|||WANNABE wrote:
> THANK YOU!! It took about 20 minutes for this process to complete, and then
> all locks cleared. The Developers are another Company and discussing how
> there app works, is not easy. Would you have any ideas as to why a process
> that effects maybe 15,000 entries, would lock 2145 records (am I correct to
> assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
> that process?) and takes 20 minutes.
>
No, it shows you the number of LOCKS that the process has in place. You
need to look at the lock type to determine if they are rows, pages,
tables, etc...
My guess is that there are missing or inadequate indexes on some of the
tables involved in this posting process, which result in table locks. I
would pursue this before going with NOLOCK. Using NOLOCK is an
often-abused band-aid for poorly designed systems. It exposes you to
possible reading "dirty" data, and should be used carefully.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

PLEASE HELP !>> 2145 Locks, displayed from SP_LOCK 143

HI, Can someone PLEASE HELP ME to better understand what is happening in the
SQL2000 SP4 database when someone runs a posting process that creates 2145
locks. There are about 300 users connected to this database, entering
transactions all day and some through the night, and most of the time
everything runs along smoothly, I've seen 300 - 600 locks on normal OPs, but
when a posting is run (I know little about that actual application) it
creates thousands of locks and transactions grind to a halt. I've been
monitoring the locks while writing this and that one SPID has a death grip
on 2145 locks, this has been for at least 15 minutes. I've been the SLQ DBA
here for just under a year, and this is one of many issues that needs
attention. IS THIS AN APPLICATION THING, IS THIS SOMETHING THAT I NEED TO
CHANGE ON THE SQL CONFIGURATION, >> WHAT CAN I DO? IS THIS AN
ENGINEERING QUESTION'Check the Query from that particular SPID (user) via Profiler.
to immideatly resolve the issue: kill the SPId that is Blocking:
(Carefully examine that this particular SPID for other blocking
scenarios)
Read more about NOLOCK and ISOLATION LEVELS.
Get the developers to use this often if there are heavy transactions,
specially for "select" stuff.
Get a nolock on all SPs that this application or the Posting Process
uses.
More about blocking in BOL:
Maninder
MCDBA|||THANK YOU!! It took about 20 minutes for this process to complete, and then
all locks cleared. The Developers are another Company and discussing how
there app works, is not easy. Would you have any ideas as to why a process
that effects maybe 15,000 entries, would lock 2145 records (am I correct to
assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
that process') and takes 20 minutes.
Server is Win2003 x64, 4g Ram, Xeon 3.0 GHz Dual Proc, with HyperThreading
On(shows 4 Proc's in TaskManager), Dual gigabit NICs, SQL2000 SP4.
=========================================="Maninder" <msdhanjal@.gmail.com> wrote in message
news:1170345674.343261.97400@.a75g2000cwd.googlegroups.com...
> Check the Query from that particular SPID (user) via Profiler.
> to immideatly resolve the issue: kill the SPId that is Blocking:
> (Carefully examine that this particular SPID for other blocking
> scenarios)
> Read more about NOLOCK and ISOLATION LEVELS.
> Get the developers to use this often if there are heavy transactions,
> specially for "select" stuff.
> Get a nolock on all SPs that this application or the Posting Process
> uses.
> More about blocking in BOL:
> Maninder
> MCDBA
>|||WANNABE wrote:
> THANK YOU!! It took about 20 minutes for this process to complete, and then
> all locks cleared. The Developers are another Company and discussing how
> there app works, is not easy. Would you have any ideas as to why a process
> that effects maybe 15,000 entries, would lock 2145 records (am I correct to
> assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
> that process') and takes 20 minutes.
>
No, it shows you the number of LOCKS that the process has in place. You
need to look at the lock type to determine if they are rows, pages,
tables, etc...
My guess is that there are missing or inadequate indexes on some of the
tables involved in this posting process, which result in table locks. I
would pursue this before going with NOLOCK. Using NOLOCK is an
often-abused band-aid for poorly designed systems. It exposes you to
possible reading "dirty" data, and should be used carefully.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Plase HELP ! SQL ERROR =>> An I/O error occurred while receiving the TDS prelogin response

i get this error when i try to connect to sql server 2005 developer edition with the latest JDBC.

sql string =>> jdbc:sqlserver://localhost:1434;databaseName=c1 Elvis elvis
SQL STATE: 08S01
ERROR CODE: 0
MESSAGE: An I/O error occurred while receiving the TDS prelogin response.

And the stacktrace is:

com.microsoft.sqlserver.jdbc.SQLServerException: An I/O error occurred while receiving the TDS prelogin response.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.Prelogin(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)

and is called :connection=DriverManager.getConnection(con, user, password);

Please tell me what is wrong ? Because in other part of the code it works fine !

What is this "I/O error occurred while receiving the TDS prelogin response" ?

There is a limitation about the number of simultaneous connections to the database in the SQL Server 2005 Developer Edition DVD ?

Can be that error because of that ? What is TDS prelogin response ? Any ideea Microsoft guys ? Pls ...

|||I'm also getting the same error! Did you ever find anything out, Elvis? Somebody please respond. Anything will help.

Thanks,|||Hi all,

I fixed it! Well I don't know if this fixes it, but it's working now by some intervention. I changed the url from 127.0.0.1 to my intranet ip 172.22.x.x (not my external 64.72.x.x). I then telneted 172.22.x.x port 1434 (which works for 127.0.0.1) and it didn't work. Then I tried 172.22.x.x 1433 and it did (even though the server logs say it's listening on 1434 like 127.0.0.1). Again, this may just be a coincedense, but at least I'm up and running now.

Thanks,|||

Yes, the error message could stand to be more useful. I will make sure we address that.

But it looks like you've solved the problem though. By default, SQL Server listens for TCP connections on port 1433. Port 1434 is normally used by SQL Browser to respond to UDP queries to map a SQL Server instance name to IP address & port number.

Thank you for your feedback,

--David Olix

JDBC Development

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've foud the problem the day before but you in an increddible time of 7 moths.

You are the best, with "your messages" and "increddible fast work" :))

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've found the problem the day after i've posted the problem, but you in an increddible time of 7 months.

You are the best, with "your messages" and "increddible fast work" :))

|||

ProgrammerJ,

I sincerely apologize for not responding to your question sooner. We do try our best to respond to all questions posted on this forum within two business days. In this case we did not. Indeed, the only reason I saw your post at all is that someone else followed up to it with a post about the same problem, which brought the thread back to the top of the list. However, I hope this has not dissuaded you from using the Microsoft SQL Server 2005 JDBC driver.

Again, my apologies, and thank you for your feedback,

--David Olix

JDBC Development

|||http://msdn.microsoft.com/newsgroups/default.aspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.jdbcdriver&p=1&tid=f0f1c794-a805-4370-aca7-4f633c460104&mid=f0f1c794-a805-4370-aca7-4f633c460104

I have posted a similar problem on the jdbc discussion newsgroup. We are using the production 1.1 version of the JDBC driver. To repeat that post:

The stack trace is

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown
Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.flush(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.endRequest(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown
Source)

This is happening during a performance stress test, and only happens very
sporadically: two or three times during a multi-hour test of several
simultaneous users pounding on the database. There are no events on the DB
server, and nothing in the SQL Server 2005 log. The Tomcat server is running
on Linux. We are using a commons-DBCP connection pool that validates the
connection on borrow with "select 1", so we know the connection is good when
we get it for the pool. The network error happens right after a prepare
statement, so the connection is working, and then breaks on the execute of
the prepared statement. The query is not a long running query. We've also
checked the network health and it looks fine. We start some background batch
jobs against the server before the errors start to occur.

We are pretty much out of ideas. There are some relevant threads we can see
by Googling this, but neither IE nor Firefox can get the msdn.forums,
complaining the URL will never complete or something, which can be caused by
not accepting cookies, but that's not the problem.

Plase HELP ! SQL ERROR =>> An I/O error occurred while receiving the TDS prelogin response

i get this error when i try to connect to sql server 2005 developer edition with the latest JDBC.

sql string =>> jdbc:sqlserver://localhost:1434;databaseName=c1 Elvis elvis
SQL STATE: 08S01
ERROR CODE: 0
MESSAGE: An I/O error occurred while receiving the TDS prelogin response.

And the stacktrace is:

com.microsoft.sqlserver.jdbc.SQLServerException: An I/O error occurred while receiving the TDS prelogin response.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.Prelogin(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)

and is called :connection=DriverManager.getConnection(con, user, password);

Please tell me what is wrong ? Because in other part of the code it works fine !

What is this "I/O error occurred while receiving the TDS prelogin response" ?

There is a limitation about the number of simultaneous connections to the database in the SQL Server 2005 Developer Edition DVD ?

Can be that error because of that ? What is TDS prelogin response ? Any ideea Microsoft guys ? Pls ...

|||I'm also getting the same error! Did you ever find anything out, Elvis? Somebody please respond. Anything will help.

Thanks,|||Hi all,

I fixed it! Well I don't know if this fixes it, but it's working now by some intervention. I changed the url from 127.0.0.1 to my intranet ip 172.22.x.x (not my external 64.72.x.x). I then telneted 172.22.x.x port 1434 (which works for 127.0.0.1) and it didn't work. Then I tried 172.22.x.x 1433 and it did (even though the server logs say it's listening on 1434 like 127.0.0.1). Again, this may just be a coincedense, but at least I'm up and running now.

Thanks,|||

Yes, the error message could stand to be more useful. I will make sure we address that.

But it looks like you've solved the problem though. By default, SQL Server listens for TCP connections on port 1433. Port 1434 is normally used by SQL Browser to respond to UDP queries to map a SQL Server instance name to IP address & port number.

Thank you for your feedback,

--David Olix

JDBC Development

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've foud the problem the day before but you in an increddible time of 7 moths.

You are the best, with "your messages" and "increddible fast work" :))

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've found the problem the day after i've posted the problem, but you in an increddible time of 7 months.

You are the best, with "your messages" and "increddible fast work" :))

|||

ProgrammerJ,

I sincerely apologize for not responding to your question sooner. We do try our best to respond to all questions posted on this forum within two business days. In this case we did not. Indeed, the only reason I saw your post at all is that someone else followed up to it with a post about the same problem, which brought the thread back to the top of the list. However, I hope this has not dissuaded you from using the Microsoft SQL Server 2005 JDBC driver.

Again, my apologies, and thank you for your feedback,

--David Olix

JDBC Development

|||http://msdn.microsoft.com/newsgroups/default.aspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.jdbcdriver&p=1&tid=f0f1c794-a805-4370-aca7-4f633c460104&mid=f0f1c794-a805-4370-aca7-4f633c460104

I have posted a similar problem on the jdbc discussion newsgroup. We are using the production 1.1 version of the JDBC driver. To repeat that post:

The stack trace is

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown
Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.flush(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.endRequest(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown
Source)

This is happening during a performance stress test, and only happens very
sporadically: two or three times during a multi-hour test of several
simultaneous users pounding on the database. There are no events on the DB
server, and nothing in the SQL Server 2005 log. The Tomcat server is running
on Linux. We are using a commons-DBCP connection pool that validates the
connection on borrow with "select 1", so we know the connection is good when
we get it for the pool. The network error happens right after a prepare
statement, so the connection is working, and then breaks on the execute of
the prepared statement. The query is not a long running query. We've also
checked the network health and it looks fine. We start some background batch
jobs against the server before the errors start to occur.

We are pretty much out of ideas. There are some relevant threads we can see
by Googling this, but neither IE nor Firefox can get the msdn.forums,
complaining the URL will never complete or something, which can be caused by
not accepting cookies, but that's not the problem.

sql

Plase HELP ! SQL ERROR =>> An I/O error occurred while receiving the TDS prelogin resp

i get this error when i try to connect to sql server 2005 developer edition with the latest JDBC.

sql string =>> jdbc:sqlserver://localhost:1434;databaseName=c1 Elvis elvis
SQL STATE: 08S01
ERROR CODE: 0
MESSAGE: An I/O error occurred while receiving the TDS prelogin response.

And the stacktrace is:

com.microsoft.sqlserver.jdbc.SQLServerException: An I/O error occurred while receiving the TDS prelogin response.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.Prelogin(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)

and is called :connection=DriverManager.getConnection(con, user, password);

Please tell me what is wrong ? Because in other part of the code it works fine !

What is this "I/O error occurred while receiving the TDS prelogin response" ?

There is a limitation about the number of simultaneous connections to the database in the SQL Server 2005 Developer Edition DVD ?

Can be that error because of that ? What is TDS prelogin response ? Any ideea Microsoft guys ? Pls ...

|||I'm also getting the same error! Did you ever find anything out, Elvis? Somebody please respond. Anything will help.

Thanks,|||Hi all,

I fixed it! Well I don't know if this fixes it, but it's working now by some intervention. I changed the url from 127.0.0.1 to my intranet ip 172.22.x.x (not my external 64.72.x.x). I then telneted 172.22.x.x port 1434 (which works for 127.0.0.1) and it didn't work. Then I tried 172.22.x.x 1433 and it did (even though the server logs say it's listening on 1434 like 127.0.0.1). Again, this may just be a coincedense, but at least I'm up and running now.

Thanks,|||

Yes, the error message could stand to be more useful. I will make sure we address that.

But it looks like you've solved the problem though. By default, SQL Server listens for TCP connections on port 1433. Port 1434 is normally used by SQL Browser to respond to UDP queries to map a SQL Server instance name to IP address & port number.

Thank you for your feedback,

--David Olix

JDBC Development

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've foud the problem the day before but you in an increddible time of 7 moths.

You are the best, with "your messages" and "increddible fast work" :))

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've found the problem the day after i've posted the problem, but you in an increddible time of 7 months.

You are the best, with "your messages" and "increddible fast work" :))

|||

ProgrammerJ,

I sincerely apologize for not responding to your question sooner. We do try our best to respond to all questions posted on this forum within two business days. In this case we did not. Indeed, the only reason I saw your post at all is that someone else followed up to it with a post about the same problem, which brought the thread back to the top of the list. However, I hope this has not dissuaded you from using the Microsoft SQL Server 2005 JDBC driver.

Again, my apologies, and thank you for your feedback,

--David Olix

JDBC Development

|||http://msdn.microsoft.com/newsgroups/default.aspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.jdbcdriver&p=1&tid=f0f1c794-a805-4370-aca7-4f633c460104&mid=f0f1c794-a805-4370-aca7-4f633c460104

I have posted a similar problem on the jdbc discussion newsgroup. We are using the production 1.1 version of the JDBC driver. To repeat that post:

The stack trace is

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown
Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.flush(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.endRequest(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown
Source)

This is happening during a performance stress test, and only happens very
sporadically: two or three times during a multi-hour test of several
simultaneous users pounding on the database. There are no events on the DB
server, and nothing in the SQL Server 2005 log. The Tomcat server is running
on Linux. We are using a commons-DBCP connection pool that validates the
connection on borrow with "select 1", so we know the connection is good when
we get it for the pool. The network error happens right after a prepare
statement, so the connection is working, and then breaks on the execute of
the prepared statement. The query is not a long running query. We've also
checked the network health and it looks fine. We start some background batch
jobs against the server before the errors start to occur.

We are pretty much out of ideas. There are some relevant threads we can see
by Googling this, but neither IE nor Firefox can get the msdn.forums,
complaining the URL will never complete or something, which can be caused by
not accepting cookies, but that's not the problem.