Tag Archives: 2011

Microsoft Dynamics CRM – Enable Tracing

Required registry entries

Name Type Data value Notes
TraceEnabled DWORD A value of 0 or 1 If you use a value of 0, tracing is disabled. If you use a value of 1, tracing is enabled.
TraceDirectory String C:\CRMTrace The TraceDirectory registry entry specifies the directory for the trace log files. The directory must exist, and the user who starts the Microsoft CRMAppPool must have full control over this directory. When you install Microsoft CRM, the default user is NT AUTHORITY\NETWORK SERVICE. This entry is only required for Microsoft Dynamics CRM 3.0. For later versions, the trace directory is set to the install location of the Microsoft Dynamics CRM program files, C:\Program Files\Microsoft Dynamics CRM\Trace
TraceRefresh DWORD A number between zero and 99 When the data is changed, the trace settings in the other trace registry entries are applied.

Optional registry entries

Name Type Data value Notes
TraceCategories String or Multi-String Category.Feature:TraceLevel The TraceCategories registry entry is a combination of a category, a feature, and a trace level. You can specify multiple categories, features, and trace levels. Separate each combination by using a semicolon. For a list of categories, features, and trace levels and for sample combinations that are valid, see the “Trace level values” section.
TraceCallStack DWORD A value of 0 or 1 If you use a value of 0, the call stack is not included in the trace file. If you use a value of 1, the call stack is included in the trace file.
TraceFileSizeLimit DWORD A size between 1 and 100 MB The TraceFileSizeLimit registry entry specifies the maximum size of trace files. New files are created when the limit is reached.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM

When you run Microsoft Dynamics CRM 4.0 or Microsoft Dynamics CRM 2011, the AsyncOperationBase table grows to be very large. When the table contains millions of records, performance is slow.

To resolve this problem, perform a cleanup of the AsyncOperationBase table by running the following script against the<OrgName>_MSCRM database, where the placeholder<OrgName> represents the actual name of your organization.

Warning Before you clean up the data, be aware that completed system jobs have business value in some cases and have to be stored for a long period. Therefore, you should discuss this with your organization’s administration staff first.

System jobs that are affected:

  • SQM data collection. Software Quality Metrics collects data for the customer experience program.
  • Update Contract States SQL job. This job runs one time per day at midnight. This job sets the expired contracts to a state of Expired.
  • Organization Full Text Catalog Index. Populates full text index in db for searching Microsoft Knowledge Base articles in CRM.

If recurring jobs were canceled, they will be removed.

Notes

  • For Microsoft Dynamics CRM 4.0 The SQL script in this Knowledge Base article is a one-time effort only. You can add this as a SQL job to run on a recurring nightly, weekly, or monthly basis. As your CRM runs, you have to either apply this article weekly, depending on your business needs, or apply the solution by writing custom BULK DELETE jobs. (Refer to our CRM SDK documentation on the BulkDeleteRequest.QuerySet property, on the BulkDeleteRequest class, and on the order of deletion).
  • For Microsoft Dynamics CRM 2011 The SQL script in this Knowledge Base article is a one-time effort only. You can add this as a SQL job to run on a recurring nightly, weekly, or monthly basis. As your CRM runs, you have to either apply this article weekly, depending on your business needs, or apply the solution by using BULK DELETE jobs by defining a job by using the BULK DELETE wizard.
  • Make sure that the AsyncOperation records for workflows and the corresponding records are deleted from theWorkflowLogBase object.
  • Make sure that all the corresponding bulkdeletefailure records are deleted.
  • Make sure that only the following Async operation types are deleted if the state code of the types is 3 and the status code of the types is 30 or 32:
    • Workflow Expansion Task (1)
    • Collect SQM data (9)
    • PersistMatchCode (12)
    • FullTextCatalogIndex (25)
    • UpdateContractStates (27)
    • Workflow (10)
IF EXISTS (SELECT name from sys.indexes
                  WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin      
begin tran      
insert into @DeletedAsyncRowsTable(AsyncOperationId)
      Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
      where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)     
       Select @rowCount = 0
      Select @rowCount = count(*) from @DeletedAsyncRowsTable
      select @continue = case when @rowCount <= 0 then 0 else 1 end      
        if (@continue = 1)        begin
            delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
            where W.AsyncOperationId = d.AsyncOperationId             
 delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
            where B.AsyncOperationId = d.AsyncOperationId
 delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
 where WS.AsyncOperationId = d.AsyncOperationID 
            delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
            where A.AsyncOperationId = d.AsyncOperationId             
            delete @DeletedAsyncRowsTable      
