Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

[fniles]

Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.

It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding."

What would cause this error to show up every now and then, but not all
the time ?
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?

Dim adoCon As SqlClient.SqlConnection
With adoCon
.ConnectionString = "Data Source=myServer;Initial Catalog=myDB;User
ID=myID;Password=myPwd;Max Pool Size=200"
.Open()
End With

Thank you

[Cor]

Hi,

The timeout can be everything, but an not an not rolledback or committed
transaction is a reasonable chance to be the reason.

Cor

"fniles" wrote in message news:f83225e8-d3e8-42bf-b967-***@i17g2000vbq.googlegroups.com...

Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.

It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding."

What would cause this error to show up every now and then, but not all
the time ?
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?

Dim adoCon As SqlClient.SqlConnection
With adoCon
.ConnectionString = "Data Source=myServer;Initial Catalog=myDB;User
ID=myID;Password=myPwd;Max Pool Size=200"
.Open()
End With

Thank you

[fniles]

Thank you for your quick reply.

Did you say that a transaction that's not committed or rolledback will
be 1 of the cause for timeout expired when opening the database ?

Post by Cor
Hi,
The timeout can be everything, but an not an not rolledback or committed
transaction is a reasonable chance to be the reason.
Cor
Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on  Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.
It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired.  The timeout period
elapsed prior to completion of the operation or the server is not
responding."
What would cause this error to show up every now and then, but not all
the time ?
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?
Dim adoCon As SqlClient.SqlConnection
With adoCon
   .ConnectionString = "Data Source=myServer;Initial Catalog=myDB;User
ID=myID;Password=myPwd;Max Pool Size=200"
   .Open()
End With
Thank you

[Cor]

Yes the database is waiting for it.


"fniles" wrote in message news:b788e600-7ac6-43be-bc4c-***@p8g2000vbs.googlegroups.com...

Thank you for your quick reply.

Did you say that a transaction that's not committed or rolledback will
be 1 of the cause for timeout expired when opening the database ?

Post by Cor
Hi,
The timeout can be everything, but an not an not rolledback or committed
transaction is a reasonable chance to be the reason.
Cor
Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.
It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding."
What would cause this error to show up every now and then, but not all
the time ?
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?
Dim adoCon As SqlClient.SqlConnection
With adoCon
.ConnectionString = "Data Source=myServer;Initial Catalog=myDB;User
ID=myID;Password=myPwd;Max Pool Size=200"
.Open()
End With
Thank you

[fniles]

So, the database is waiting for a transaction to be committed or
rolled back before I can open the database again, is that correct ?
Does it mean the stored procedure is too slow to execute ?
Below is the stored procedure that I call.
What is the best way to fix this error ?
When this error occurs, should I loop and retry opening the database a
few times ?
Thank you.

ALTER PROCEDURE [dbo].[INSERT_INTO_HistTradesOrig]
@ID int output,
@Order varchar(50) = NULL,
@ACCOUNT varchar(10) = NULL,
:
AS
DECLARE @Success int, @TryNr int, @ErrMsg varchar(500), @ErrNumber
int, @expected_id int;
SET @Success = 0;
SET @TryNr = 1;
SET @ErrMsg = ''
WHILE @Success = 0 AND @TryNr <= 3 and @ErrMsg = ''
BEGIN;
BEGIN TRY;
BEGIN TRANSACTION;
retry:
BEGIN TRY;
SELECT @expected_id = ident_current('HistTradesOrig') + 1
SELECT @order = @account + '-' + CAST(@expected_id AS varchar(50))
insert into HistTradesOrig
(Order],..)
values
(@Order...)
END TRY
BEGIN CATCH;
SELECT @ErrNumber = ERROR_NUMBER()
IF @ErrNumber = 2627
BEGIN;
GOTO retry
END;
ELSE
BEGIN;
SET @ErrMsg = '1.' + ERROR_MESSAGE() + ' ' + @order
RAISERROR (@ErrMsg, 16, 1);
END;
END CATCH

select @ID = SCOPE_IDENTITY()
IF @id <> @expected_id
BEGIN
UPDATE HistTradesOrig
SET [Order] = @ACCOUNT + '-' + CAST(@ID AS varchar(50))
WHERE ID = @ID
END
SET @Success = 1
COMMIT TRANSACTION;
END TRY
BEGIN CATCH;
ROLLBACK TRANSACTION;
SELECT @ErrNumber = ERROR_NUMBER()
IF @ErrNumber = 1205
BEGIN;
SET @TryNr = @TryNr + 1;
IF @TryNr > 3
RAISERROR ('Giving up after 3 consecutive deadlocks for %s',
16, 1,@ACCOUNT);
END;
ELSE
BEGIN;
SET @ErrMsg = '2.' + ERROR_MESSAGE() + ' ' + @order
RAISERROR (@ErrMsg, 16, 1);
END;
END CATCH;
END;

