다음을 통해 공유

CRM 2013 Maintenance Jobs

  • 아티클

The Maintenance jobs for CRM 2013 is similar to the CRM 2011 jobs if they are not the same. I had to dig into the details of each of the jobs for my project. These are my findings and I hope it will be useful for everyone. Please remember to reschedule these maintenance jobs after the installation of CRM is completed.

Maintenance Job Name

Purpose

Default Frequency/ Recommendation

Deletion Service

The deletion service maintenance operation now cleans up subscription tracking records for deleted metadata objects as they expire.

  • If Outlook does not sync for 90 days, the job will remove the subscription with CRM.
  • Cleanup POA records. Un-sharing of record(s) do not remove the records in the POA table, it will stay there for 90 days and it will clean up by the deletion job.
  • Cleanup failed/stuck workflows for the record(s) synchronize with Outlook.

By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night

Indexing Management

Validates that system-managed indexes exist for all entities and recreates any missing indexes.

This is more for CRM Online and only impacts us during configuration of the solution. It automatically creates index for each column used in search for Quick Find if the index is not already created in the CRM database

By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night

Reindex All

Reorganizes/rebuilds fragmented indexes depending on the amount of fragmentation.

It execute the p_ReindexAll stored procedure, it selects all indexes in the database with greater than 30% fragmentation and runs ReIndex on each while in online mode. Run while online mode means indexes are not taken offline while reindex is happening they still are active and are functioning and can be used.

Large enterprise CRM implementation with big data volume should disable this CRM reindex job if a custom job is created for recreating the indexes and with run update stats.

By default, the job executes every 24 hours, disable the job by setting the Job Next Run value to the future. E.g. 12/31/2999

Cleanup Inactive Workflow Assemblies

Seeks custom workflow assemblies that are no longer referenced in workflow rules or in-process jobs. Those unreferenced assemblies are then deleted. Consider the scenario where you register version 2.0 of a custom workflow assembly.  You may update your rules to reference the new version, but some in-progress jobs may still be referencing version 1.0.  Once those jobs have completed, this maintenance job will clean up the version 1.0 assembly that is no longer referenced by rules/jobs.

It executes the p_CleanupInactiveWorkflowAssemblies stored procedure.

By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night

Create Audit Partition

Alters the partitioning scheme for the auditbase table (SQL Enterprise only).

By default, the job executes every month, reschedule the job to run at non-peak hours. E.g. 11:00PM at night

Check for MUI Updates

Detects upgrades to language (MUI) packs and schedules additional async operations to perform individual language provisioning.

By default, the job executes every 24 hours, if you don’t have language pack installed, you can disable the job by setting the Job Next Run value to the future. E.g. 12/31/2999

Refresh Entity Row Count

Refreshes the Record Count snapshot statistics leveraged enhanced query plans.

This job is important because it define how Quick Find works, it uses the counts to come up with the correct query plan.

By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night

Refresh Sharing Count

Refreshes the POA read snapshot statistics leveraged in enhanced query plans.

This job is important because it define how Quick Find works, it uses the counts to come up with the correct query plan.

By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night

The Deletion Service executes the following SQL queries.

exec sp_executesql N'select SubscriptionId, SystemUserId

from Subscription (nolock)

where datediff(dd, LastSyncStartedOn, getutcdate()) >= @expireSubscriptionInDays',N'@expireSubscriptionInDays int',@expireSubscriptionInDays=90

exec sp_executesql N'delete from PrincipalObjectAccess where (AccessRightsMask = 0 or AccessRightsMask is null) and (InheritedAccessRightsMask = 0 or InheritedAccessRightsMask is null) and VersionNumber <= @versionNumberExpired',N'@versionNumberExpired bigint',@versionNumberExpired=404676

exec sp_executesql N'delete from SubscriptionTrackingDeletedObject

where TimeStamp <= convert(timestamp, @versionNumberExpired)

SELECT @@ROWCOUNT',N'@versionNumberExpired bigint',@versionNumberExpired=404676

exec sp_executesql N'

declare @now DateTime = getutcdate();

DELETE FROM [MetadataSyncTrackingDeletedObject]

WHERE datediff(dd, CreatedOn, @now) >= @expireSubscriptionInDays;

declare @rowCount int;

SELECT @rowCount = @@ROWCOUNT;

declare @columnExists bit = 0;

IF (EXISTS (SELECT * FROM sys.columns

WHERE object_id = OBJECT_ID(N''OrganizationBase'')

AND name = N''MetadataSyncLastTimeOfNeverExpiredDeletedObjects''))

BEGIN

set @columnExists = 1;

END

SELECT @rowCount as [RowCount], @now as [Now], @columnExists as [ColumnExists];

',N'@expireSubscriptionInDays int',@expireSubscriptionInDays=90

exec sp_executesql N'

UPDATE

OrganizationBase

SET

MetadataSyncLastTimeOfNeverExpiredDeletedObjects = null

WHERE

MetadataSyncLastTimeOfNeverExpiredDeletedObjects is not null

