• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

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
0
Chris Jones
Asked:
Chris Jones
  • 6
  • 4
  • 2
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
Surendra NathTechnology LeadCommented:
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.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Chris JonesLead Application Web DeveloperAuthor Commented:
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.
0
 
Surendra NathTechnology LeadCommented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just a note on "timeout", there are 2 timeout values:
* connection timeout => which is usually specified, defaults to 30, and has NO influence at all on queries taking long time
* command timeout => usually not specified, defaults also to 30, and THIS one is cancelling your query if it takes longer ...

however, before playing with those settings, rather check why it takes so long.

and as you say "it's not indexed", create the index, and see.

now, when you say "it runs fine from my dev box", do you mean you also have a dev SQL box, or do you connect to the same box.

finally: if a database is running fine for some time, and then starts to "time out", the usual culprit is that your sql box is eating up too much memory from the windows machine.
typically this can be solved by reducing the max memory setting from the sql instance from the usual "unlimited" down to +- 60% of the physical RAM of the machine.
say the windows box has 10GB or RAM, you set that setting to 6000 (setting is in MB), if your windows box is not hosting any other large application.
  => after this, you might want to restart the sql instance ...

which also indicates that your query, as such, is not the problem...


hope this helps
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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
0
 
Surendra NathTechnology LeadCommented:
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?
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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

0
 
Surendra NathTechnology LeadCommented:
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

0
 
CodeCruiserCommented:
>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?
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
no the the time that is happens is starting to get random and sometimes it never times out.
0
 
CodeCruiserCommented:
Do you have a DBA? Is there anything else going on on the sql server when this happens?
0
 
Anthony PerkinsCommented:
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.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
thank you i think this was the issue
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now