Avatar of Chris Jones
Chris JonesFlag for United States of America asked on

SQL timeout error

Hello,

i have a .net console application and using MSSQL server 2008 on a clustered box. i have an automated process that runs on a server and builds information in my database. the application runs fine on my development boxes i can run it 100 times. But when i run it on my automated server is starts to get timeouts.

========================================================================

ERROR

(0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.


SQL STATEMENT
UPDATE SDL_DropList SET DualCredit = 'Y' WHERE PIDM = '330348'
========================================================================

As you see a very simple SQl statement but i run that command about 3000 items on the database for different users
.NET ProgrammingMicrosoft SQL Server 2008Visual Basic.NET

Avatar of undefined
Last Comment
Chris Jones

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

what is the explain plan for that query?
if it shows full table scan, make sure you have a index on PIDM.
is the field PIDM numerical? [if yes, remove the quotes]
you may want to add this to the query:
AND ( DualCredit IS NULL OR DualCredit <> 'Y' )

next steps would be to look at the transaction log configuration and backup of the prod sql server...
Surendra Nath

simple work around

try to put increase your timeout period in the SQL Server connection string you are using to connect to the SQL Server.

The other way around is by doing this

1) create a table, let us call it XYZ...
2) instead of the above update statement...., insert all the PIDM that needs to be updated into this table.
3) configure an SQL job to run at every 10 minutes....
 with the below update statement

UPDATE S
SET dual_credit = 'Y'
FROM SDL_droplist S
JOIN XYZ Z
ON S.PIDM = Z.PIDM

DELETE X
FROM XYZ X
JOIN SDL_droplist S
ON X.PIDM = S.PIDM
AND S.dual_credit = 'Y'

Open in new window


by doing this, the load on the SQL server will be reduced definetly.. but the problem the update will run asynchronously now.. i.e. after you trigger the update it will take 10 minutes to apply in the database.
ASKER
Chris Jones

Hello  @Surendra GantiPosted  I have completed a sample project with the following statement you suggested I still get timeouts but can run it form MSSAL manager fine. also I upped the timeout to 120 and it just take longer to tell me timeout
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Chris Jones

Hello Guy Hengel PIDM is not an index field but it is set to be numeric. NOTE that the application runs fine with no timeouts form my development box and when I run the process manually. The application also has access to run the file and the SQl statements but after about 40 minutes into my program it starts to timeout.
Surendra Nath

as you said you have done a project with my statamet...

Can you please give us what exactly you used over there and also how you implemented it.
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Chris Jones

Hello  Surendra Ganti

I set the two SQL statements up in a simple .NET application and ran it. I am not sure if I said this before with my original program I always run a truncate instead of a delete not sure if that mattered. What would you like me to provide exactly Surendra Ganti
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Surendra Nath

STEP 1:

As hengel said create an index on PIDM column of your table.
use the below script to do the same

CREATE NONCLUSTERED INDEX IX_Test1
    ON SDL_DropList (PIDM);

STEP 2:

Check if the time outs are gone, if that is not the case

STEP 3:

Give us the connection string, how you are connecting to SQL?
ASKER
Chris Jones

Hello,

SQl connection string

    <add
    name="SDLT"
    connectionString="Data Source=HASQLSERVER;Initial Catalog=StudentDropListP;Persist     Security Info=True;User ID=droplistw;Password=******;Connection Timeout=120"
    providerName="System.Data.SqlClient" />

Open in new window

Surendra Nath

do this

  <add
    name="SDLT"
    connectionString="Data Source=HASQLSERVER;Initial Catalog=StudentDropListP;Persist     Security Info=True;User ID=droplistw;Password=******;Connection Timeout=12000"
    providerName="System.Data.SqlClient" />

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Nasir Razzaq

>The application also has access to run the file and the SQl statements but after about 40 minutes into my program it starts to timeout.

Do you constantly issue update statements in those 40 minutes?
ASKER
Chris Jones

no the the time that is happens is starting to get random and sometimes it never times out.
Nasir Razzaq

Do you have a DBA? Is there anything else going on on the sql server when this happens?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

As Guy has mentioned, changing the Connection timeout is irrelevant on long running queries and changing it to 12,000 is questionable at best.  12,000 > 3 hours.

Please don't take this the wrong way, but you need to hire a competent and reliable SQL Server consultant who can monitor your queries and pin-point the exact query and time that it is occurring.
ASKER
Chris Jones

thank you i think this was the issue