Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

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.

share|improve this answer
    
Thanks Jon, We've tried running it with no one else suing the system with no success. We've also set the timeout to 0 (unlimited), but it's still giving us the timeout error... perhaps there's another setting somewhere that we're not privy to. We're managing to limp along at the moment, and it's only a month or two before this system is replaced anyway. I think it's just throwing its last tantrum. Thanks for yor help. –  gsteinert Aug 14 '12 at 7:32
    
@gsteinert: Applications sometimes don't implement timeout settings correctly (because I've neeeeeever made that mistake myself... lol). Yeah, if you can see the end of it, I'd just as soon let it limp along until it goes away. –  Jon Seigel Aug 16 '12 at 2:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.