Freigeben über

How to manually execute WorkItem Grooming in Service Manager

  • Artikel

IMPORTANT: Always perform a FULL Backup of the database before doing anything to it !!!

ALSO: It is advised that you open a case at Microsoft before doing this - directly editing the database is not supported and you may find yourself in an unsupported state if anything goes wrong.

 

There are situations where Grooming for some or all WorkItem types may fail because of the fact that there are just too many entries to delete and the job takes more than 30 minutes (Workflow TimeOut) to complete. This may happen in some situations but the most common one is where the default grooming settings for the different WorkItems was changed and set too high for the environment to handle.

In these situations you might need to manually run the Grooming Workflow (SQL Stored Procedure) for all or some WorkItems types.

 The SQL Query below should be executed on the ServiceManager database and it will manually groom all WorkItem types that meet their grooming criteria. More on how this works here: https://blogs.technet.com/b/servicemanager/archive/2009/09/18/data-retention-policies-aka-grooming-in-the-servicemanager-database.aspx

 

If you don't want to do this for *all* WorkItem types in one execution, then please feel free to remove the names of the WorkItem types from the Query below which you do not want to get groomed out - be careful with the comma " , " there after each WorkItem type-name so that the Query remains valid.

 

 DECLARE<br>    @ManagedEntityType AS UNIQUEIDENTIFIER,<br>    @GroomingCriteria AS NVARCHAR(MAX),<br>    @RetentionPeriodMinutes AS INT,<br>    @BatchSize AS INT<br> SET @BatchSize = 10000<br> DECLARE GroomingToExecute CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR<br> SELECT ManagedTypeId<br> FROM ManagedType WITH(NOLOCK)<br> WHERE TypeName IN (<br>    'System.WorkItem.ReleaseRecord',<br>    'System.WorkItem.Incident',<br>    'System.WorkItem.ServiceRequest',<br>    'System.WorkItem.ChangeRequest',<br>    'System.WorkItem.Problem'<br> )<br> OPEN GroomingToExecute<br> FETCH NEXT FROM GroomingToExecute INTO @ManagedEntityType<br> WHILE @@FETCH_STATUS = 0 BEGIN<br> SET @GroomingCriteria = (<br>    SELECT Criteria<br>    FROM MT_GroomingConfiguration WITH(NOLOCK)<br>    WHERE TargetId = @ManagedEntityType<br> )<br> SET @RetentionPeriodMinutes = (<br>    SELECT RetentionPeriodInMinutes<br>    FROM MT_GroomingConfiguration WITH(NOLOCK)<br>    WHERE TargetId = @ManagedEntityType<br> )<br> EXEC p_GroomManagedEntity @ManagedEntityType, @RetentionPeriodMinutes, @GroomingCriteria, @BatchSize<br> FETCH NEXT FROM GroomingToExecute INTO @ManagedEntityType<br> END<br> CLOSE GroomingToExecute<br> DEALLOCATE GroomingToExecute

 

I highly recommend setting the Grooming Thresholds as low as possible/allowed in the SM Console because you have the data for these items in the Data Warehouse database for a very long time anyway.

Also, another great recommendation is to lower the Grooming Threshold for the internal JobStatus and WindowsWorkflowTaskJobStatus history data as explained and described here: https://blogs.technet.com/b/servicemanager/archive/2010/12/07/more-aggressively-grooming-the-jobstatus-and-windowsworkflowtaskjobstatus-tables.aspx

 

 

If you are interested in seeing which WorkItems should/would be deleted on grooming *and* the grooming criteria for that type, then you can execute the below query to check. This query will show you the results for the Incident WorkItem type. If you want to view this for another type, then in the below query, change the value of the @TypeName variable. Currently it is set to System.WorkItem.Incident and the accepted values are:

  • System.WorkItem.Problem
  • System.WorkItem.ChangeRequest
  • System.WorkItem.ServiceRequest
  • System.WorkItem.ReleaseRecord
 DECLARE<br>    @TargetTypeId AS UNIQUEIDENTIFIER,<br>    @GroomingCriteria AS NVARCHAR(MAX),<br>    @RetentionPeriodMinutes AS INT,<br>    @TypeName AS NVARCHAR(255),<br>    @RetentionDateTime AS DATETIME<br> SET @TypeName = 'System.WorkItem.Incident'<br>  SET @GroomingCriteria = (<br>    SELECT Criteria<br>    FROM MT_GroomingConfiguration WITH(NOLOCK)<br>    WHERE TargetId = (<br>       SELECT ManagedTypeId<br>       FROM ManagedType WITH(NOLOCK)<br>       WHERE TypeName = @TypeName<br>    )<br> )<br> SET @RetentionPeriodMinutes = (<br>    SELECT RetentionPeriodInMinutes<br>    FROM MT_GroomingConfiguration WITH(NOLOCK)<br>    WHERE TargetId = (<br>       SELECT ManagedTypeId<br>       FROM ManagedType WITH(NOLOCK)<br>       WHERE TypeName = @TypeName<br>    )<br> )<br> SET @TargetTypeId = (<br>    SELECT ManagedTypeId<br>    FROM ManagedType WITH(NOLOCK)<br>    WHERE TypeName = @TypeName<br> )<br> SELECT @GroomingCriteria<br> SET @RetentionDateTime = DATEADD(MI, -@RetentionPeriodMinutes, GETUTCDATE())<br>EXEC sp_executesql<br>       @GroomingCriteria,<br>       N'@Retention DATETIME, @TargetTypeId UNIQUEIDENTIFIER, @NumOfEntities INT',<br>       @Retention = @RetentionDateTime,<br>       @TargetTypeId = @TargetTypeId,<br>       @NumOfEntities = 1000

 

  

Well - the database can breathe better now, eh? :D

Comments

  • Anonymous
    January 01, 2003
    This will purge all data older than "now" (moment when the query is being executed) which was deleted (IsDeleted=1):

    DECLARE
    @TimeGenerated DATETIME,
    @BatchSize INT,
    @RowCount INT
    SET @TimeGenerated = GETUTCDATE()
    SET @BatchSize = 10000
    EXEC p_DiscoveryDataPurgingByRelationship @TimeGenerated, @BatchSize, @RowCount
    EXEC p_DiscoveryDataPurgingByTypedManagedEntity @TimeGenerated, @BatchSize, @RowCount
    EXEC p_DiscoveryDataPurgingByBaseManagedEntity @TimeGenerated, @BatchSize, @RowCount
  • Anonymous
    January 01, 2003
    This is great for Grooming, but what about data Purging?
  • Anonymous
    May 13, 2014
    Pingback from SCSM.SE » Blog Archive » DCIM-B308: Advanced Lessons learned from Implementing System Center 2012 R2 Service Manager