AND MetadataSyncLastTimeOfNeverExpiredDeletedObjects <= DATEADD(dd, -@expireSubscriptionInDays, @now)

',N'@now datetime,@expireSubscriptionInDays int',@now='2014-04-02 16:13:42.993',@expireSubscriptionInDays=90

exec sp_executesql N'

if exists (select * from WorkflowWaitSubscriptionBase (nolock) where IsModified = 1)

begin

update AsyncOperationBase

set

StateCode = @readyState,

StatusCode = @waitingStatus,

ModifiedOn = @modifiedOn,

ModifiedBy = CreatedBy

where

StateCode = @suspendedState

and RetryCount < @maxRetries

and AsyncOperationId in (select AsyncOperationId from WorkflowWaitSubscriptionBase where IsModified = 1)

end',N'@readyState int,@suspendedState int,@waitingStatus int,@maxRetries int,@modifiedOn datetime',@readyState=0,@suspendedState=1,@waitingStatus=0,@maxRetries=10,@modifiedOn='2014-04-02 16:13:43'

Reindex All executes the p_ReindexAll stored procedure below.

/***********************************************************************************************/

/* RETURN CODES: */

/* 0 - Success */

/* 1 - Partial success - some indexes could not be rebuilt */

/* 5 - Invalid input parameter(s) */

/***********************************************************************************************/

/*

Returns

a) Always first recordset - one row with 4 integer columns:

ResultCode - see RETURN CODES

TotalIndexesToRebuild - total count of indexes detected to be rebuild

RebuiltWithOnlineON - count of indexes rebuilt with option ONLINE = ON

RebuiltWithOnlineOFF - count of indexes rebuilt with option ONLINE = OFF (can't be rebuilt with ONLINE = ON)

b) Always second recordset - see @errors table

c) Only when @Verbose=1, then the second recordset with detailed info about all indexes

*/

BEGIN

SET NOCOUNT ON

DECLARE @TotalIndexesToRebuild int = 0,

@RebuiltWithOnlineON int = 0,

@RebuiltWithOnlineOFF int = 0

--Get start time for max run time tracking

DECLARE @MaxTime datetime

SELECT @MaxTime = dateadd(ss,ISNULL(@MaxRunTime,0), GetUTCDate())

--Account for nulls in parameters, set to default values

SET @FragRebuildPct = ISNULL(@FragRebuildPct, 30)

SET @AllIndexTypes = ISNULL(@AllIndexTypes, 0)

SET @Verbose = ISNULL(@Verbose, 0)

SET @MinPages = ISNULL(@MinPages, 25)

SET @Online = ISNULL(@Online, 1)

--Validate parameters

IF ((@MaxRunTime <= 0) OR

(@AllIndexTypes not in (0,1)) OR

(@Verbose not in (0,1)) OR

(@Online not in (0,1)) OR

(@MinPages < 1) OR

(@FragRebuildPct > 100) OR (@FragRebuildPct < 0))

BEGIN

PRINT 'Invalid Parameter value. Valid values are:'

PRINT 'MaxRunTime > 0,'

PRINT 'MinPages > 0'

PRINT 'FragRebuildPct in {NULL,0..100}'

PRINT 'AllIndexTypes in {0,1}'

PRINT 'Verbose in {0,1}'

PRINT 'Online in {0,1}'

SELECT 5 as ResultCode, @TotalIndexesToRebuild as TotalIndexesToRebuild, @RebuiltWithOnlineON as RebuiltWithOnlineON, @RebuiltWithOnlineOFF as RebuiltWithOnlineOFF

RETURN 5

END

DECLARE @indexes table

(

SchemaName sysname,

TableName sysname,

IndexName sysname,

OldFrag int,

NewFrag int null,

processed bit

)

DECLARE @errors table

(

Number int,

Severity int,

State int,

--Message nvarchar(4000), -- can be found by select * from sys.messages m where message_id = Number and m.language_id = 1033

OnlineOn bit,

Statement NVarchar(2048)

)

INSERT INTO @indexes

SELECT schema_name(o.schema_id), object_name(s.object_id), i.name, s.avg_fragmentation_in_percent, null, 0

FROM sys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,NULL) s

JOIN sys.objects o on (s.object_id = o.object_id)

JOIN sys.indexes i on (s.object_id = i.object_id and s.index_id = i.index_id)

WHERE

s.avg_fragmentation_in_percent > @FragRebuildPct -- defrag only if more than x% fragmented

and i.type in (1, @AllIndexTypes + 1) -- (1,2) -- cannot defrag non-indexes(0-heap, 1- clustered, 2-nonclustered, 3-xml)

and s.page_count >= @MinPages -- select only if the index spans multiple pages

ORDER BY s.avg_fragmentation_in_percent desc

select @TotalIndexesToRebuild = @@rowcount

DECLARE @SchemaName sysname,

@TableName sysname,

@IndexName sysname,

@sqlTemplate NVarchar(2048),

@sql NVarchar(2048)

DECLARE @retry bit

,@onlineON bit

DECLARE IndexCursor CURSOR LOCAL FOR

