Intermittent Timeout - ExecuteNonQuery On Stored Procedure

  • Thread starter Thread starter Alex Stevens
  • Start date Start date
A

Alex Stevens

Hi All,

I'll do my best to try and describe the issue I'm having with my
application.

I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
data layer which communicates with the database, and provides classes to the
front end application.

Most of the classes are filled using the stored procedures which fill
datatable which fill properties. However, I have a method in my login class,
which calls the ADO.net method ExecuteNonQuery on a stored procedure to
update a table (I've copied in the procedure T-SQL and the end of the mail -
it's nothing complicated!!!).

Intermittently, the method will not run, and errors on the ExecuteNonQuery
line with the error "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding."

Before this runs, there is a method which fills a datatable using the Fill
Method of a Data Adapter and this runs everytime. However, the
ExecuteNonQuery does not run, and errors out.

When this does occur, I can open query analyzer and if I try to run any
stored procedures in the database, I get a timeout. Even Altering a stored
procedure times out. I can use other databases and run stored procedure in
them with no problems, but this specific database causes timeouts.

After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
be fine for a while (couple of hours), then it will start to timeout again
for 5-10 mins.

What could be causing this, as it's database specific. Is there any way, I
can have the Database rebuild itself and clear out any dodgy temporary
tables????
Any method which uses a data adapter runs fine all the time, but the
ExecuteNonQuery fails intermittently.

I'm a bit lost really.

Any help is appreciated.

Thanks

Alex


******* Stored Procedure *********
ALTER PROC proc_Utility_UpdateUserLoggedIn

@UserID int,
@LoggedIn bit = 0

AS

SET NOCOUNT ON

UPDATE tblUser
SET
LastLogin = GetDate(),
LoggedIn = @LoggedIn

WHERE UserID = @UserID
*********************************
 
Hi

Timeouts are caused by SQL not getting it's work finished in time. This
indicates a blocking or performance issue.

Make sure that you have appropriate indexes in place, run sp_who2 and look
for any processes that are blocked by other processes when you run your query
through your VB code or Query Analyser.

Regards
Mike
 
I'm not using transactions in the stored procedure..........?


Miha Markic said:
Hi Alex,

Are you ending properly transactions?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Alex Stevens said:
Hi All,

I'll do my best to try and describe the issue I'm having with my
application.

I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
data layer which communicates with the database, and provides classes to
the
front end application.

Most of the classes are filled using the stored procedures which fill
datatable which fill properties. However, I have a method in my login
class,
which calls the ADO.net method ExecuteNonQuery on a stored procedure to
update a table (I've copied in the procedure T-SQL and the end of the
mail -
it's nothing complicated!!!).

Intermittently, the method will not run, and errors on the ExecuteNonQuery
line with the error "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding."

Before this runs, there is a method which fills a datatable using the Fill
Method of a Data Adapter and this runs everytime. However, the
ExecuteNonQuery does not run, and errors out.

When this does occur, I can open query analyzer and if I try to run any
stored procedures in the database, I get a timeout. Even Altering a stored
procedure times out. I can use other databases and run stored procedure in
them with no problems, but this specific database causes timeouts.

After say 5 minutes the attempt to run the ExecuteNonQuery works and it
will
be fine for a while (couple of hours), then it will start to timeout again
for 5-10 mins.

What could be causing this, as it's database specific. Is there any way, I
can have the Database rebuild itself and clear out any dodgy temporary
tables????
Any method which uses a data adapter runs fine all the time, but the
ExecuteNonQuery fails intermittently.

I'm a bit lost really.

Any help is appreciated.

Thanks

Alex


******* Stored Procedure *********
ALTER PROC proc_Utility_UpdateUserLoggedIn

@UserID int,
@LoggedIn bit = 0

AS

SET NOCOUNT ON

UPDATE tblUser
SET
LastLogin = GetDate(),
LoggedIn = @LoggedIn

WHERE UserID = @UserID
*********************************
Click to expand...
Click to expand...
 
As you can see the stored procedure (at the bottom of the original email) is
an extremely simple update procedure.

The only thiing that has been run before that on the SQL database is a
SELECT statement (posted at the end) which returns a resultset also
implementing the NOLOCK to stop the table being locked on a simple read.

In query analyzer, Select statements work fine, but updates don't.
Make sure that you have appropriate indexes in place, run sp_who2 and look
for any processes that are blocked by other processes when you run your query
through your VB code or Query Analyser.
Click to expand...

The table has an int Primary Key, when the application is started, I
sometimes get two processes one which has a batch end time, and one which
has a batch end time of 01/01/1900 (presumably a Null).

I can't track down where this erroneous process comes from (it is on the
database in question), because it doesn't appear when I set through the
code.

How can I check to see if a process is blocking the UPDATE process????


Thanks

Alex

******Stored Proc*******
ALTER PROC proc_Get_User

-----------------------------------------------------------------
-- Date Created: 28 April 2004
-- Procedure Description: Standard Get procedure.
-- Created By: Alex Stevens
-- Template version 1.0 Dated: 28/04/2004 12:41:58
-- Generated by CodeSmith 2.5
--
-- Used in Classes:
--
--------------------------------------------------------

@UserID int = Null,
@UserName varChar(20) = Null

AS

BEGIN

SELECT dbo.tblUser.*
FROM dbo.tblUser (NOLOCK)
WHERE (@UserID IS NULL OR UserID = @UserID) OR
(@UserName IS NULL OR UserName = @UserName)
ORDER BY UserName

END

*************
 
Back
Top