Post by Cor
Yes the database is waiting for it.
Thank you for your quick reply.
Did you say that a transaction that's not committed or rolledback will
be 1 of the cause for timeout expired when opening the database ?

Post by Cor
Hi,
The timeout can be everything, but an not an not rolledback or committed
transaction is a reasonable chance to be the reason.
Cor
Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on  Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.
It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired.  The timeout period
elapsed prior to completion of the operation or the server is not
responding."
What would cause this error to show up every now and then, but not all
the time ?
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?
Dim adoCon As SqlClient.SqlConnection
With adoCon
   .ConnectionString = "Data Source=myServer;Initial Catalog=myDB;User
ID=myID;Password=myPwd;Max Pool Size=200"
   .Open()
End With
Thank you- Hide quoted text -

- Show quoted text -

[Cor]

The transaction takes place on client side.

For instance you do in one transaction two actions but find an error in
between and shows a messagebox without doing first a rollback.
You can be sure the server gives a timeout.

Cor

"fniles" wrote in message news:f1ffffb6-8b04-4cae-9bc6-***@m35g2000vbn.googlegroups.com...

So, the database is waiting for a transaction to be committed or
rolled back before I can open the database again, is that correct ?
Does it mean the stored procedure is too slow to execute ?
Below is the stored procedure that I call.
What is the best way to fix this error ?
When this error occurs, should I loop and retry opening the database a
few times ?
Thank you.

ALTER PROCEDURE [dbo].[INSERT_INTO_HistTradesOrig]
@ID int output,
@Order varchar(50) = NULL,
@ACCOUNT varchar(10) = NULL,
:
AS
DECLARE @Success int, @TryNr int, @ErrMsg varchar(500), @ErrNumber
int, @expected_id int;
SET @Success = 0;
SET @TryNr = 1;
SET @ErrMsg = ''
WHILE @Success = 0 AND @TryNr <= 3 and @ErrMsg = ''
BEGIN;
BEGIN TRY;
BEGIN TRANSACTION;
retry:
BEGIN TRY;
SELECT @expected_id = ident_current('HistTradesOrig') + 1
SELECT @order = @account + '-' + CAST(@expected_id AS varchar(50))
insert into HistTradesOrig
(Order],..)
values
(@Order...)
END TRY
BEGIN CATCH;
SELECT @ErrNumber = ERROR_NUMBER()
IF @ErrNumber = 2627
BEGIN;
GOTO retry
END;
ELSE
BEGIN;
SET @ErrMsg = '1.' + ERROR_MESSAGE() + ' ' + @order
RAISERROR (@ErrMsg, 16, 1);
END;
END CATCH

select @ID = SCOPE_IDENTITY()
IF @id <> @expected_id
BEGIN
UPDATE HistTradesOrig
SET [Order] = @ACCOUNT + '-' + CAST(@ID AS varchar(50))
WHERE ID = @ID
END
SET @Success = 1
COMMIT TRANSACTION;
END TRY
BEGIN CATCH;
ROLLBACK TRANSACTION;
SELECT @ErrNumber = ERROR_NUMBER()
IF @ErrNumber = 1205
BEGIN;
SET @TryNr = @TryNr + 1;
IF @TryNr > 3
RAISERROR ('Giving up after 3 consecutive deadlocks for %s',
16, 1,@ACCOUNT);
END;
ELSE
BEGIN;
SET @ErrMsg = '2.' + ERROR_MESSAGE() + ' ' + @order
RAISERROR (@ErrMsg, 16, 1);
END;
END CATCH;
END;

Post by Cor
Yes the database is waiting for it.
Thank you for your quick reply.
Did you say that a transaction that's not committed or rolledback will
be 1 of the cause for timeout expired when opening the database ?

Post by Cor
Hi,
The timeout can be everything, but an not an not rolledback or committed
transaction is a reasonable chance to be the reason.
Cor
Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.
It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding."
What would cause this error to show up every now and then, but not all
the time ?
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?
Dim adoCon As SqlClient.SqlConnection
With adoCon
.ConnectionString = "Data Source=myServer;Initial Catalog=myDB;User
ID=myID;Password=myPwd;Max Pool Size=200"
.Open()
End With
Thank you- Hide quoted text -

- Show quoted text -

[fniles]

Thank you.

Post by Cor
The transaction takes place on client side.

