An application that transfers users’ time from our time recording system to our accounts system was failing to run.  A support call had been logged with the supplier, but it was taking too long to get a fix.  Looking in the error log for the application I could see:

01/11/2010 15:15:00 Connected to: DTE
01/11/2010 15:15:30 Error Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

In the same folder as the failing exe was a config file.  Inside the config file were the URLs for two web services as well as some stored procedure names.  I checked the webservice URLS and they loaded fine, so the error was probably with one of the stored procedures.  I knew the application was written in C#, so I used the Red Gate .NET Reflector http://www.red-gate.com/products/reflector/ to open the exe so I could take a look at the source code.  I searched for “Connected to:” and went to the section of code that was causing the problem.  I could now see the stored procedure the application was running.  I started SQL Server Management Studio and opened the stored procedure to see what it was doing.  It took the form:

INSERT TABLENAME

SELECT…..

Running the SELECT statement took 31 seconds, checking this against the error log showed the application was timing out in 30 seconds.  A quick check of the source code in .NET Reflector showed the code was using a SqlDataAdapter Select command.  The default timeout for the Select command is 30 seconds.  I altered the stored procedure to run in batches, which took less that 30 seconds.  Running the application showed this had fixed the problem.  Next step, get the application developers to introduce a more permanent fix….