Timeout expired. The timeout period elapsed prior to completion ofthe operation or the server is no

  • Thread starter Thread starter fniles
  • Start date Start date
F

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

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
 
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 ?
 
Yes the database is waiting for it.


"fniles" wrote in message

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 ?
 
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;
 
fniles said:
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."
Click to expand...

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.
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 ?
Click to expand...

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?

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
 
Thank you.

Yes, the error definitely occurs when it tries to open the
opendatabase.
I increase the timeout to 30.
Finally, do you have any LOGON triggers in place?
Click to expand...
I don't think so.
What is a logon triggers ?
 
fniles said:
I don't think so.
What is a logon triggers ?
Click to expand...


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.

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
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

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;
 
Thank you.
The transaction takes place on client side.
Click to expand...
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.
For instance you do in one transaction two actions but find an error in between and shows a messagebox without doing first a rollback.
Click to expand...
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 ?


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


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;

Yes the database is waiting for it.
Click to expand...
"fniles"  wrote in message

Thank you for your quick reply.
Click to expand...
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 ?
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
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

Thank you.
The transaction takes place on client side.
Click to expand...
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.
For instance you do in one transaction two actions but find an error in
between and shows a messagebox without doing first a rollback.
Click to expand...
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 ?


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


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;

Yes the database is waiting for it.
Click to expand...
"fniles" wrote in message

Thank you for your quick reply.
Click to expand...
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 ?
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Thank you.
No until the timeout is expired.
Click to expand...
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 ?


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


Thank you.
The transaction takes place on client side.
Click to expand...

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.
For instance you do in one transaction two actions but find an error in
between and shows a messagebox without doing first a rollback.
Click to expand...

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 ?

The transaction takes place on client side.
Click to expand...
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.

"fniles"  wrote in message

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.
Click to expand...
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
Click to expand...
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;
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
fniles said:
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 ?
Click to expand...

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, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
 
Back
Top