I am not sure I follow.
The Stored Procedure has a BEGIN TRANSACTION, COMMIT TRANSACTION and
ROLLBACK TRANSACTION, and as far as I know stored procedure is
executed on the SQL Server, not on the client side.

Post by Cor
For instance you do in one transaction two actions but find an error in between and shows a messagebox without doing first a rollback.

So, if in between BEGIN TRANSACTION and COMMIT TRANSACTION I got an
error without doing a ROLLBACK TRANSACTION, I will not be able to open
the database again until I either COMMIT or ROLLBACK TRANSACTION, is
that correct ?
If I do not see any error, is the cause of the "timeout expired" error
something else ?

Post by Cor
The transaction takes place on client side.
For instance you do in one transaction two actions but find an error in
between and shows a messagebox without doing first a rollback.
You can be sure the server gives a timeout.
Cor
So, the database is waiting for a transaction to be committed or
rolled back before I can open the database again, is that correct ?
Does it mean the stored procedure is too slow to execute ?
Below is the stored procedure that I call.
What is the best way to fix this error ?
When this error occurs, should I loop and retry opening the database a
few times ?
Thank you.
ALTER PROCEDURE [dbo].[INSERT_INTO_HistTradesOrig]
@ID int output,
@Order varchar(50) = NULL,
@ACCOUNT varchar(10)  = NULL,
AS
BEGIN;
  BEGIN TRY;
BEGIN TRANSACTION;
BEGIN TRY;
insert into HistTradesOrig
(Order],..)
values
END TRY
    BEGIN CATCH;