SELECT SchemaName, TableName, IndexName from @indexes order by OldFrag desc

OPEN IndexCursor

FETCH NEXT FROM IndexCursor INTO @SchemaName, @TableName, @IndexName

WHILE ((@@FETCH_STATUS = 0) AND ((GetUTCDate() < @MaxTime) OR (@MaxRunTime IS NULL)) )

BEGIN

select @sqlTemplate = 'ALTER INDEX ['+ @IndexName +'] '+

'ON ['+@SchemaName+'].['+@TableName+'] REBUILD WITH '+

'( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = '

IF (@Online=1)

select @sql = @sqlTemplate + 'ON )'

ELSE

select @sql = @sqlTemplate + 'OFF )'

select @retry = 1, @onlineON = @Online

while (@retry = 1)

BEGIN

BEGIN TRY

IF (@Verbose=1) PRINT @sql

EXEC (@sql)

select @retry = 0

IF (@onlineON=1)

SELECT @RebuiltWithOnlineON = @RebuiltWithOnlineON +1

ELSE

SELECT @RebuiltWithOnlineOFF = @RebuiltWithOnlineOFF +1

END TRY

BEGIN CATCH

insert into @errors

select ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), @onlineON, @sql

IF (@onlineON=1 and ERROR_NUMBER() = 2725)

BEGIN

-- Handle the possible exception below: rebuild index offline. Only SQL2012 has THROW

--ErrorNumber ErrorMessage

--2725 An online operation cannot be performed for index '?' because the index contains column '?' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

select @sql = @sqlTemplate + 'OFF )'

select @onlineON = 0

END

ELSE

select @retry = 0

END CATCH

END

UPDATE @indexes SET processed=1 WHERE SchemaName=@SchemaName and TableName=@TableName and IndexName=@IndexName

FETCH NEXT FROM IndexCursor INTO @SchemaName, @TableName, @IndexName

END

CLOSE IndexCursor

DEALLOCATE IndexCursor

IF (@Verbose=1)

BEGIN

UPDATE @indexes

SET NewFrag = avg_fragmentation_in_percent

FROM

sys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,NULL) s

JOIN sys.objects o on (s.object_id = o.object_id)

JOIN sys.indexes i on (s.object_id = i.object_id and s.index_id = i.index_id)

WHERE SchemaName=schema_name(o.schema_id)

and TableName = object_name(s.object_id)

and IndexName = i.name

END

DECLARE @ResultCode int

IF Exists(select * from @indexes where processed = 0)

BEGIN

PRINT 'Did not process all indexes due to @MaxRunTime constraint'

SELECT @ResultCode = 1

END

ELSE

BEGIN

SELECT @ResultCode = 0

END

-- Return results

SELECT @ResultCode as ResultCode, @TotalIndexesToRebuild as TotalIndexesToRebuild, @RebuiltWithOnlineON as RebuiltWithOnlineON, @RebuiltWithOnlineOFF as RebuiltWithOnlineOFF

SELECT * from @errors

IF (@Verbose=1) SELECT * FROM @indexes order by OldFrag desc

RETURN @ResultCode

Clean Up Inactive Workflow Assemblies executes the p_CleanupInactiveWorkflowAssemblies stored procedures below:

DECLARE @ActivationsToDelete TABLE

(

WorkflowId UNIQUEIDENTIFIER

)

INSERT @ActivationsToDelete

SELECT WorkflowId

FROM WorkflowBase

WHERE

Type = 2 -- it's a workflow activation

AND StateCode = 0 -- not active

AND WorkflowId NOT IN

(SELECT OwningExtensionId FROM AsyncOperationBase

WHERE OperationType = 10

AND OwningExtensionId IS NOT NULL)

DELETE FROM WorkflowDependencyBase WHERE WorkflowId IN

(SELECT WorkflowId from @ActivationsToDelete)

DELETE FROM DependencyNodeBase WHERE

ComponentType = 29 AND

ObjectId IN

(SELECT WorkflowId from @ActivationsToDelete)

DELETE FROM WorkflowBase WHERE WorkflowId IN

(SELECT WorkflowId from @ActivationsToDelete)

Comments

  • Anonymous
    October 20, 2014
    Thanks Darren, that is great information. It is very difficult to find any information about these jobs. Where do I reschedule them? Best regards Thomas

  • Anonymous
    October 22, 2014
    You may download the CRM Maintenance Job Editor from Codeplex to reschedule them. crmjobeditor.codeplex.com

  • Anonymous
    October 19, 2015
    Hi Darrenliu This is good information, but I'm wondering if you could help me with an issue. I've been running my dynamics 2013 system now for about 2 years and am not sure these jobs even exist.  I've looked at the jobs on my sql server as well as the task manager on my Dynamics servers and I can't find these jobs anywhere. Are they created by default on an installation of 2013?  Do I have to create them?  If they don't exist, is there a way to automatically add them now? Thank You

  • Anonymous
    December 07, 2015
    same question as above ^ do we need to set these up manually? or should they already exist? Thanks