Asked by:
SQL Server Timeout Expired when accessing System Jobs in CRM 2011 after upgrade from CRM4.0

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://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