Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL timeout error

Posted on 2014-01-12
15
Medium Priority
?
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +2
15 Comments
 
LVL 143

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 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
 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

670 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