locked
SQL Server Timeout Expired when accessing System Jobs in CRM 2011 after upgrade from CRM4.0 RRS feed

  • Question

  • Hi All,

    I upgraded CRM 4.0 to CRM 2011 and having nearly 45GB Database. I am trying to open System Jobs and it is giving me the following error. I added DWORD for OLEDBTimeout and ExtendedTimeout by mentioning 86400 in Decimal. But it is throwing me this error even after did IISRESET.

    Can anyone please tell me how to resolve it. Thanks in Advance


    Rudravarma Batta

    Thursday, September 20, 2012 10:50 AM

All replies

  • It maybe that its trying to read too many rows of data & therefore is timing out.

    If you could put a SQL Server profile trace on the database you could see which query it is timing out on.

    I'll guess that its to do with the number of workflows & if so you should look at using the following scripts to clean up the database. 

    http://blogs.msdn.com/b/benlec/archive/2009/04/13/sdk-cleanup-script-for-unwanted-workflow-instances-stuck-in-waiting-state.aspx

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;968520


    John Grace (Founder, North52) Simplifying CRM & xRM development
    How? Check out the Free Community Edition of Formula Manager at, North52

    Wednesday, September 26, 2012 12:05 PM
  • Hi John,

    Thanks for your response. I ran query to delete Asynchronous records but It took 2 days to run even  there is no luck. This is the error.

    Source: MSCRMPlatform &  EventID: 24066

    StackTrace: [CrmException: SQL timeout expired.]
       at Microsoft.Crm.Application.Platform.ServiceCommands.PlatformCommand.XrmExecuteInternal()
       at Microsoft.Crm.Application.Platform.ServiceCommands.RetrieveMultipleCommand.Execute()
       at Microsoft.Crm.ApplicationQuery.RetrieveMultipleCommand.RetrieveData()
       at Microsoft.Crm.ApplicationQuery.ExecuteQuery()
       at Microsoft.Crm.Application.Platform.Grid.GridDataProviderQueryBuilder.LoadQueryData()
       at Microsoft.Crm.Application.Platform.Grid.GridDataProviderQueryBuilder.LoadData()
       at Microsoft.Crm.Application.Controls.GridUIProvider.PrepareGridData()
       at Microsoft.Crm.Application.Controls.GridUIProvider.Render(HtmlTextWriter output)
       at Microsoft.Crm.Application.Components.UI.Grid.DataGrid.RenderData(HtmlTextWriter output)
       at Microsoft.Crm.Application.Components.UI.Grid.DataGrid.RenderInnerHtml(HtmlTextWriter output)
       at Microsoft.Crm.Application.Components.UI.Grid.DataGrid.Render(HtmlTextWriter output)
       at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children)
       at System.Web.UI.Page.Render(HtmlTextWriter writer)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

    [HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown.]
       at System.Web.UI.Page.HandleError(Exception e)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest()
       at System.Web.UI.Page.ProcessRequest(HttpContext context)
       at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
       at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

    Thanks in Advance

    RVB


    Rudravarma Batta

    Wednesday, September 26, 2012 3:19 PM
  • Wow, 2 days that's a long time.

    Was it the SQL queries from this MS support article that you ran?

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;968520

    It would be useful to run this command & let us know what the results are?

    Select Count(AsyncOperationId)from AsyncOperationBase WITH (NOLOCK)
    where OperationType in (1, 9, 12, 25, 27, 10) 
    AND StateCode = 3 AND StatusCode IN (30,32)




    John Grace (Founder, North52) Simplifying CRM & xRM development
    How? Check out the Free Community Edition of Formula Manager at, North52

    Wednesday, September 26, 2012 3:34 PM
  • Hi John,

    I ran the same SQL Query from MS Support Article.

    The result for your query is 1967240.

    Is there any way that I can fix this SQL timed out error.

    Regards

    RVB


    Rudravarma Batta

    Thursday, September 27, 2012 9:49 AM