end       
commit
end
--Drop the Index on AsyncOperationBase
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

 

Original Url

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Cleaning up CRM Sync Entry tables

If you’re CRM system has been up and running for a long period of time and your users have the CRM Outlook client installed (or have had it installed in the past) you’ll find many tables starting in SyncEntry_ as well as SubscriptionStatistics_ in your database. These tables are used to track items that are synchronized down to users client machines, however, when users get new client machines or leave the company the tables are left behind indefinitely.  This script has become a cleanup step we run with our customers prior to upgrading or periodically to prune out old sync data.

Important NOTES about this script:

  • Always make a backup before running this script against your DB.
  • Running scripts that modify your database are not supported, thus we’ve gone to great lengths to test this and make sure that all entries are cleaned out.  As of today this script can successfully run on:
    • CRM 4.0 UR7 and greater
    • CRM 2011 RTM through CRM 2011 Update Rollup 8
  • Do not modify any parameters or joins in this script or you could risk data damage
  • The script currently deletes all sync data older than 90 days
  • If a users sync entry data were to be deleted, the next time the user attempts to sync their client, the data would be rebuilt automatically
  • To test the script you can edit the “SET @execute = 1” and set it to a “0”, this will allow the script to run in a read-only mode and print out the SQL statements it would run to delete the tables.

 

Declare 
      @SyncEnt    varchar(60),
      @syncId     uniqueidentifier,
      @SQL        nvarchar(MAX), 
      @execute bit

--To run the deletions set this to 1, if it is 0 it will only print the statements
SET @execute = 1 

DECLARE CRMSync_cursor CURSOR FOR

SELECT Replace(SyncEntryTableName,'SyncEntry_','') as SyncEntryGUID, SubscriptionId from subscription 
where LastSyncStartedOn < GetDate()-90 or LastSyncStartedOn is NULL

OPEN CRMSync_cursor
FETCH NEXT FROM CRMSync_cursor INTO @SyncEnt, @syncId
WHILE @@Fetch_Status = 0
BEGIN
      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('subscriptionStatistics_'+@SyncEnt) AND type in (N'U'))
      BEGIN
            SET @SQL = 'DROP TABLE SubscriptionStatistics_' +(@SyncEnt)
            IF @execute=1 EXEC sp_executesql @SQL
            IF @execute=0 PRINT @SQL
            IF @execute=1 PRINT 'Dropped table: SubscriptionStatistics_'+(@SyncEnt)+ ' with error: ' + CAST(@@ERROR as varchar(255))
      END
      ELSE
            IF @execute=1 PRINT 'SubscriptionStatistics table does not exist for subscriptionID: ' + CAST(@syncId as varchar(50))

      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('SyncEntry_'+@SyncEnt) AND type in (N'U'))
      BEGIN
            SET @SQL = 'DROP TABLE SyncEntry_' +(@SyncEnt)
            IF @execute=1 EXEC sp_executesql @SQL
            IF @execute=0 PRINT @SQL
            IF @execute=1 PRINT 'Dropped table: SyncEntry_'+(@SyncEnt) + ' with error: ' + CAST(@@ERROR as varchar(255))
      END
      ELSE
            IF @execute=1 PRINT 'SyncEntry table does not exist for subscriptionID: ' + CAST(@syncId as varchar(50))

      SET @SQL = 
            'delete from SubscriptionManuallyTrackedObject where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''';' +
            'delete from subscriptionclients where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''';' +
            'delete from Subscriptionsyncinfo where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''';' +
            'delete from subscription where subscriptionId = ''' + CAST(@syncId as varchar(50)) + ''''
      IF @execute=1 EXEC sp_executesql @SQL
      IF @execute=0 PRINT @SQL
      IF @execute=1 PRINT 'Dropped subscription table data for subscriptionId: ' + CAST(@syncId as varchar(50)) + ' with error data: ' + CAST(@@ERROR as varchar(255))
      FETCH NEXT FROM CRMSync_cursor INTO @SyncEnt, @syncId
END
CLOSE CRMSync_cursor
DEALLOCATE CRMSync_cursor
Original url

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Configuring CRM 2011 IFD

A really, really good article on how to configure CRM 2011 IFD:

http://dynamics.co.il/configuring-crm-2011-ifd/

 

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>