Integrated Security: timeout

Showing posts with label timeout. Show all posts

Friday, March 30, 2012

Intermittent Timeout - ExecuteNonQuery On Stored Procedure

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 Alex,
Are you ending properly transactions?
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
"Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> 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
"Alex Stevens" wrote:
> 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
> *********************************
>
>|||I'm not using transactions in the stored procedure........?
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%23Au3lrUoEHA.3592@.TK2MSFTNGP14.phx.gbl...
> Hi Alex,
> Are you ending properly transactions?
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
> "Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
> news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> > 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
> > *********************************
> >
> >
>|||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.
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
*************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F92E78E3-70CF-44C2-824D-4C686642307C@.microsoft.com...
> 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
> "Alex Stevens" wrote:
> > 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
> > *********************************
> >
> >
> >sql

Wednesday, March 28, 2012

intermittent locks

We are experiencing problems intermittently.

Users report problems of various types including timeout messages. We investigate and find a user has acquired a lock which is blocking other users.

We contact the user and they have usually completed their activity and are not always aware of any problem despite them owning a lock.

When the user logs out of the application the lock clears and the system returns to normal.

Indexes have been rebuilt, auto update statistics is on.

Does anyone have any suggestions? :cool:The first thing I'd do is start watching for locks to determine how often they occur, and ask the users if they know of any activity that causes the problems associated with locking/blocking (that may give you clues about what you need to watch).

Once you understand what you are looking for, run a trace using SQL Profiler at the same time as a Performance Monitor trace watching for locking/blocking. The PerfMon trace will show you when the problem occurs, the Profiler trace will show you what caused the problem.

When you understand the cause of the problem, you can then look at changing the application to avoid the problem.

-PatP|||We are trying to gather more information from the users to track this down.

Anecdotally users believe that they have finished their activity and are simply still logged on or are running searches.

We haven't needed to kill a session, the user simply logs off.

It's almost as though the lock has been taken but not released when the activity has finished.

Does this sound likely/possible? If so any ideas what could be causing it?|||Does this sound likely/possible? If so any ideas what could be causing it?Yes, it sounds rather likely.

I'd suspect that the problem is something that the code is doing "behind the curtains" that the user is completely unaware of, but is still causing havok. Until you can compare the two traces (or provide LOTS of additional insight into your application and server configuration), we can only guess.

-PatP

Monday, March 26, 2012

Intermittent connection timeout / general network error

We have a SQL 2005 clustered server (Microsoft Cluster Services) that is queried from 4 IIS6 Windows Server 2003 frontends. Each frontend runs both classic ASP apps connection with SQL Server ODBC and .NET2 apps connecting with System.Data.SqlClient. Ocassionaly we get a string of errors/timeouts opening a connection lasting maybe 2 minutes.

One the classic ASP apps we log one of these two errors:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Timeout expired

on the ADODB.Connection.Open

On the .NET2 apps we log

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

StackTrace at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)

Source .Net SqlClient Data Provider

Any ideas for further debugging?

What does your connection string look like? Please try add "Connect Timeout=120" or "Connection Timeout = 120" in it, and see wether the problems go away.

Thanks!

Ming.

|||<add name="default" connectionString="Data Source=server;Initial Catalog=db;Integrated Security=False; uid=user; password=pw" providerName="System.Data.SqlClient"/>

for .NET via TCP/IP

The regular ASP websites are having problems as well though using ODBC.

Upping the timeout seems like a band-aid at best and may lead to connections piling up and making the situation worse. There isn't a good reason for communication between the frontends and db server over a private Gigabit link to take over 30 seconds to connect.
|||You can use your HOSTS file to define the servername-IP address combination to resolve DNS names. If you have a DNS server, define this in your DNS server. Try a simple test. Try installing any SQL Server client tool(Query Analyzer, Enterprise Manager, etc) on the server running your .NET app. Connect to your database server using this client tool. If it takes more than 5 seconds to connect, there's something wrong with your network|||

Did you get an answer for this?

we are facign similar issues.

We have 4 way cluster sql server 2005 on SP2

|||No I haven't had any answers. The problem is definitely more pronounced during heavier traffic so I wasn't able to debug much during the lull during the summer.

Intermittent connection timeout / general network error

We have a SQL 2005

clustered server (Microsoft Cluster Services) that is queried from 4 IIS6

Windows Server 2003 frontends. Each frontend runs both classic ASP apps

connection with SQL Server ODBC and .NET2 apps connecting with

System.Data.SqlClient. Ocassionaly we get a string of errors/timeouts opening a

connection lasting maybe 2 minutes.

One the classic ASP

apps we log one of these two errors:

Microsoft OLE DB Provider for ODBC

Drivers (0x80004005)
[Microsoft][ODBC SQL Server

Driver][DBNETLIB]General network error. Check your network

documentation

Microsoft OLE DB Provider for

ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server

Driver]Timeout expired

on the

ADODB.Connection.Open

On the .NET2 apps we

log

Message Timeout expired. The timeout

period elapsed prior to completion of the operation or the server is not

responding.

StackTrace at

System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection

owningObject)
at

System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection

owningConnection)
at

System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection

outerConnection, DbConnectionFactory connectionFactory)
at

System.Data.SqlClient.SqlConnection.Open()
at

System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo

sqlPartitionInfo)

Source .Net SqlClient Data Provider

Any ideas for further debugging?

What does your connection string look like? Please try add "Connect Timeout=120" or "Connection Timeout = 120" in it, and see wether the problems go away.

Thanks!

Ming.

|||<add name="default" connectionString="Data Source=server;Initial Catalog=db;Integrated Security=False; uid=user; password=pw" providerName="System.Data.SqlClient"/>

for .NET via TCP/IP

The regular ASP websites are having problems as well though using ODBC.

Upping the timeout seems like a band-aid at best and may lead to connections piling up and making the situation worse. There isn't a good reason for communication between the frontends and db server over a private Gigabit link to take over 30 seconds to connect.|||You can use your HOSTS file to define the servername-IP address combination to resolve DNS names. If you have a DNS server, define this in your DNS server. Try a simple test. Try installing any SQL Server client tool(Query Analyzer, Enterprise Manager, etc) on the server running your .NET app. Connect to your database server using this client tool. If it takes more than 5 seconds to connect, there's something wrong with your network|||

Did you get an answer for this?

we are facign similar issues.

We have 4 way cluster sql server 2005 on SP2

|||No I haven't had any answers. The problem is definitely more pronounced during heavier traffic so I wasn't able to debug much during the lull during the summer.

|||We have a similar issue with our Clustered SQL Server 2005. We are seein it a lot in a VB 6 executable, but I have gotten the errors in Server Management Studio, too. We get one of two errors:
Timeout expired
or
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

We have run Sysinternals Process Monitor on a PC that receives these errors and have seen a NAME NOT FOUND result on queryopen operation for CLUSAPI.DLL, RESUTILS.DLL and ntdsapi.dll. The path it is looking in is the application path C:\Program Files\ROAS. These DLLs don't exist in this folder, but do exist in C:\Windows\System32.

The first two are MS Clustering DLLS and i asume with a name like ntdsapi the other is a server OS DLL.

Friday, March 30, 2012

Intermittent Timeout - ExecuteNonQuery On Stored Procedure

Intermittent Timeout - ExecuteNonQuery On Stored Procedure

Wednesday, March 28, 2012

intermittent locks

Monday, March 26, 2012

Intermittent connection timeout / general network error

Intermittent connection timeout / general network error

Integrated Security

Blog Archive

About Me