timout error on server running vb .net routine

[Bernie Yaeger]

I'm getting the following error - not always, only randomly - when I run a
certain routine (the routine is a simple executenonquery and it appears to
complete despite the message) against sql server 2000:

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

Anyone have any idea what causes this and how to avoid it?

Thanks for any help.

Bernie Yaeger

[William Ryan eMVP]

Bernie:

There are two timeouts in SQL Server that you want to think about.
Connection Timeouts and Command Timeouts. Connection Timeouts occur when
you try to open a connection but are unable to for longer than the default
period of time. You can only adjust this in the connectionstrin g b/c the
TimeOut property is read-only...hence, once you create your connection
string, this can't be changed. Now, once you have a connection, theh
Command object has a timeout too. So, if you set the Command.TimeOut to 30
seconds and the query took 35 seconds, you'd get an exception. This is
easier to deal with b/c you can just bump it up.

The big problem though is why it's taking so long to execute. Most inserts
should take a fraction of a second for one record so I'd probably look to
Sql Profiler and see what's happening. Unfortunately, htere's not just one
thing that causes Command Timeouts, there are tons of things it could be.
Connection timeouts are a little easier to deal with b/c it's usually that
the server is down, no connections are available, your network cable is
loose, stuff like that.

Does it happen predictably? If so, that may help isolate it. Also, is it a
test server or a production server? If it's a production server, look to
see what's happening in the background (reindexing for instance or
updatestats may cause a temp lock on the db and may be causing this.

If you can, run a trace and see if that doesn't tip you off.

HTH,

Bill
"Bernie Yaeger" <be*****@cherwe llinc.com> wrote in message
news:uR******** ******@TK2MSFTN GP09.phx.gbl...

I'm getting the following error - not always, only randomly - when I run a
certain routine (the routine is a simple executenonquery and it appears to
complete despite the message) against sql server 2000:

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

Anyone have any idea what causes this and how to avoid it?

Thanks for any help.

Bernie Yaeger

[Bernie Yaeger]

Hi Bill,

Thanks for your response. The issue was this: before undertaking important
routines I copy a 750,000 table to a bu table - this runs in a stored proc
that I call via executenonquery . I solved the problem by simply changing
the commandtimeout property to 600 (10 minutes).

Tx for your help, as always.

Bernie

"William Ryan eMVP" <do********@com cast.nospam.net > wrote in message
news:er******** ******@TK2MSFTN GP10.phx.gbl...

Bernie:

There are two timeouts in SQL Server that you want to think about.
Connection Timeouts and Command Timeouts. Connection Timeouts occur when
you try to open a connection but are unable to for longer than the default
period of time. You can only adjust this in the connectionstrin g b/c the
TimeOut property is read-only...hence, once you create your connection
string, this can't be changed. Now, once you have a connection, theh
Command object has a timeout too. So, if you set the Command.TimeOut to 30 seconds and the query took 35 seconds, you'd get an exception. This is
easier to deal with b/c you can just bump it up.

The big problem though is why it's taking so long to execute. Most inserts should take a fraction of a second for one record so I'd probably look to
Sql Profiler and see what's happening. Unfortunately, htere's not just one thing that causes Command Timeouts, there are tons of things it could be.
Connection timeouts are a little easier to deal with b/c it's usually that
the server is down, no connections are available, your network cable is
loose, stuff like that.

Does it happen predictably? If so, that may help isolate it. Also, is it a test server or a production server? If it's a production server, look to
see what's happening in the background (reindexing for instance or
updatestats may cause a temp lock on the db and may be causing this.

If you can, run a trace and see if that doesn't tip you off.

HTH,

Bill
"Bernie Yaeger" <be*****@cherwe llinc.com> wrote in message
news:uR******** ******@TK2MSFTN GP09.phx.gbl...

I'm getting the following error - not always, only randomly - when I run a certain routine (the routine is a simple executenonquery and it appears to complete despite the message) against sql server 2000:

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

Anyone have any idea what causes this and how to avoid it?

Thanks for any help.

Bernie Yaeger