SQL transaction timeing out problem

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
            If cn.State = ConnectionState.Broken Or cn.State = ConnectionState.Closed Then cn.Open()
            If cn.State = ConnectionState.Open Then
                ModifyData = "Success"
            End If
       Catch exSQL As SqlException
         msgbox exsql.message.tostring

            command = Nothing
        End Try

thanks, any help would be great

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Probably somebody has got exclusive access to the table? Did you check event/sql server log? Could you restart SQL Server and try?
CraigLazarAuthor Commented:
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

Ok, try the following command on the database

Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

CraigLazarAuthor Commented:
hi mohan_sekar:
what does the sp do?
It updates the statistics. Since you said the database was restored from a backup, I thought updating stats might help. Did it?
CraigLazarAuthor Commented:
no unfortunately not
Aneesh RetnakaranDatabase AdministratorCommented:
>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CraigLazarAuthor Commented:
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?

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.
CraigLazarAuthor Commented:
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

CraigLazarAuthor Commented:
thanks for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.