SQL Server Timeout expired when update, delete or insert record but not when select

Question

Dear all,

I am using sql server management studio 2008. whenever I want to select the data, I have no problem. However, whenever I want to use delete, update, or insert sql statement, I got timeout expired error message. It doesn;'t happen everytime.once or twice a day.... below is the error message. Can someone please help? thanks heaps!



Answer 1

did you enable cascade update , delete on the references.

this might happen when you have the reference keys on a big table with cascading settings.

---

Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

Answer 2

Thanks for the reply Chandra.

What do you mean by cascade update or delete on the references?

Answer 3

Hi JayNoob,

Regarding to the error message you provided, which seems is a command timeout issue, basically SQL Server took too long to execute. This is one of the more "mystical" parts of client/server programming. You can try to set the timeout to be longer which means that it takes longer for the client to realize that the server.  

Alternatively you can look into improving performance on the server. It depends on the task and importance of the operation.
For more information about SqlCommand.CommandTimeout property , you can refer to this article:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(VS.80).aspx

Meanwhile please refer to this blog about the troubleshooting steps with  the same error message as yours:  http://blogs.msdn.com/b/ialonso/archive/2007/12/04/sql-execution-error-timeout-expired-the-timeout-period-elapsed-while-opening-view-from-ssms.aspx

---

Regards, Amber zhang

Answer 4

hi amber, 

It says timeout, but im not sure if it is really the case. I just need to update one row directly by executing delete from table 1 where id = 1. The cost is only 0.02. I execute it directly at sql server. and it doesn't happen everytime. Is it possible that the table is being locked or something by other users who are using front end to update the database?

Answer 5

Hi JayNoob,
Regarding to the error message timeout, which is a general issue. If you remote connection which might caused by the network issue. Did you run this execution success? How did you get its cost is 0.02?

Meanwhile if there is a constraint on ID as a foreign key (and no index) it can be quite costly.) How many indexes are on the table? Index B-tree rearrangement is a costly part of the DELETE statement. You can try to definitely check the query plan, and look at an index on ID on table. If there is without any more information,  it might be doing a table scan on the table and costing you greatly.

As you mentioned, it may be related to the blocking. It looks like the row is being locked but after the update the lock is lost and the commit is doing nothing. If it is an issue of dead lock the execute sp_who2 while running your query. This sp will show you all the process and it will also show you some process is waiting for some other process or not.
For more information, please check this discussion for sp_who2 as below:
1. http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/74c5b42e-96a0-4004-a8b7-86707f5d045b
2. Tips to improve delete statement may be it will be helpful for you

Additional, Executing DELETE on Large RecordSet may cause the TimeOut as well. For more information, please refer to this Blog.

---

Regards, Amber zhang

Answer 6

Hi Amber,

I know the cost because I press ctrl+m and tried to delete from SQL Server.

SO in a summary:

1. my user is trying to update or insert a record by clicking a submit button remotely. If the record exist, it will update. if the record is not exist it will do insert.

2. I don't use any foreign key. I have used primary key for cluster index.

3. as they can't update or insert, therefore I tried simple delete statement ( I connect to the databsae server directly, not remotely). sometimes it just doesn't execute. However when I select, it executes properly.

Is it possible the database is corrupted? How do I know if it is corrupted? If a table is locked, can you still perform select statement on that table? Thanks.

Jaynoob

Answer 7

Hi JayNoob,

>> How do I know if it is corrupted? If a table is locked, can you still perform select statement on that table?

In my opinion, you can try to use SQL Server Profiler and trace flags to capture Dead lock information.
You can create the desired trace using Profiler against a test box and then export the trace definition as a script that you can customize and run on the target server.  For more information, please refer to 
1. http://msdn.microsoft.com/en-us/library/ms191006.aspx
2. http://weblogs.sqlteam.com/dang/archive/2007/12/16/Avoid-Causing-Problems-with-Profiler.aspx

Additional please refer to these articles
1. INF: Understanding and resolving SQL Server blocking problems
2. Analyzing Deadlocks with SQL Server Profiler

---

Regards, Amber zhang

Answer 8

Thanks Amber for your generous help. :) will try to do it.