BEGIN;
GOTO retry
END;
ELSE
BEGIN;
END;
END CATCH
BEGIN
UPDATE HistTradesOrig
END
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH;
    ROLLBACK TRANSACTION;
    BEGIN;
        RAISERROR ('Giving up after 3 consecutive deadlocks for %s',
    END;
    ELSE
    BEGIN;
    END;
  END CATCH;
END;

Post by Cor
Yes the database is waiting for it.
Thank you for your quick reply.
Did you say that a transaction that's not committed or rolledback will
be 1 of the cause for timeout expired when opening the database ?

Post by Cor
Hi,
The timeout can be everything, but an not an not rolledback or committed
transaction is a reasonable chance to be the reason.
Cor
Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on  Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.
It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired.  The timeout period
elapsed prior to completion of the operation or the server is not
responding."
What would cause this error to show up every now and then, but not all
the time ?
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?
Dim adoCon As SqlClient.SqlConnection
With adoCon
   .ConnectionString = "Data Source=myServer;Initial Catalog=myDB;User
ID=myID;Password=myPwd;Max Pool Size=200"
   .Open()
End With
Thank you- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

[Cor]

No until the timeout is expired.

Did you ever used SQL server profiler by the way?
http://msdn.microsoft.com/en-us/library/ms187929.aspx

It looks like a tool every developer makes sometimes for himself, not really
an end-user tool.

But fine to solve these problems.

Cor

"fniles" wrote in message news:67bdc9b7-c900-4040-a062-***@f2g2000vby.googlegroups.com...

Thank you.

Post by Cor
The transaction takes place on client side.

I am not sure I follow.
The Stored Procedure has a BEGIN TRANSACTION, COMMIT TRANSACTION and
ROLLBACK TRANSACTION, and as far as I know stored procedure is
executed on the SQL Server, not on the client side.

Post by Cor
For instance you do in one transaction two actions but find an error in
between and shows a messagebox without doing first a rollback.

So, if in between BEGIN TRANSACTION and COMMIT TRANSACTION I got an
error without doing a ROLLBACK TRANSACTION, I will not be able to open
the database again until I either COMMIT or ROLLBACK TRANSACTION, is
that correct ?
If I do not see any error, is the cause of the "timeout expired" error
something else ?

Post by Cor
The transaction takes place on client side.
For instance you do in one transaction two actions but find an error in
between and shows a messagebox without doing first a rollback.
You can be sure the server gives a timeout.
Cor
So, the database is waiting for a transaction to be committed or
rolled back before I can open the database again, is that correct ?
Does it mean the stored procedure is too slow to execute ?
Below is the stored procedure that I call.
What is the best way to fix this error ?
When this error occurs, should I loop and retry opening the database a
few times ?
Thank you.
ALTER PROCEDURE [dbo].[INSERT_INTO_HistTradesOrig]
@ID int output,
@Order varchar(50) = NULL,
@ACCOUNT varchar(10) = NULL,
AS
BEGIN;
BEGIN TRY;
BEGIN TRANSACTION;
BEGIN TRY;
insert into HistTradesOrig
(Order],..)
values
END TRY
BEGIN CATCH;
BEGIN;
GOTO retry
END;
ELSE
BEGIN;
END;
END CATCH
BEGIN
UPDATE HistTradesOrig
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH;
ROLLBACK TRANSACTION;
BEGIN;
RAISERROR ('Giving up after 3 consecutive deadlocks for %s',
END;
ELSE
BEGIN;
END;
END CATCH;
END;

Post by Cor
Yes the database is waiting for it.
Thank you for your quick reply.
Did you say that a transaction that's not committed or rolledback will
be 1 of the cause for timeout expired when opening the database ?

Post by Cor
Hi,
The timeout can be everything, but an not an not rolledback or committed
transaction is a reasonable chance to be the reason.
Cor
Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.
It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding."
What would cause this error to show up every now and then, but not all
the time ?
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?
Dim adoCon As SqlClient.SqlConnection
With adoCon
.ConnectionString = "Data Source=myServer;Initial Catalog=myDB;User
ID=myID;Password=myPwd;Max Pool Size=200"
.Open()
End With
Thank you- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

[fniles]

Thank you.

Post by Cor
No until the timeout is expired.

Is this the answer to the question
- So, if in between BEGIN TRANSACTION and COMMIT TRANSACTION I got an
error without doing a ROLLBACK TRANSACTION, I will not be able to open
the database again until I either COMMIT or ROLLBACK TRANSACTION, is
that correct ?
OR
-If I do not see any error, is the cause of the "timeout expired"
error something else ?

Post by Cor
No until the timeout is expired.
Did you ever used SQL server profiler by the way?http://msdn.microsoft.com/en-us/library/ms187929.aspx
It looks like a tool every developer makes sometimes for himself, not really
an end-user tool.
But fine to solve these problems.
Cor
Thank you.

Post by Cor
The transaction takes place on client side.

I am not sure I follow.
The Stored Procedure has a BEGIN TRANSACTION, COMMIT TRANSACTION and
ROLLBACK TRANSACTION, and as far as I know stored procedure is
executed on the SQL Server, not on the client side.

Post by Cor
For instance you do in one transaction two actions but find an error in
between and shows a messagebox without doing first a rollback.

So, if in between BEGIN TRANSACTION and COMMIT TRANSACTION I got an
error without doing a ROLLBACK TRANSACTION, I will not be able to open
the database again until I either COMMIT or ROLLBACK TRANSACTION, is
that correct ?
If I do not see any error, is the cause of the "timeout expired" error
something else ?

Post by Cor
The transaction takes place on client side.
For instance you do in one transaction two actions but find an error in
between and shows a messagebox without doing first a rollback.
You can be sure the server gives a timeout.
Cor
So, the database is waiting for a transaction to be committed or
rolled back before I can open the database again, is that correct ?
Does it mean the stored procedure is too slow to execute ?
Below is the stored procedure that I call.
What is the best way to fix this error ?
When this error occurs, should I loop and retry opening the database a
few times ?
Thank you.
ALTER PROCEDURE [dbo].[INSERT_INTO_HistTradesOrig]
@ID int output,
@Order varchar(50) = NULL,
@ACCOUNT varchar(10)  = NULL,
AS
BEGIN;
  BEGIN TRY;
BEGIN TRANSACTION;
BEGIN TRY;
insert into HistTradesOrig
(Order],..)
values
END TRY
    BEGIN CATCH;
