Hello there. My company converted to SQL Server 2008 R2 as the backend for its inventory database about a month ago, and since then, we've been having Timeout Expired errors with error code S1T00 occasionally. The frontend we're using to interface with SQL Server is Microsoft Access. We can't seem to pinpoint the cause of the errors, and I've been trying various fixes, though they don't seem to be producing results. The other oddity about the problem is that the error will appear on occasion and halt whatever process is running, but upon retrying the process, it continues without any complaints. Other times, however, the error will stop all functionality for a very long amount of time.
Fixes attempted so far:
1. Ensuring that ports are opened on the server firewall for SQL Server; ports are opened, and SQL Server is set to listen on those ports.
2. Checked the authentication scheme for SQL Server (currently using NTLM); I've read that using Kerberos is more secure, but I'm not entirely sure how to set it up correctly. I'm essentially the only IT person at this company, and networking isn't quite my forte, so if this seems like the way to get this fixed, support on enabling Kerberos functionality would be greatly appreciated
3. Checked the ODBC DSN we're using to connect; the connection tests using the DSN always seem to return successful results, even when the problem is ongoing.
4. Setting up a task to run an ipconfig /flushdns command regularly on each computer; this was done after I noted that, in the case of one of the long-running service halts, I tried flushing the DNS cache just on a whim, and the process proceeded to run along fine after that. However, either the interval of the task isn't set low enough, or this actually isn't doing anything, as the problems don't seem to have abated.
5. Configuring various timeouts; the SQL Server timeouts are set at 600 seconds, and the timeouts in Access for ODBC are set to 600 seconds as well. The oddity here is that the error will appear immediately when a function is called whenever it does happen, so it doesn't seem to be an actual timeout error. (For clarity, the error usually appears as such: Someone pushes a button, and the error immediately appears).
6. Voodoo rituals.
So far, I haven't had any luck getting rid of this error, and while it isn't harmful most of the time, the occasions when it does stop our functionality for a good while are understandably not ideal. Would anyone have any idea how to fix this problem?
For further information, the server has 16GB memory, SQL Server is configured to use 14GB of that memory, and other processes only use up around 1-1.5GB of memory at any given time. The server is a quad-core with good processing speed, and the data we're working on isn't particularly huge; our largest table is about 300,000 rows, and the vast majority of queries that we use on a day-to-day basis complete in less than a second. The longest-running queries we use take roughly 5 seconds at the worst. We support 10 or fewer users at any given time simulatenously. DMV queries don't show anything particularly wrong with memory, processor, or I/O pressure. I'm quite stumped, personally.