Original blog Post : https://blogs.msdn.microsoft.com/crminthefield/2014/09/29/table-bloat-due-to-workflow-log-entries/
One of the more common customer issues that has been coming up often lately is table bloat due to a large volume of completed or canceled workflow entries in the organization. There are three main tables that are impacted by this workflow history retention:
The second table of concern is the AsyncOperationBase table. This table tracks your asynchronous processing job execution (system jobs, workflows, plug-ins, etc). Similar to the POA table, as this table grows larger and contains millions of records, you will most likely start to see a performance decrease on the system. The last table is the WorkflowLogBase table. This table stores your detailed workflow execution history, including detailed information on the execution of each step within the workflow. Again, if this table grows into the millions of records, you may start to see performance issues with the system. Now that we’ve covered the purpose and concerns of these tables, let’s discuss the issue at hand. As mentioned previously, table bloat for these three tables is a concern. It’s probably common sense that a large number of completed workflows could directly affect the AsyncOperationBase and WorkflowLogBase tables since they’re used for storing execution and logging history of the workflows. So, why are we seeing table bloat on the POA table as well? This occurs because workflow executions result in the creation of shared access entries in the POA table for the WorkflowLog record of the owner of that workflow. This means that every time a workflow is executed there are associated WorkflowLog records created in the POA table. Depending on the number of steps within the workflow, this could result in a high ratio of POA records being created for a single workflow execution. The more often workflows are executed, the faster all three of these tables will grow. There are ways to reduce the number of WorkflowLog entries within these tables and we’ll get into that in a bit. How can you tell if your table bloat is due to a large number of workflow entries? Below are a few simple queries that can be executed to provide you with a record count on the tables of concern. The AsyncOperationBase table will typically have less records than the POA and WorkflowLogBase tables. Take the results of all three queries into consideration when determining if you are seeing this issue in your environment. Disclaimer: I recommend running these queries during non-Production hours to minimize any performance impact on the system AsyncOperationBase Query – This query will display a row count of records (operation types defined in the comments of the query below) with a status of completed or canceled and a state of completed. /*AsyncOperationBase OperationTypes, StatusCodes and StateCodes Operation Types: Workflow Expansion Task = 1 Collect SQM Data = 9 PersistMatchCode = 12 FullTextCatalogIndex = 25 UpdateContractStates = 27 WorkFlow = 10 Status Codes: Suspended, Waiting = 10 Succeeded = 30 Canceled = 32 State Codes: Completed = 3 Suspended (Waiting) = 1 Replace "OrgName" below with your CRM organization*/ USE [OrgName_MSCRM] GO SELECT Name, OperationType, StatusCode, StateCode, COUNT(Name) AS TotalRecordCount FROM AsyncOperationBase WITH(NOLOCK) WHERE StatusCode IN (30, 32) AND OperationType IN (1, 9, 12, 25, 27, 10) GROUP BY Name, OperationType, StatusCode, StateCode ORDER BY TotalRecordCount DESC POA Query – The query below will provide you with a breakdown of the total number of POA record counts per record type. This will allow you to pinpoint which record types are consuming the majority of your POA table. If the record type “WorkflowLog” contains a large number of records, then you may be experiencing the issue described above. /*Replace "OrgName" below with your CRM organization*/ USE [OrgName_MSCRM] GO WITH POAS as (SELECT ObjectTypeCode, COUNT(ObjectTypeCode) AS TotalRecordCount FROM [dbo].[PrincipalObjectAccess] POA WITH (NOLOCK) GROUP BY ObjectTypeCode) SELECT MTDSE.Name, POAS.ObjectTypeCode, TotalRecordCount FROM POAS JOIN ( SELECT [Name],[ObjectTypeCode] FROM [MetadataSchema].[Entity] GROUP BY [Name], [ObjectTypeCode] ) MTDSE ON MTDSE.ObjectTypeCode = POAS.ObjectTypeCode ORDER BY POAS.TotalRecordCount DESC WorkflowLogBase Query – This query will return the total number of records in the WorkflowLogBase table with a status of Completed, Canceled or Waiting. /*WorkflowLogBase Status codes: Status 1 = In Progress Status 2 = Succeeded Status 3 = Failed Status 4 = Canceled Status 5 = Waiting Replace "OrgName" below with your CRM organization*/ USE [OrgName_MSCRM] GO SELECT wflb.Status, COUNT(wflb.Status) AS TotalRecordCount FROM WorkflowLogBase wflb WITH(NOLOCK) WHERE Status IN (2,4,5) GROUP BY wflb.Status ORDER BY TotalRecordCount DESC Resolution There are a couple different ways to remediate this issue. Listed below are separate remediation options based on whether or not your company has business requirements for retaining a history of the workflow execution history. Option 1 – Purge all successfully completed and canceled workflow history If you have no business requirements for keeping a history of the successfully completed and canceled workflows, consider using the remediation steps below: 1. Enable the Automatically delete completed workflow jobs (to save disk space) option located in the WorkFlow properties>Administration tab>Workflow Job Retention section: Note: You would need to do this for all workflows in the organization 2. Purge all workflow history for successfully completed and canceled workflows using one of the options outlined below:
If you have business requirements around keeping a history of the successfully completed and canceled workflows, it is still highly recommended to determine what that retention period is and purge any completed and canceled workflows older than that timeframe. Keeping an indefinite amount of workflow history will cause these tables to continue to grow out of control slowing down performance. Review the remediation steps below: 1. Create a scheduled Bulk Delete job to run during non-Production hours with the action of purging successfully completed and canceled workflow jobs older than a specific timeframe (no more than 1 month is recommended). Below is an example of search criteria to use for this type of job: Note: If you’d like to first determine the number of records that would be purged based on a specific timeframe, you can use the three queries listed above and add a WHERE clause for the CompletedOn and/or ModifiedOn columns. See example below. --WHERE clause to display only results with CompletedOn and ModifiedOn older than 30 days WHERE CompletedOn <= dateadd(day,-30,getdate()) AND ModifiedOn <= dateadd(day,-30,getdate()) Additional methods for controlling POA table bloat by modifying security configuration, shared privilege proliferation and unnecessary cascaded behaviors can be found below. Additional POA Performance Recommendations http://blogs.msdn.com/b/crminthefield/archive/2011/06/09/principalobjectaccess-performance-recommendations.aspx Last week we had new requirement to add Global Close/Cancel Button to all CRM entity to help user go back to previous page in case of current window and if it's new window then close window. here's steps to do this task : 1- In Ribbon workbench Open solution which has "Application Ribbon" Entity , Add new button "Close/Cancel" with below properties 2- Create new Command and add javascript action to call javascript function
3- Here's functions to close or go to previous window based on current window or iframe window. here function checks for "pagemode" param which has "Iframe" value in case of New window CancelRecord: function () { var IsNewWindow = getQueryStringParameterValue("pagemode"); if (IsNewWindow == 'iframe') { window.closeWindow(); window.closeCurrentWindow(); } else window.history.back() } // Get query string param value getQueryStringParameterValue: function (name) { name = name.replace(/[\[]/, "\\[").replace(/[\]]/, "\\]"); var regex = new RegExp("[\\?&]" + name + "=([^&#]*)"), results = regex.exec(location.search); return results === null ? "" : decodeURIComponent(results[1].replace(/\+/g, " ")) } Modify Survey View
5. From right hand side Screen select "Edit the Current View" 7. on modify view screen ,provide column selection ,sort ,group and "SAVE" it.
Create New View
you’ve set up a Forms library in SharePoint 2010 and have published an InfoPath 2010 form to it. You’ve got requirements that state that users in one particular SharePoint group are only allowed to add items to the Forms library. Your form has a submit button on it and you name your files with a unique user/timestamp value the first time a form is submitted (Submit rules) and overwrite the files on subsequent submits (Edit rules).
Since you only want certain users to be able to add items to the library, you set up an “Add Items” permission level and grant that permission to the Forms library for your SharePoint group. When you submit the form, you get “The form cannot be submitted to the specified SharePoint list or document library. The SharePoint location may be read-only or you may not have permissions to access it.” The group has permissions to add items to the Form library, so what’s up with that? When following the Auto-Generating Filenames pattern, make sure you check the “Stop processing rules when this rule finishes” checkbox after you’ve defined your Submit rules and your Edit rules. If you don’t, your Submit (add) rules will fire, and the form will be saved successfully. Then, your Edit rules will fire and will attempt to update the form. Your users don’t have the update permission, so the “you may not have permissions” message fires. Hope this help !! To deploy Custom Web application inside CRM 2013 we need to follow below steps
1.Make following changes in the web.config <configuration> <configSections> <remove name=“crm.authentication“ /> </configSections> <system.web> <httpModules> <clear/> </httpModules> 2.after deploying the application within the ISV folder, place the following dlls within its bin folder 1.AntiXssLibrary.dll 2.Microsoft.Crm.dll 3.Microsoft.Crm.Sdk.dll 4.Microsoft.Crm.Platform.Sdk.dll 5.Microsoft.Crm.Sdk.Proxy.dll 6.Microsoft.Xrm.Sdk.dll We can find these dll’s inside ../Microsoft Dynamics CRM/Server/Bin folder. For WCF service we had to make following changes to the web.config apart from the changes mentioned above <serviceHostingEnvironment aspNetCompatibilityEnabled=”false”> </serviceHostingEnvironment> </system.serviceModel> When a custom display form is generated by SharePoint Designer the attachment will not be exposed if one is present. Please refer to the following link for additional information in regards to custom list forms:
http://office.microsoft.com/en-us/sharepoint-designer-help/create-a-custom-list-form-using-sharepoint-designer-HA010378258.aspx The out of the box ListFormWebPart does not exhibit this behavior. Here's how you add a custom list form and display the comments field within SharePoint Designer 2007. I created a blank Web Part Page as my starting point.
|
AuthorMy specific technology focus areas within the Microsoft ecosystem are: ● Web development, ● SharePoint 2007/2010/2013 ● Microsoft CRM 4.0/2011/2013 Archives
October 2016
Categories
All
|