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
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
Last Comment
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
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.
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'
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
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
ASKER
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.
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.
Can you please give us what exactly you used over there and also how you implemented it.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
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?
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
Hello,
SQl connection string
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" />
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" />
>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?
Do you constantly issue update statements in those 40 minutes?
ASKER
no the the time that is happens is starting to get random and sometimes it never times out.
Do you have a DBA? Is there anything else going on on the sql server when this happens?
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.
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
thank you i think this was the issue
.NET Programming
The .NET Framework is not specific to any one programming language; rather, it includes a library of functions that allows developers to rapidly build applications. Several supported languages include C#, VB.NET, C++ or ASP.NET.
137K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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...