none
.NET 2.0 client to SQL Server 2005 Timeout exception while running a stored procedure RRS feed

  • Question

  •  

    Our environment:

    Client:             Windows XP SP2 (current patches)

    Application:     Windows Forms application written in VB.NET 2.0

    Connection:     Uses System.Data.SqlClient

    Server:            Windows Server 2003 (current patches)

    Database:       Sql Server 2005 Standard Edition SP1 (9.00.2198.00)

     

    We are currently experiencing a problem in that when we execute a stored procedure in SQL Server 2005 from within a windows application running VB.NET 2.0 we receive the exception

     

    "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

     

    This stored procedure is a select query which joins a few tables together and returns the matching records based on the parameters passed in. When we run the sproc with the exact same parameters inside SQL Server Management Studio the results come back immediately. We are only retrieving about 1000 records when the exception occurs and when we change the parameter value to retrieve less that 300 records, the stored procedure returns the records to the .NET client application successfully.

     

    We have run into a similiar problem in the past with another "select" stored procedure and found that restarting the instance solves the problem for a few days and then the problem will come back. Our IT Department determined that we were running short of memory and upgraded from 4 Gig to 8 Gig. The available memory shows a little more than 2 Gig when the problem is happening.

     

    All other connections to the database from the application appear to work just fine with no delays that are noticeable.

     

    Any help would be appreciated

     

    Thanks,

     

    Lonnie

    Monday, November 19, 2007 8:29 PM

All replies

  • This is a stab in the dark, But we ran into the same problem. Perhaps have a look at the Log file. try deleting it or creating a new one.

     

     

    Tuesday, December 4, 2007 10:49 AM
  • Hi Lonnie,

     

    In this case you can set the timeout of the Command Object before executing the stored procedure.

    like -

    objSQLCommand.CommandTimeout = 180

     

    Perhaps this may help you out.

     

    Harsha

    Friday, March 21, 2008 9:19 AM
  • You also need to verify your indexes... the number of records is very low, so it could be that your select query is not adequately optimized.

     

    Get a workload file using SqlProfiler while your application runs and then from Management Studio select Tools / Database Engine Tuning Advisor and tune your indexes.

     

    Bruno
    Saturday, March 29, 2008 12:02 AM
  • Hi All,

    Resetting CommandTimeout property of your SqlCommand object may gives the right direction to the question. you can set commandtimeout as Haesha said. Hope you can work out the problem.

     

    Regards

    Jing

     

    Monday, May 26, 2008 7:50 AM
  • Hi All,

    The solution you are giving is to increase the "wait time" before the error acutally occurs. I've set it to 5 minutes but still the error occured. I've also noticed that during the 30 (default) seconds before timeout expires, the sql server on my pc is taking up 1 whole processor.
    I ran a SQL Profiler, took the query string from the command that timed out on the .NetSqlClient and ran it in SQL Developer Studio and it ran instantly. The result has 1,200 records. Maybe that's the problem. Any real fix?
    Friday, June 27, 2008 4:02 PM
  • I am experiencing the same problem, I have a stored procedure (which incidentially relies on a table valued function using common table expressions in sqlserver 2005).  If I run this stored proc using Management studio it takes less than a second (around 200 ms), when I run this stored proc from code (c#) it takes 39 seconds to complete.  This is one of the most bizarre problems I've ever run across and am looking for someone to point me in the right direction instead of just saying "try changing the command timeout" (which I've tried).

    thanks,
    -Matt

    BTW... This stored proc runs fine with different parameters and I've done this exact kind of thing before, when it times out it is only returning about 130 rows which shouldn't really be the problem.  Also....I've tried this using inline sql, execute query, using table adapters, etc... no matter what method I use, it times out.
    Friday, October 17, 2008 2:35 AM
  • A little more information:
    When I restart the server this problem goes away for awhile, now I've hit the problem again and I'm trying to see what is going on before throwing up my hands and restarting the server again, I'm looking for service packs also, but I didn't really want to randomly install patches before understanding the bug.

    -Matt
    Wednesday, October 29, 2008 12:31 AM
  • SET ARITHABORT ON

    seems to be the solution to that kind of problem.

    Friday, August 7, 2009 4:19 PM