Morning All,
I'm having trouble with query timeouts in MS SQL Server 2000. It is used as a backend for Intuitive ERP, a rather old ERP system based on MS Access.
My problem is that, when performing the planning calculations, a process that preforms a large number of queries, we are experiencing an SQL timeout error.
I initially used the profiler to determine the offending query (a DELETE query), and performed it manually before running again, which seemed to work. However, on subsequent runs it would timeout at a later stage. Details are a little sketchy here as my manager took over, but from what she tells me, I would guess at it being a SELECT or INSERT query.
Performing any of these queries directly on the server (using Enterprise Manager) results in a timely response every time, there seems to be no reason for the timeout.
I realise this isn't much to go on, so I guess my question is more what sort of things can cause a query to timeout? We've had issues with disk space in the past, but that isn't the problem here. I've also checked the network connection: pings come back in about 1ms, so that shouldn't be the issue (besides, the profiler shows the failing queries as taking an inordinate amount of time, suggesting iit is something on the server end).
Thanks for your time.
Best Answer
As opposed to you running the statements ad-hoc, the application is probably running them all in the context of a user transaction, so there may be much more lock accumulation in that case. Concurrent user activity in the database can then result in blocking, which in turn can cause timeouts if things get held up too long.
Unfortunately, since we're talking about a 3rd-party application, I'm not sure there's much you can do on the SQL Server side. If the application exposes any timeout settings, it may be a good idea to increase them, perhaps substantially. It may also help to run these kinds of batch processes at off-peak times if possible.