none
PSI: SqlException timeout occurred RRS feed

  • Question

  • Hi,

    On our project server 2010 we get SQL timeout errors when we try to open a large project from Project Professional 2010. MPP tries to load the project but just stops after about one minute with a message in the status bar "Closing project 'SomeProject'". No error message, nothing in the exception log at the client.

    We use:
        Project Server 2010 version 14.0.4763.1000
        Project Professional 2010 version 14.0.5128.5000 (32-bit)

    The ULS log on the WFE sharepoint server shows that there are SQL timeout exceptions in the PSI while reading project data (in office/project/server/webservices/WinProj/ReadProjectNonCoreData2). We are using a lot of task custom fields (20+) and the projects can be big (thousands of records). Is there a way to increase the timeout values for SQL commands done from the PSI?

    The log contains the following exception messages:

    PWA:http://gdfn-ipl-01/PWA, ServiceApp:Project Server Service Application, User:GDFN\quint.mouthaan, PSI: SqlException occurred in DAL:  <Error><Class>0</Class><LineNumber>0</LineNumber><Number>-2</Number><Procedure></Procedure>  <Message>  System.Data.SqlClient.SqlError: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  </Message>  <CallStack>   
     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)   
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)   
     at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()   
     at System.Data.SqlClient.SqlDataReader.get_MetaData()   
     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)   
     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)   
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)   
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)   
     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)   
     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)   
     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.ExecuteStoredProcedureDataReader(String storedProcedureName, CommandBehavior commandBehavior, SqlParameter[] parameters)  </CallStack>  </Error>

    --------------------------------------------------------

    PWA:http://gdfn-ipl-01/PWA, ServiceApp:Project Server Service Application, User:GDFN\quint.mouthaan, PSI: [T:d8351afe-583b-4065-ad47-b2659cefc97d][U:15948122-2f12-418f-b7a4-5f0a1c4f8c79][S:ed6c0d9c-03d1-448d-9114-e512d8a0f88d][D:GDFN-CX14-02\IPL Dev][J:00000000-0000-0000-0000-000000000000][P:1f4c0e4c-13f5-473d-95ac-95944caec3be][PS_AC][3] System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.   
     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)   
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)   
     at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()   
     at System.Data.SqlClient.SqlDataReader.get_MetaData()   
     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)   
     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)   
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)   
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)   
     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)   
     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)   
     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.ExecuteStoredProcedureDataReader(String storedProcedureName, CommandBehavior commandBehavior, SqlParameter[] parameters)   
     at Microsoft.Office.Project.Server.DataAccessLayer.Generated.WinProjDalBase.ReadNonCoreDataForProjectReader(Guid PROJ_UID, Int32 MOD_REV_COUNTER, Boolean GET_PROJECT_DATA, Boolean GET_RES_DATA, Boolean GET_TASK_DATA, Boolean GET_ASSN_DATA, Boolean GET_BASELINES, Boolean GET_CUSTOM_FIELD_VALUES, Int32 MAX_BASELINE_NUM, Boolean GET_DELETIONS, Boolean BCM)   
     at Microsoft.Office.Project.Server.BusinessLayer.WinProj.ReadProjectNonCoreData(WinProjDal winProjDal, Guid projGuid, Int32 modRevCounter, Dictionary`2 cntrIdMap, Boolean getCustomFieldValues, Boolean getBaselines, Boolean getDeletions, List`1 tableNameList, List`1 resultSets, Boolean bcmMode)   
     at Microsoft.Office.Project.Server.BusinessLayer.WinProj.ReadProjectNonCoreData2(Guid projGuid, Int32 projType, Int32 datastore, String oldVersionStampStr, Int16 dataFormatVersion, UInt16[] containerIds, Int32 lcid, Guid sessionGuid, String sessionDescr, Boolean isDataCompressed, String winprojBuildNumber, Boolean forceSendAll)

    ----------------------------------------------------

    Exception occurred in method Microsoft.Office.Project.Server.BusinessLayer.WinProj.ReadProjectNonCoreData2 System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.   
     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)   
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)   
     at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()   
     at System.Data.SqlClient.SqlDataReader.get_MetaData()   
     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)   
     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)   
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)   
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)   
     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)   
     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)   
     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.ExecuteStoredProcedureDataReader(String storedProcedureName, CommandBehavior commandBehavior, SqlParameter[] parameters)   
     at Microsoft.Office.Project.Server.DataAccessLayer.Generated.WinProjDalBase.ReadNonCoreDataForProjectReader(Guid PROJ_UID, Int32 MOD_REV_COUNTER, Boolean GET_PROJECT_DATA, Boolean GET_RES_DATA, Boolean GET_TASK_DATA, Boolean GET_ASSN_DATA, Boolean GET_BASELINES, Boolean GET_CUSTOM_FIELD_VALUES, Int32 MAX_BASELINE_NUM, Boolean GET_DELETIONS, Boolean BCM)   
     at Microsoft.Office.Project.Server.BusinessLayer.WinProj.ReadProjectNonCoreData(WinProjDal winProjDal, Guid projGuid, Int32 modRevCounter, Dictionary`2 cntrIdMap, Boolean getCustomFieldValues, Boolean getBaselines, Boolean getDeletions, List`1 tableNameList, List`1 resultSets, Boolean bcmMode)   
     at Microsoft.Office.Project.Server.BusinessLayer.WinProj.ReadProjectNonCoreData2(Guid projGuid, Int32 projType, Int32 datastore, String oldVersionStampStr, Int16 dataFormatVersion, UInt16[] containerIds, Int32 lcid, Guid sessionGuid, String sessionDescr, Boolean isDataCompressed, String winprojBuildNumber, Boolean forceSendAll)   
     at Microsoft.Office.Project.Server.Wcf.Implementation.WinProjImpl.ReadProjectNonCoreData2(Guid projGuid, Int32 projType, Int32 datastore, String oldVersionStampStr, Int16 dataFormatVersion, UInt16[] containerIds, Int32 lcid, Guid sessionGuid, String sessionDescr, Boolean isDataCompressed, String winprojBuildNumber, Boolean forceSendAll) Source: .Net SqlClient Data Provider Server: GDFN-SQL-01\G431128

    ------------------------------------

    Watson bucket parameters: Microsoft Project Server 2010, ULSException14, 070f6ea0 "project server", 0e00129b "14.0.4763.0", ea364808 "system.data", 0200c627 "2.0.50727.0", 4a274a01 "thu jun 04 06:13:53 2009", 00002481 "00002481", 0000002c "0000002c", c46b8b58 "sqlexception:-2:0", 30303030 "0000"

    --------------------------------------------

    And finally:

    Leaving Monitored Scope (ExecuteWcfOperation:http://schemas.microsoft.com/office/project/server/webservices/WinProj/ReadProjectNonCoreData2). Execution Time=30543,1374657952

    The execution time exceeds 30 seconds, which I believe is the default timeout for SQL commands.

     

    I hope somebody can help us, because this is a critical issue.

    Thanks,

    Quint Mouthaan.

    Thursday, December 30, 2010 11:07 AM

Answers