SQL timeout errors
I only have a problem with 1 out of 30 sites that we run on the W2003 web server.
Probably about 25% of the day, the website keeps returning: SQL timeout errors on various SQL connections (using ODBC)
I checked and updated the ODBC drivers to the latest I could find (3.5.x?) And I also check the SQL server to see if it has problems (another server is running on the same network connected via Gb LAN)
IIS log files return "[Microsoft] [ODBC_SQL_Server_Driver] Timeout_expired"
I ran into a problem this morning, so I tried to restart the SQL server to see if it was a load-related issue or something, but the site kept generating these errors after about 20 minutes after the restart (although all of our other sites were working at this stage) - then it stopped timing and now works again.
I tried expanding the SQL connection timeout for the website to see if it changed something, but it didn't seem to do anything.
This site has been running for 3 years continuously without problems and we haven't changed anything on our servers for a long time - it started on Christmas, but since then it has become more and more regular.
I went through all the code to make sure the DB Connections are open / closed properly (classic ASP site) and ensured that it doesn't open too many concurrent connections to no avail and I'm starting to run out of ideas .... anyone ?
My only thought is to switch to OLEDB connection instead of ODBC. But before I do that, I wanted to check if there was anything else that I missed and I could try first.
Thank you Advance!
Charles.
You mentioned changing the timeout of SQL connections, but it is not clear what exactly the timeout is: is it database related or are the queries too long?
One tool that can help is Sql Profiler. Limit this to a user or database experiencing timeouts. If query length is an issue, this should give you an idea of what queries are taking a long time to complete.
If the connection itself is disconnected, check the current activity in Management Studio. This shows the number of open connections; if they are large, there is probably a leak. You can check this during development by running a connection string containing:
MAX POOL SIZE=2;
This will rapidly leak leaks from the connection pool.
Mitch Wheat's suggestion is very good, inaccurate stats can really degrade performance over time. This query can tell you if your statistics match:
SELECT
object_name = Object_Name(ind.object_id),
IndexName = ind.name,
StatisticsDate = STATS_DATE(ind.object_id, ind.index_id)
FROM SYS.INDEXES ind
order by STATS_DATE(ind.object_id, ind.index_id) desc
a source to share
Since you haven't changed the code, there is nothing wrong with your connections.
Find out why SQL queries are taking so long. It's hard to say it's easier. You may find that you have a deadlock between your code that hasn't changed and some other use of the database that has changed. But you need to keep track of this and not touch the working code that hasn't changed.
a source to share
If these things above don't work, I would check your Page File (PF) file usage in Task Manager and see where it is. You may get timeout errors due to out of virtual memory. If it looks like the top, consider making it larger. I get a lot of SQL timeout errors sporadically and in different parts of my application. It turned out to be a performance issue. I would recommend at least 4000 MB of page file depending on the amount of bar you have on your server.
a source to share