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.

I've recently updated an MSSQL server from 2000 version to 2005, to make use of UDFs and tweak some results in a system. The thing is that we don't have the source code.

So, I replaced the SQL version, and every worked fine... except when we have to do a large query. I'm getting this error:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I've searched about it, and what I get is that it use to be a CommandTimeout issue that I have to solve programmatically, as it is supposed to be in the client side, but this is weird, because it always worked even with big queries.

Is there any way to remove any kind of timeout? The system is completely internal and only a few people uses it, so there's no risk of outages... I prefer a query running forever that this annoying messages.

Thanks in advance!


share|improve this question
add comment

1 Answer

These command timeouts are defined by code so if you don't have the source then you're a bit out of luck with removing it.

What you should do is put a SQL Profiler trace on a session and find out exactly what is causing the long running query. You'll likely be able to find something that is not right on the database side (missing indexes, bad joins, etc).

share|improve this answer
    
But the long running query is actually normal. Why this worked fine with SQL Server 2000 (no timeouts at all) and it doesn't with 2005? That's what doesn't make sense to me. Nothing on the client side have changed. –  Gonzalo Larralde Sep 16 '11 at 21:06
    
I understand that the length of time is expected. SQL 2005 architecture is quite a bit different than SQL 2000, though. That's why I'm saying do a Profiler trace. You'll see exactly what is going on server side and thus will get an idea of why things are different now that it's on 2005. You can't remove the timeout, so you're left with inspecting the server. –  squillman Sep 16 '11 at 21:12
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.