BEGIN;
GOTO retry
END;
ELSE
BEGIN;
END;
END CATCH
BEGIN
UPDATE HistTradesOrig
END
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH;
    ROLLBACK TRANSACTION;
    BEGIN;
        RAISERROR ('Giving up after 3 consecutive deadlocks for %s',
    END;
    ELSE
    BEGIN;
    END;
  END CATCH;
END;

Post by Cor
Yes the database is waiting for it.
Thank you for your quick reply.
Did you say that a transaction that's not committed or rolledback will
be 1 of the cause for timeout expired when opening the database ?

Post by Cor
Hi,
The timeout can be everything, but an not an not rolledback or committed
transaction is a reasonable chance to be the reason.
Cor
Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on  Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.
It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired.  The timeout period
elapsed prior to completion of the operation or the server is not
responding."
What would cause this error to show up every now and then, but not all
the time ?
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?
Dim adoCon As SqlClient.SqlConnection
With adoCon
   .ConnectionString = "Data Source=myServer;Initial Catalog=myDB;User
ID=myID;Password=myPwd;Max Pool Size=200"
   .Open()
End With
Thank you- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

[Erland Sommarskog]

Post by fniles

Post by Cor
No until the timeout is expired.

Is this the answer to the question
- So, if in between BEGIN TRANSACTION and COMMIT TRANSACTION I got an
error without doing a ROLLBACK TRANSACTION, I will not be able to open
the database again until I either COMMIT or ROLLBACK TRANSACTION, is
that correct ?

I don't know what Cor have in mind, but that is definitely not correct.

There are certainly problems with command timeouts if you don't handle
them well. Say that you run a command that starts a transaction. After
30 second the API times out (My recommendation is to set the command
timeout to 0 to prevent this from happening.) If you just move on, you
will move on with that transaction. You say that you disconnects, but
the transaction will live until the connection is reused. This can
result in blocking. But it cannot result in other processes not being
able to connect.

So all in all, I'm inclined to think that Cor's suggestion is a false
lead.

[Cor]

Erland,

I am not waiting for a long discussion with you.

If you have another opinion than tell that, but spare me your personal
vision on other persons.

Thanks,

Cor

Post by fniles

Post by Cor
No until the timeout is expired.

Is this the answer to the question
- So, if in between BEGIN TRANSACTION and COMMIT TRANSACTION I got an
error without doing a ROLLBACK TRANSACTION, I will not be able to open
the database again until I either COMMIT or ROLLBACK TRANSACTION, is
that correct ?

I don't know what Cor have in mind, but that is definitely not correct.

There are certainly problems with command timeouts if you don't handle
them well. Say that you run a command that starts a transaction. After
30 second the API times out (My recommendation is to set the command
timeout to 0 to prevent this from happening.) If you just move on, you
will move on with that transaction. You say that you disconnects, but
the transaction will live until the connection is reused. This can
result in blocking. But it cannot result in other processes not being
able to connect.

So all in all, I'm inclined to think that Cor's suggestion is a false
lead.

[Bob Barrows]

Err, I must have missed something. "Personal vision"?? I assume that phrase
comes from some phrase meaning "insult" in your language? How does saying
"Cor is incorrect" and explaining why he thinks so constitute an "insult"?

I've never seen Erland insult anyone (even CELKO <gdr>), but I assume if he
ever did, I can't help but think that it would be unmistakable.

Please give people posting here the benefit of the doubt. Things said in
newsgroup posts can be interpreted quite differently from what they were
intended.

PS. If there is personal history between you and Erland, I guess that would
be another story wouldn't it. However, wouldn't it make more sense to
conduct that vendetta in private so that someone coming on this public
discussion could not come to the conclusion that you were thin-skinned,
i.e., prone to fly off the handle with very little provocation?

Post by Cor
Erland,
I am not waiting for a long discussion with you.
If you have another opinion than tell that, but spare me your personal
vision on other persons.
Thanks,
Cor

Post by fniles

Post by Cor
No until the timeout is expired.

Is this the answer to the question
- So, if in between BEGIN TRANSACTION and COMMIT TRANSACTION I got an
error without doing a ROLLBACK TRANSACTION, I will not be able to
open the database again until I either COMMIT or ROLLBACK
TRANSACTION, is that correct ?

I don't know what Cor have in mind, but that is definitely not
correct.
There are certainly problems with command timeouts if you don't handle
them well. Say that you run a command that starts a transaction. After
30 second the API times out (My recommendation is to set the command
timeout to 0 to prevent this from happening.) If you just move on, you
will move on with that transaction. You say that you disconnects, but
the transaction will live until the connection is reused. This can
result in blocking. But it cannot result in other processes not being
able to connect.
So all in all, I'm inclined to think that Cor's suggestion is a false
lead.

[Erland Sommarskog]

Post by fniles
Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.
It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding."

First of all, isolate exactly there the problem appears. Does it happen
when you connect, or does it happen when you run the command? You seem
to be thinking that it happens when you connect, but there all reason
to double-check. The course of action depends on where it happens.

If it it happens on connection, the error may occur in the connection
pool, that is, there is not any available connection in the pool. I
will have to admit that I don't know for sure whether a timeout in the
connection pool gives exactly this error.

Yet a possible reason is simply a network glitch. DNS somewhere dies
for a moment or something similar. I have experienced short outages in the
network where I work have not been able to reach some servers for a
few minutes. However such errors usually gives a different error message,
as the server cannot be reached at all. I think this message indicates
that connection was initialised, but never completed.

In this case, there may be problems with available worker threads in
SQL Server. I recall a thread in our internal MVP forum where quite
some few experienced people have fought this error with limited success.
I think there is a hotfix, but it may not address everything.

SP4 for SQL 2005 came out recently, I think you install it as a start.

Post by fniles
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?

As long as you can accept the delay, that's a way. You can also use the
.ConnectionTimeout property to permit for more time.

Finally, do you have any LOGON triggers in place?

[fniles]

Thank you.

Yes, the error definitely occurs when it tries to open the
opendatabase.
I increase the timeout to 30.

Post by Erland Sommarskog
Finally, do you have any LOGON triggers in place?

I don't think so.
What is a logon triggers ?

Post by Erland Sommarskog

Post by fniles
Our application is in VB.NET 2008 and our database is SQL Server 2005.
Our application runs on Windows Server 2003 R2 Standard Edition SP 2.
The database runs on  Windows Server 2003 R2 Enterprise x64 Edition SP
1.
The database server has many databases.
The database connection string in the program uses a name instance,
not an IP address.
The application is multi threaded, each client that connects to it has
each own thread.
In the application, I open and close database before updating it.
It works for most of the time, but every now and then during opening
the database I got the error "Timeout expired.  The timeout period
elapsed prior to completion of the operation or the server is not
responding."

First of all, isolate exactly there the problem appears. Does it happen
when you connect, or does it happen when you run the command? You seem
to be thinking that it happens when you connect, but there all reason
to double-check. The course of action depends on where it happens.
If it it happens on connection, the error may occur in the connection
pool, that is, there is not any available connection in the pool. I
will have to admit that I don't know for sure whether a timeout in the
connection pool gives exactly this error.
Yet a possible reason is simply a network glitch. DNS somewhere dies
for a moment or something similar. I have experienced short outages in the
network where I work have not been able to reach some servers for a
few minutes. However such errors usually gives a different error message,
as the server cannot be reached at all. I think this message indicates
that connection was initialised, but never completed.
In this case, there may be problems with available worker threads in
SQL Server. I recall a thread in our internal MVP forum where quite
some few experienced people have fought this error with limited success.
I think there is a hotfix, but it may not address everything.
SP4 for SQL 2005 came out recently, I think you install it as a start.

Post by fniles
And, what is the best way to fix it ? In the application, when this
error occurs, should I retry opening the database a few times ?

As long as you can accept the delay, that's a way. You can also use the
.ConnectionTimeout property to permit for more time.
Finally, do you have any LOGON triggers in place?
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx- Hide quoted text -
- Show quoted text -

[Erland Sommarskog]

Post by fniles

Post by Erland Sommarskog
Finally, do you have any LOGON triggers in place?

I don't think so.
What is a logon triggers ?

A login trigger is a server-level DDL trigger which fires when a user logs
in. You could for instance use it to prevent users from logging during
maintenance hours. And, for that matter, shoot yourself in the foot. They
were added in SQL 2005 SP2.

I would not really expect your problem to be with a login trigger, but I
figured that I should check.

[fniles]

Thank you.
You mentioned that "If it it happens on connection, the error may
occur in the connection pool, that is, there is not any available
connection in the pool. "
I set the max pool size to 200. Should I increase it ?
The error usually happens during busy times when many people just
logged in at the same time.

With adoCon
.ConnectionString = "Data Source=myServer;Initial Catalog=myDB;User
ID=myID;Password=myPwd;Max Pool Size=200"

Post by Erland Sommarskog

Post by fniles

Post by Erland Sommarskog
Finally, do you have any LOGON triggers in place?

I don't think so.
What is a logon triggers ?

A login trigger is a server-level DDL trigger which fires when a user logs
in. You could for instance use it to prevent users from logging during
maintenance hours. And, for that matter, shoot yourself in the foot. They
were added in SQL 2005 SP2.
I would not really expect your problem to be with a login trigger, but I
figured that I should check.
--
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Post by fniles
You mentioned that "If it it happens on connection, the error may
occur in the connection pool, that is, there is not any available
connection in the pool. "
I set the max pool size to 200. Should I increase it ?
The error usually happens during busy times when many people just
logged in at the same time.

How do people log in? Do they log in over the web, or do they log in
through their workstation? Or do all connections go through an application
server? (Or a farm of servers.) It's only in the first and the last case,
the connection-pool size matters.

In any case, I'm quite sure that the message is different in that case.

My prime theory is that you run out of worker threads. What do these
queries return in peak situations:

select max_workers_count From sys.dm_os_sys_info
select count(*) from sys.dm_os_threads

SQL Server dynamically configures the number of threads, but the problem
could be to get a worker thread in time.

Did you change the connection timeout? Did that change matters?

[fniles]

I am sorry for my late reply.
As always, thank you for your help again.

How do people log in? Do they log in over the web, or do they log in through their workstation? Or do all connections go through an application server? (Or a farm of >servers.) It's only in the first and the last case, the connection-pool size matters.

All connections go through an application server, that has a thread
per client connection.

I just ran the below queries (but it's not busy right now) and they
return the following:
select max_workers_count From sys.dm_os_sys_info RETURNS 576
select count(*) from sys.dm_os_threads RETURNS 166
Why when I ran the same query on a different SQL Server (on a
different machine) the queries return the same result ?
I will run the queries on Monday at the busy time, and report back the
result

Did you change the connection timeout? Did that change matters?

Yes, I have changed the connection timeout to 30.
I haven't seen this problem again after that, but I haven't run the
program on a busier system yet, I will need more time to see if the
problem is permanently fixed.
I will report back once I find out.

So, you don't think I need to increase the max pool size from 200 ?

Thank you.

Post by fniles
You mentioned that "If it it happens on connection, the error may
occur in the connection pool, that is, there is not any available
connection in the pool. "
I set the max pool size to 200. Should I increase it ?
The error usually happens during busy times when many people just
logged in at the same time.

How do people log in? Do they log in over the web, or do they log in
through their workstation? Or do all connections go through an application
server? (Or a farm of servers.) It's only in the first and the last case,
the connection-pool size matters.
In any case, I'm quite sure that the message is different in that case.
My prime theory is that you run out of worker threads. What do these
   select max_workers_count From sys.dm_os_sys_info
   select count(*) from sys.dm_os_threads
SQL Server dynamically configures the number of threads, but the problem
could be to get a worker thread in time.
Did you change the connection timeout? Did that change matters?
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Erland Sommarskog]

Post by fniles
All connections go through an application server, that has a thread
per client connection.

OK. If that is a single server, there *could* be a bottleneck.

Post by fniles
So, you don't think I need to increase the max pool size from 200 ?

If my guess is that the message does not related to the pool is correct.

Please bear in mind that it is very hard to diagnose a system you don't see,
and I have to rely on the information I am told, and I don't know what I am
not told.

[fniles]

Thank you.

Post by Erland Sommarskog
OK. If that is a single server, there *could* be a bottleneck.

Did you mean that SQL Server run out of worker threads, or there is a
bottleneck in our application ?
What did you mean by bottleneck ?

Post by Erland Sommarskog

Post by fniles
All connections go through an application server, that has a thread
per client connection.

OK. If that is a single server, there *could* be a bottleneck.

Post by fniles
So, you don't think I need to increase the max pool size from 200 ?

If my guess is that the message does not related to the pool is correct.
Please bear in mind that it is very hard to diagnose a system you don't see,
and I have to rely on the information I am told, and I don't know what I am
not told.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Erland Sommarskog]

