Solved

Timeout Error

Posted on 2006-06-26
12
474 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 143

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 143

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

839 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