Solved

SQL transaction timeing out problem

Posted on 2009-07-02
11
150 Views
Last Modified: 2012-05-07
Hi i am using vb.net vs2008. I have a command object which executes a simple update statement on a table that has 100 000 records in it.
For some reason today it has started timeing out which it has not done before
I then took the UPDATE statement and ran it on SQL management studio and it is timing out there now as well.

UPDATE tblACBTrans set Status = 'Rejected' , UnpaidDate = '2009/07/02' where AutoID = 2829442;
I have created a separate Index non-clustered Unique index on my field AutoID which is also my primary key for this table. Still no joy

This is my code in vb to execute the statement, i tried setting the CommandTimeOut property to 60 but did  not help

       Dim command As New SqlCommand(SQLString, cn)
        command.CommandTimeout = 60
        Try
            If cn.State = ConnectionState.Broken Or cn.State = ConnectionState.Closed Then cn.Open()
            If cn.State = ConnectionState.Open Then
                command.ExecuteNonQuery()
                ModifyData = "Success"
            End If
       Catch exSQL As SqlException
         msgbox exsql.message.tostring
        Finally

            command.Dispose()
            command = Nothing
        End Try

thanks, any help would be great

thanks
0
Comment
Question by:CraigLazar
[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
11 Comments
 
LVL 15

Assisted Solution

by:mohan_sekar
mohan_sekar earned 100 total points
ID: 24765173
Probably somebody has got exclusive access to the table? Did you check event/sql server log? Could you restart SQL Server and try?
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 24765255
i have taken a backup of hte database of the server and onto my local machine, so i have been restoring the database and trying to fix the problem - so i do not think some one has got exclusive access to the table

thanks
0
 
LVL 15

Expert Comment

by:mohan_sekar
ID: 24765517
Ok, try the following command on the database

sp_updatestats
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 4

Author Comment

by:CraigLazar
ID: 24765572
hi mohan_sekar:
what does the sp do?
0
 
LVL 15

Expert Comment

by:mohan_sekar
ID: 24765888
It updates the statistics. Since you said the database was restored from a backup, I thought updating stats might help. Did it?
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 24766261
no unfortunately not
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 200 total points
ID: 24766309
>I have created a separate Index non-clustered Unique index on my field AutoID which is also my primary key for this table. Still no joy

remove that newly created index  and try to run the same statment from the management studio

UPDATE tblACBTrans set Status = 'Rejected' , UnpaidDate = '2009/07/02' where AutoID = 2829442

on a new query window run sp_Who2 and check whether there is any entries with a value in the 'blked_by' column


also if it again times out, try with a different Auto_Id
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 24766403
Hi,
Ok if i run my query from management studio it seems to be running fine. However from my vb app it is timing out. I restored yesterdays database and ran the same process using my vb app and it ran with no problems. So i cannot help thinking that there could possibly be some sort of table corruption in todays database compared with yesterdays. I have made now code adjustments to the application, so its very strange.
Are there sp's that i can run to redo/check table indexes, similar to Access's compact and repare database functions?

thanks
0
 
LVL 7

Assisted Solution

by:wilje
wilje earned 200 total points
ID: 24766439
What indexes do you currently have on that table?  With either a clustered index on AutoID, or the non clustered index you created - that query should be very fast.
That would lead to either a blocking issue - or maybe a parallelism issue.
You could try using OPTION(MAXDOP 1) with the query to see if that helps.
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 24793772
Hi Guys,

Ok i eventually found a work around for my problem. I cannot explain why it was doing it but for some reason it was timing out on my SQL reader adapter, so i then switched to using a data table as my container for the records based on my sql statement and it seems to have fixed it. very very odd


thanks anyway for the help

cheers
0
 
LVL 4

Author Closing Comment

by:CraigLazar
ID: 31599259
thanks for your help
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 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