Solved

SQL timeout error

Posted on 2014-01-12
15
414 Views
Last Modified: 2014-03-21
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
Comment
Question by:Chris Jones
  • 6
  • 4
  • 2
  • +2
15 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39774833
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39774981
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 39775015
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 39775024
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39775093
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39775134
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 39775201
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39775215
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 39776414
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39777942
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39806694
>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
 
LVL 1

Author Comment

by:Chris Jones
ID: 39806709
no the the time that is happens is starting to get random and sometimes it never times out.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39806946
Do you have a DBA? Is there anything else going on on the sql server when this happens?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39807717
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
 
LVL 1

Author Closing Comment

by:Chris Jones
ID: 39945097
thank you i think this was the issue
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now