Post by fniles

Post by Erland Sommarskog
OK. If that is a single server, there *could* be a bottleneck.

Did you mean that SQL Server run out of worker threads, or there is a
bottleneck in our application ?
What did you mean by bottleneck ?

As I said, it is very difficult to diagnose a system that I don't see.
Please understand that most of the answers you will get are speculations.
It's not one of the situations where the doctor can tell from your
description "oh, you have the poh-poh disease". From what I could tell
you could have cancer, a cold, or be mentally depressed because your
girlfriend dumped you.

If you want good and precise answers for what is going on, you will need to
get a consultant that comes on site to investigate what is going on.

What I meant to say is that if you have a single application server, it is
something that could be overloaded, and thus could be a bottleneck. Just
like if you tell me that you take a certain medicine, you could develop
allergic reactions against it - but you may also be perfectly unaffected
by it.

[fniles]

Thanks, Erland.
I understand that you couldn't give the exact answer to the problem. I
appreciate the help you have given me.

If you don't mind, can I ask you more questions ? (Again, I understand
that your answer may not be the exact answer to the problem).
If you don't know the answer, that's fine also.

Although it is a single application, it is using multiple threads,
each thread for every client that connects to it.
So, in this case you no longer think it is SQL Server run out of
worker threads ?

Post by Erland Sommarskog

