Mihai Sarbulescu's System Center Blog

let's play with System Center

How to manually execute WorkItem Grooming in Service Manager

How to manually execute WorkItem Grooming in Service Manager

  • Comments 3
  • Likes

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: http://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
   @ManagedEntityType AS UNIQUEIDENTIFIER,
   @GroomingCriteria AS NVARCHAR(MAX),
   @RetentionPeriodMinutes AS INT,
   @BatchSize AS INT
SET @BatchSize = 10000
DECLARE GroomingToExecute CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT ManagedTypeId
FROM ManagedType WITH(NOLOCK)
WHERE TypeName IN (
   'System.WorkItem.ReleaseRecord',
   'System.WorkItem.Incident',
   'System.WorkItem.ServiceRequest',
   'System.WorkItem.ChangeRequest',
   'System.WorkItem.Problem'
)
OPEN GroomingToExecute
FETCH NEXT FROM GroomingToExecute INTO @ManagedEntityType
WHILE @@FETCH_STATUS = 0 BEGIN
SET @GroomingCriteria = (
   SELECT Criteria
   FROM MT_GroomingConfiguration WITH(NOLOCK)
   WHERE TargetId = @ManagedEntityType
)
SET @RetentionPeriodMinutes = (
   SELECT RetentionPeriodInMinutes
   FROM MT_GroomingConfiguration WITH(NOLOCK)
   WHERE TargetId = @ManagedEntityType
)
EXEC p_GroomManagedEntity @ManagedEntityType, @RetentionPeriodMinutes, @GroomingCriteria, @BatchSize
FETCH NEXT FROM GroomingToExecute INTO @ManagedEntityType
END
CLOSE GroomingToExecute
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: http://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
   @TargetTypeId AS UNIQUEIDENTIFIER,
   @GroomingCriteria AS NVARCHAR(MAX),
   @RetentionPeriodMinutes AS INT,
   @TypeName AS NVARCHAR(255),
   @RetentionDateTime AS DATETIME
SET @TypeName = 'System.WorkItem.Incident'
SET @GroomingCriteria = (
   SELECT Criteria
   FROM MT_GroomingConfiguration WITH(NOLOCK)
   WHERE TargetId = (
      SELECT ManagedTypeId
      FROM ManagedType WITH(NOLOCK)
      WHERE TypeName = @TypeName
   )
)
SET @RetentionPeriodMinutes = (
   SELECT RetentionPeriodInMinutes
   FROM MT_GroomingConfiguration WITH(NOLOCK)
   WHERE TargetId = (
      SELECT ManagedTypeId
      FROM ManagedType WITH(NOLOCK)
      WHERE TypeName = @TypeName
   )
)
SET @TargetTypeId = (
   SELECT ManagedTypeId
   FROM ManagedType WITH(NOLOCK)
   WHERE TypeName = @TypeName
)
SELECT @GroomingCriteria
SET @RetentionDateTime = DATEADD(MI, -@RetentionPeriodMinutes, GETUTCDATE())
EXEC sp_executesql
      @GroomingCriteria,
      N'@Retention DATETIME, @TargetTypeId UNIQUEIDENTIFIER, @NumOfEntities INT',
      @Retention = @RetentionDateTime,
      @TargetTypeId = @TargetTypeId,
      @NumOfEntities = 1000

 

  

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

 

 

  • This is great for Grooming, but what about data Purging?

  • 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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search
Blog - Link List

Official System Center Blogs

Need to read Blogs