none
Timeout expired problem RRS feed

  • Question

  • Hi,

    firstly some introduction to our software:
    we have website, written in ASP.NET, hosted in ISS on Windows Server 2008 R2, web edition, as our storage we use MS SQL 2008 R2, Web Edition. We use LINQ to SQL.

    Main problem: 
    we are getting timeout expired exceptions, when executing sp that search on fulltext indexed columns.

    Details:
    On our sql we have mutlible databases. Each database have spSearch (which is same on each db). This spSearch searches with CONTAINS/CONTAINSTABLE on data, that are full-text indexed. Normaly this spSearch takes about 1-3 seconds executing.

    But when we execute this spSearch throught our web site, using ADO.NET 
    executeStoredProcedure, from beggining everything works great. Results are return normaly, in 1-5 seconds.

    But after some random time (or some number of calling spSearch???) from our website, we start to get timeout expired exception. If one user gets this exception, on the database, in which user is, all users gets this error from now on, that try to use this spSearch on the database. If we execute this spSearch in management studio on the db, it executes normaly, in 1-3 seconds, without any timeout error. If some user executes this sp on other db, it executes normaly.

    What seems to help is to rebuild fulltext catalog on the db, if we do this, all users from our site can now search with spSearch without any problem. But after some time, everything starts again.

    Exception:

    "StackTrace":" at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)\r\n
     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)\r\n
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()\r\n
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)\r\n
     at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()\r\n
     at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n
     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n
     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)\r\n
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)\r\n
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n
     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n
     at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n
     at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n
     at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n
     at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)\r\n
     at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)\r\n
    
    ---
    after this is our codes, that executes sp...



    Qustions:
    The biggeest mystery is, that how it is possible, that from our site we are unable to search, but from management studio it executes right away at the same time?

    We are out of ideas, so we appreciate any help.

    Thanks and regards

    Thursday, October 11, 2012 9:37 AM

All replies

  • Hi vterbium,

    Welcome to MSDN Forum.

    When you execute the stored procedure in SQL Server Management Studio, you needn't to create connection to the database, so the execution has no problem. I think the issue is caused by failing connect to the database from your website. Please check your connection string and try to connect to the database from Server Explorer, or you can increate the time of timeout property.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, October 12, 2012 3:25 AM
    Moderator
  • Hi Allen,

    thanks for reply,

    but problem is, that from beginning, everything works ok, so I think connection string should be allright.

    We use classic connection string with usernmae and password + we specified one more paramater: Max Pool Size = 300

    Nothing else is in connection string. We aslso tried to simulate this behaviour locally, but I was generating "abnormal amout of request" and wasnt able to generate this timeout error (and I use locally only express sql)

    Do you have any other idea?

    Thanks and regards
    ytb

    Friday, October 12, 2012 7:52 AM
  • I would always try and read uncomitted as the 1st thing. Either add it in the procedure, or add it in linq to sql.

    using (
                   var t = new TransactionScope(TransactionScopeOption.Required,
                                                new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
                {

    Friday, October 12, 2012 10:18 AM
  • Hi vterbium,

    I'm afraid I've no idea now. I will do more research on it.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, October 15, 2012 9:18 AM
    Moderator