Post by fniles

Post by Erland Sommarskog
OK. If that is a single server, there *could* be a bottleneck.

Did you mean that SQL Server run out of worker threads, or there is a
bottleneck in our application ?
What did you mean by bottleneck ?

As I said, it is very difficult to diagnose a system that I don't see.
Please understand that most of the answers you will get are speculations.
It's not one of the situations where the doctor can tell from your
description "oh, you have the poh-poh disease". From what I could tell
you could have cancer, a cold, or be mentally depressed because your
girlfriend dumped you.
If you want good and precise answers for what is going on, you will need to
get a consultant that comes on site to investigate what is going on.
What I meant to say is that if you have a single application server, it is
something that could be overloaded, and thus could be a bottleneck. Just
like if you tell me that you take a certain medicine, you could develop
allergic reactions against it - but you may also be perfectly unaffected
by it.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Erland Sommarskog]

Post by fniles
Although it is a single application, it is using multiple threads,
each thread for every client that connects to it.
So, in this case you no longer think it is SQL Server run out of
worker threads ?

No. My prime guess is that this could be the reason.

But as I said, it's difficult to make a diagnosis of the situation on a
distance. And to be honest, I have not experienced that particular situation
myself, so I mainly rely on what I eavesdropped from discussions elsewhere.

[fniles]

