Solved

Timeout Error

Posted on 2006-06-26
12
473 Views
Last Modified: 2008-01-09
Hi Guys,

I keep getting a timeout error in sql server when I try to run a delete or update query.  Is there any way to increase this timeout threshhold or turn it off all together?

Thanks,

Ryan


0
Comment
Question by:dchau12
  • 7
  • 2
  • 2
  • +1
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16986148
first thing to try would be to check why the query takes that much time to cross the timeout.
also, you need to specify which coding language you use to help you with setting timeouts
0
 

Author Comment

by:dchau12
ID: 16986173
It is not a complicated query.  It is something like this:

delete from table 1
where month_1 = 5
and year_1 = 2006

It should delete about a hundred records.  The coding language is vb.net.

Ryan
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16986181
having a (clustered) index on the field year_1 + month_1 should solve the timeout...
note: 1 index with both fields in it
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16986739
If you have added the index Angel referred to...and still having issues:

In vb you can set the COMMAND TIMEOUT value.

How big is the table you are trying to delete from?
0
 

Author Comment

by:dchau12
ID: 16986793
Its only got about 800 records in it, but about 300 columns.  

0
 

Author Comment

by:dchau12
ID: 16986802
I run the exact same query from enterprise manager and it runs instantly.  Wierd...
0
 

Author Comment

by:dchau12
ID: 16986838
Here is the exact code I am using to execute the query:

'**** Transaction set information for ComericaSurvey *******
        Dim ConnectionString2 As String
        ConnectionString2 = Utils.getSurvConnectionString
        Dim con2 As New SqlClient.SqlConnection(ConnectionString2)
        con2.Open()
        Dim tr2 As SqlClient.SqlTransaction = con2.BeginTransaction()
        Dim cmd2 As New SqlClient.SqlCommand
        '***********************************************************

        strqry = " DELETE from tbl_ComConsumer_Survey " & _
                 " WHERE QreportMonth_1 = " & Calculate.txt_AnalysisMonth.Text & " " & _
                 " AND QreportYear_1 = " & Calculate.txt_AnalysisYear.Text & ""
        cmd2 = New SqlClient.SqlCommand(strqry, con2, tr2)
        cmd2.ExecuteNonQuery()

I tried the following directly after the cmd2 sql command declaration and it had no effect.  I still get the timeout error:

cmd2.CommandTimeout = 0
I also tried
cmd2.CommandTimeout = 3600

0
 

Author Comment

by:dchau12
ID: 16986917
AngelIII,

I tried creating the clustered index, but I already have a primary key on that table, and it says that I cannot have more than one clustered index per table.  When I try to remove the clustered property from the primary key, it tell me that sql server cannot have more than 249 nonclustered indicies or column statistics on one table.

Ryan
0
 

Author Comment

by:dchau12
ID: 16986951
I think I figured it out.  I had a previous transaction set that I had not commited before I tried to execute this one with a new connection and transaction object.  That seemed to be the problem.  When I commented out the code from the previous transactions, it ran fine.

Ryan
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 500 total points
ID: 16990042
for reference.....    if a small statement like this is timing out it is often due to blocking,  running sp_who2 will show you if it is and who is blocking.   This is the case here.
0
 

Author Comment

by:dchau12
ID: 16992734
Good call ShogunWade
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 16995644
You're welcome
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 29
Loop to go backward 90 days 2 18
convert null in sql server 12 33
SQL Server - Set Field Values ito Zero Based on Related Table 4 24
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 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