Upgrade to SQL Server 2005 - ODBC Timeout Problem


  • Hi, on a web application that I develop, I just upgraded to a new database server running SQL Server 2005 and Windows Server 2003 Enterprise Edition.  The previous database server ran SQL Server 2000 and Windows Server 2003 Standard Edition.  Since this upgrade, the application has been causing "timeout" errors all over the application.  I've been stumped.  Here's the error mssage:

     Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
    [Microsoft][ODBC SQL Server Driver]Timeout expired

    I'm hoping there is some sort of a setting that can be modified to increase the maximum number of connections between the web server and database server.  This is a fresh version of Windows Server 2003 and SQL Server 2005.  Any support that can be provided will be greatly appreciated.

    Wednesday, October 18, 2006 2:33 PM

All replies

  • If you have connection pooling enabled you will eventually have to increase the pool size or disable the pool size if you don´t want to use it.

    HTH, Jens K. Suessmeyer.

    Wednesday, October 18, 2006 5:26 PM
  • Increase the value for connectionTimeout property of the connection object. The default value is 30 sec

    You might need to tune the way your app retrieves data. Rebuild all indexes in the database the app is using and update the stats with full scan.

    You could as well generate a profiler trace and see what takes longer than the timeout period.

    Thursday, October 19, 2006 2:26 AM
  • One thing you can do is to configure matching protocols between your server and your client. When clients connect to the server, it will try the protocols in sequence until one success or all fail. Which protocols are enabled on the server? Depends on your client app, c:\windows\system32\cliconfg.exe can configure protocols for MDAC. For SNAC, you can use SQL Configuration Manager. By default, we first try TCP then NP.Local connection will try Shared Memory first.
    Thursday, October 19, 2006 7:25 AM