I have more information about the problem, because it happened again
last night, during a non busy time.

We have 3 databases on that SQL Server machine, and multiple different
applications (on 3 different machines) connect to those 3 databases.
Those applications around the same time got the "Timeout expired"
error either when reading a query (different queries on different
applications) or opening the database.

Meanwhile, another application that connect to a database on a
different SQL Server machine didn't get the error.

It seems like there is a problem with that SQL Server machine may be ?
Like there is a network problem connecting to that SQL Server
machine ?

Post by Erland Sommarskog

Post by fniles
Although it is a single application, it is using multiple threads,
each thread for every client that connects to it.
So, in this case you no longer think it is SQL Server run out of
worker threads ?

No. My prime guess is that this could be the reason.
But as I said, it's difficult to make a diagnosis of the situation on a
distance. And to be honest, I have not experienced that particular situation
myself, so I mainly rely on what I eavesdropped from discussions elsewhere.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[fniles]

Also, around the same time our network person was on that SQL Server
machine and was on Windows explorer, and he said Windows Explorer was
not responding at that time also.

Post by fniles
I have more information about the problem, because it happened again
last night, during a non busy time.
We have 3 databases on that SQL Server machine, and multiple different
applications (on 3 different machines) connect to those 3 databases.
Those applications around the same time got the "Timeout expired"
error either when reading a query (different queries on different
applications) or opening the database.
Meanwhile, another application that connect to a database on a
different SQL Server machine didn't get the error.
It seems like there is a problem with that SQL Server machine may be ?
Like there is a network problem connecting to that SQL Server
machine ?

Post by Erland Sommarskog

Post by fniles
Although it is a single application, it is using multiple threads,
each thread for every client that connects to it.
So, in this case you no longer think it is SQL Server run out of
worker threads ?

No. My prime guess is that this could be the reason.
But as I said, it's difficult to make a diagnosis of the situation on a
distance. And to be honest, I have not experienced that particular situation
myself, so I mainly rely on what I eavesdropped from discussions elsewhere.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx- Hide quoted text -

- Show quoted text -

[0***@gmail.com]

Post by fniles
Also, around the same time our network person was on that SQL Server
machine and was on Windows explorer, and he said Windows Explorer was
not responding at that time also.

Post by fniles
I have more information about the problem, because it happened again
last night, during a non busy time.
We have 3 databases on that SQL Server machine, and multiple different
applications (on 3 different machines) connect to those 3 databases.
Those applications around the same time got the "Timeout expired"
error either when reading a query (different queries on different
applications) or opening the database.
Meanwhile, another application that connect to a database on a
different SQL Server machine didn't get the error.
It seems like there is a problem with that SQL Server machine may be ?
Like there is a network problem connecting to that SQL Server
machine ?

Post by Erland Sommarskog

Post by fniles
Although it is a single application, it is using multiple threads,
each thread for every client that connects to it.
So, in this case you no longer think it is SQL Server run out of
worker threads ?

No. My prime guess is that this could be the reason.
But as I said, it's difficult to make a diagnosis of the situation on a
distance. And to be honest, I have not experienced that particular situation
myself, so I mainly rely on what I eavesdropped from discussions elsewhere.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx- Hide quoted text -

- Show quoted text -

http://www.sqlpassion.at/archive/2011/10/25/troubleshooting-threadpool-waits/

[0***@gmail.com]

Please refer below link, that might help you

http://www.sqlpassion.at/archive/2011/10/25/troubleshooting-threadpool-waits/

[Gene Wirchenko]

On Sun, 23 Jan 2011 05:42:54 -0800 (PST), fniles

Post by fniles
Thanks, Erland.
I understand that you couldn't give the exact answer to the problem. I
appreciate the help you have given me.
If you don't mind, can I ask you more questions ? (Again, I understand
that your answer may not be the exact answer to the problem).
If you don't know the answer, that's fine also.

It would be a good idea for you to read this:
http://www.catb.org/~esr/faqs/smart-questions.html
How To Ask Questions The Smart Way
It is a guide for how to ask technical questions.

Sincerely,

Gene Wirchenko