Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lior
Starting Member
2 Posts |
 Posted - 2008-06-27 : 12:48:43
|
Hello all,Our website has been running for years, and we haven't had code changes in months. A few weeks ago we moved our DB from SQL Server 2000 to a new SQL 2005 server, and now we're starting to get this on updates/inserts/deletes error 3-4 times a day:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding Once it happens for the first time, it happens with any query that updates/inserts/deletes records. How can the same code work most times and then other times cause this error? Regular select queries don't cause this error.The problem goes away by itself eventually, or it goes away after we restart the MSSQL service. Any help would be GREATLY appreciated! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-27 : 12:51:42
|
| Is there a chance that any of the object thats involved in update,insert or delete operation gets locked due to some other activity happening on it simulatneously which causes the triggered action to be timed out. |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-06-27 : 13:35:39
|
| Chances are this type of error is not going to be caused by a code change (since you haven't made a change), rather it's all about the traffic and workload on the database. Procedures that have been running fine for years might no longer be optimal given a heavier workload. Bottom line is you're dealing with a contention problem at the database level.As far as how to resolve the problem you need to determine what's running when the timeout occurs. Then evaluate the queries running and determine the problem. It might be a heavy update, outdated statistics, outdated indexes, etc. Heck, it could be that on your old server the databases might have been on different disks and now they are not (so they share the same hardware resources for reading data).I hope this gives you a good starting point. |
|
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-06-27 : 15:10:31
|
| did you change the Database Compatibility Level to 90 and is the insert /updates through a SP or AD-HOC in APP.Maninder |
|
|
|
lior
Starting Member
2 Posts |
Posted - 2008-06-27 : 16:05:15
|
quote:
Originally posted by maninder
did you change the Database Compatibility Level to 90 and is the insert /updates through a SP or AD-HOC in APP.Maninder
No, I didn't change the Database Compatibility Level, it was at 80. I changed it to 90 now, thanks for the tip...The inserts/updates aren't in a SP, it's code in the aspx application. |
|
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-06-27 : 16:24:19
|
| http://forums.asp.net/t/903456.aspxManinder |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-06-27 : 16:33:34
|
quote:
Originally posted by maninder
http://forums.asp.net/t/903456.aspxManinder
Seriously, anyone that recommends to set the timeout of a query against the database to infinite (that's what setting it to zero does) does not understand what they are talking about. I'm sorry, but look at the queries, look at the processes and figure out what the real problem is. Fix it and move on.All this does is mask the true problem of bad coding and/or database design. |
|
|
|
|
|
|
|
|
Sporadic timeout expired in DB updates