Unexplained timeouts when running stored procedures

Question

Background - I have a website & a windows scheduled job which are a part of an MSI and get installed on the same server. The website is used by the end-user to create some rules and the job is scheduled to run on a daily basis to create flat files for the rules created by end-user. The actual scenarios are way more complex than explained above.

Problem (with the website) - The website is working fine most of the times, but some times it just wont load the rule creation page - and the exception being logged it 'query timeout or SQL server not responding'

Problem (with the job) - The job is behaving just like the website and fails some times with the exception - 'query timeout or SQL server not responding'

What I've tried -

• I've added 'Connection Timeout' to the SQL connection string - doesn't seem to help with the logging - which would tell me if it was a SQL connection timeout or a query timeout.
• I've also run the stored procedures which are called by the website & job - and ALL the stored procedures complete well within the business defined timeout of 3600 seconds. The stored procedures actually complete in under a minute.
• I've also run SQL profiler - but the TRACES also didn't help me - though I could see a lot of transactions but I couldn't justify something being wrong with the server.

What I seek - Are there any other reasons which could cause this? Is there something which I could look for?

Technology - SQL Server 2008 R2, ASP.Net, C#.Net

Restrictions - The code details can't be revealed due to client confidentiality, though I'm open to questions - which I'd try to answer keeping client confidentiality in mind.

Note - There is already a query timeout (3600s) & Connection Timeout (30s) defined in the applicaiton config file.

Answer 1

So, I tried a few things here and there and was able to figure out root cause -

The SQL stored procedure was joining 2 tables from 2 different databases - one of which had varying number of records - these records were being updated/inserted by a different (3rd party) job. Since the time of the 3rd party job and my job was not same - no issue came up due to table locks, but the sheer volume of records caused my job to timeout when my timeout was not enough.

But, as I said I've given the business standard command timeout of 3600 seconds - somehow Enterprise Library was overriding my custom timeout with its own default command timeout of 30s - and hence the C# code part would come throw an exceptions even before the stored procedure had completed executing.

What I did - This may be of help for some of us -

1. I removed the reference of Enterprise Library from the project
2. Cleaned up my solution and checked into SVN.
3. Then cleaned up SVN as well.
4. I didn't build the application after removing Enterprise Library reference - obviously it wouldn't build due to reference errors.
5. After that, I took a clean checkout and added Enterprise Library again.

Now it seems to work even with varying number of records.