What is Grooming? Why Groom?
The ServiceManager database is the “online” or “operational” database in Service Manager. It is the database which most users are interacting with when they are using Service Manager. Therefore, you want to keep it running as fast as possible. Databases are kind of like car engines. The longer they run, the more they fill up with “junk” and need to be cleansed to operate at peak efficiency. “Junk” in the case of databases is extraneous data that nobody cares about anymore. At one point it was useful, but days, weeks, months, or even years later it is highly unlikely to be accessed in the normal course of operations. Having that extra data in there makes the queries for data that people really care about slower.
The System Center Data Warehouse in Service Manager is intended to be the long term storage and reporting store. It exists in part to offload the “junk” data from the ServiceManager database. You see, people care about “junk” data in aggregate – for example, “How many incidents did we have last month compared to the same month last year?” For the most part, nobody really cares about incident ID: 5233 that occurred last February though. Sure, it’s there if you really need it in the warehouse, but it’s highly unlikely you’ll need to go back that far and look at the details of that incident.
How Does Grooming Work?
So, to keep the ServiceManager database running at peak efficiency we periodically cleanse it with engine dejunker via a process we call “Grooming”. Basically, it works like this…
1) The extract workflow copies the data (new and updated objects) from the ServiceManager database to the System Center Data Warehouse every 5 minutes (by default). This creates a copy of the data for long term storage and reporting. The object still remains in the ServiceManager database so it can be further acted upon as appropriate. It's important to note that even though the data gets into the warehouse every 5 minutes, the reports themselves get access to the fresh data once an hour by default. If you'd like to learn more about what happens to the data between the time it gets copied out of the ServiceManager database and when it shows up in the reports, see our blog about the Extract, Transform and Load process.
2) As time goes on, the data loses its relevance. Now, one of two things happen to mark an object deleted in the database:
a. A user, connector, or some other client makes a call to the Data Access Service and says ‘Delete this object’ or
b. An automated workflow looks for objects which match certain criteria and marks those objects deleted. For example: Delete all incidents which are Status=Closed and the incident hasn’t been modified in 90 days.
3) An automated purging workflow actually removes the objects marked deleted from the database forever (but don’t worry you have a copy in the data warehouse, remember?).
Grooming Data Using the Console
Out of the box, we provide two ways to groom objects using the console:
1) Grooming configuration items (CIs) or “Manual Grooming” – Only users in the Advanced Operators and Administrators user roles can select any configuration item in the Configuration Items workspace in their security scope and click Delete in the task pane.
Despite what it sounds like, this does not actually delete the object from the database . It merely changes the Object Status enumeration data type property on the configuration item from ‘Active’ (System.ConfigItem.ObjectStatusEnum.Active) to ‘Pending Delete’ (System.ConfigItem.ObjectStatusEnum.PendingDelete). When the object is in this ‘Pending Delete’ state it will no longer be displayed in any configuration item views or in the Select Object dialog. It will only be shown in the Deleted Items view in the Administration workspace. From there you can choose to Restore the object by clicking Restore Items or Remove the object by clicking Remove Items in the task pane.
Restore sets the Object Status property back to ‘Active’ so it will show up in the Configuration Items views again. Remove sets the Object Status property to ‘Deleted’ (System.ConfigItem.ObjectStatusEnum.Deleted) and tells the Data Access service to mark the object to Deleted (#2a above). This changes the value of the IsDeleted field on the BaseManagedEntity table in the database from 0 to 1. The object is not yet actually dropped from the database. That’s what the purging workflow (#3 above) is for.
2) Grooming Work Items or “Automated Grooming” - The second method is for Problem, Incident, and Change Request work items which are automatically groomed based on certain criteria. You can go to the Settings view in the Administration workspace and configure the Retention Period settings for each of these.
The default retention period for these work items is:
· Incidents: 90 days
· Change requests: 365 days
· Problems: 365 days
You can configure these values to be anything from 0 up to 10,000 days. If you set it to 0 then there is essentially no retention and any object that matches the criteria the next time the grooming workflow runs will be deleted.
The retention period here is really talking about how many days must have passed since the last time the object was modified before it can be deleted.
The object must also meet other criteria. For the work items we provide out of the box, the additional criteria are:
· Incidents: Status = Closed (IncidentStatusEnum.Closed)
· Change request: Status = Closed (ChangeStatusEnum.Closed)
· Problem: Status = Closed (ProblemStatusEnum.Closed)
So, to summarize and provide an example, an incident must be Status = Closed AND not be modified for more than 90 days before it will be deleted.
When an object is deleted, the objects that are hosted (System.Hosting) by that object or have a membership relationship (System.Membership) with that object are also deleted. For example, when a change request is deleted, the activities that are members of that change request are also deleted. Objects which are related to (System.Reference) or contained by (System.Containment) the object being deleted are not deleted. For example, if an incident is deleted, the computer it is related to is not deleted.
Once an object is marked deleted it will no longer be shown in the console or returned by a query to the Data Access Service. As with the configuration items, the grooming workflow will only mark the objects IsDeleted = 1. It is not actually dropped from the database yet though. That’s the job of the purging workflow.
Grooming Workflows
There are five grooming workflows for the ServiceManager database. They are implemented as Rule workflows :
· Microsoft.ServiceManager.Grooming.GroomEntities
Management Pack: ServiceManager.Grooming.Configuration
This rule is the primary grooming workflow for objects. It runs every night at midnight on the management server that is currently configured to run workflows within the management group.
· Microsoft.ServiceManager.Grooming.GroomSubscriptionLogs
Management Pack: ServiceManager.Grooming.Configuration
This rule grooms the subscription data source logs. This is an internal log store that you should never need to worry about. It runs every 15 minutes on the management server.
· Microsoft.ServiceManager.Grooming.GroomChangeLogs
Management Pack: ServiceManager.Grooming.Configuration
This rule grooms the object history logs that keep track of every property and relationship change of many of the objects in the database. It runs every day at 2:00 in the morning on the management server.
· Microsoft.SystemCenter.SqlJobs.PartitioningAndGrooming
Management Pack: ServiceManager.Core.Library
This rule does some additional grooming of system data types and partitions tables so that entire tables of internal data can be dropped. This workflow isn’t really relevant to Service Manager. It runs every day at midnight on the management server.
· Microsoft.SystemCenter.SqlJobs.DiscoveryDataPurging
Management Pack: ServiceManager.Core.Library
This rule is the purging workflow that looks for objects marked IsDeleted = 1 and actually drops those records from the database completely. It runs every day at 2:00 in the morning on the management server.
A couple of notes about these:
· There are actually two rules with the ID Microsoft.SystemCenter.SqlJobs.PartitioningAndGrooming. One of them is in the ServiceManager.Core.Library MP and the other is in the Microsoft.SystemCenter.Internal MP. The rule in the Microsoft.SystemCenter.Internal MP is a legacy rule that is disabled by a rule override in the ServiceManager.Core.Library such that only the rule in the ServiceManager.Core.Library MP runs. The same is true of the Microsoft.SystemCenter.SqlJobs.DiscoveryDataPurging rule.
· The ServiceManager.Grooming.Configuration MP is an unsealed MP which means you can change the configuration of those grooming rules such as the schedule and whether or not they are enabled. For example, you could change the schedule for the from midnight to 3:00 in the morning like this:
<SimpleReccuringSchedule>
<Interval Unit="Days">1</Interval>
<SyncTime>03:00</SyncTime>
</SimpleReccuringSchedule>
<ExcludeDates />
</Scheduler>
Or you could disable object grooming altogether:
<Rule ID="Microsoft.ServiceManager.Grooming.GroomEntities" Enabled="false"…
To do this, just export out the MP, modify it, increment the version number in the Manifest section and reimport it. See Hacking Management Pack XML Like a Pro .
Grooming Extensibility
Partners and customers can extend the grooming infrastructure to add their own grooming criteria. To do this you need to create a new instance of the System.GroomingConfiguration class. This is the model of that class:
<ClassType ID="System.GroomingConfiguration" Base="System.SolutionSettings">
<Property ID="TargetId" Type="guid" Key="true" Required="true" />
<Property ID="Category" Type="enum" EnumType="GroomingCategory" Key="true" Required="true" />
<Property ID="RetentionPeriodInMinutes" Type="int" Required="true" />
<Property ID="IsInternal" Type="bool" Required="true" />
<Property ID="BatchSize" Type="int" Required="true" />
<Property ID="StoredProcedure" Type="string" Required="true" />
<Property ID="Criteria" Type="string" MaxLength="10000" />
</ClassType>
Here is some guidance on creating instances of this class:
· You should set the TargetId to the ID of the class that you are grooming objects of.
· Set the Category equal to GroomingCategory.Entity. This enum hierarchy is in the System.AdminItem.Library MP.
· Set RetentionPeriodInMinutes to whatever you want – remember this is in minutes . This is how long you want to keep the object for from the last time the object was modified.
· Set IsInternal = false.
· Set BatchSize to some positive integer. We are using 1,000 for incident, problem, and change requests.
· Set StoredProcedure to ‘p_GroomManagedEntity’ (without the single quotes)
· Set the Criteria equal to the T-SQL statement that selects just the objects you want to have deleted. The @Retention variable will be converted and set for you at runtime according to the value defined in RetentionPeriodInMinutes. The @TargetTypeId variable will be set for you at runtime with the value in TargetId. For example, here are the Criteria for Incident grooming:
SELECT BME.[BaseManagedEntityId]
FROM dbo.[BaseManagedEntity] BME
JOIN dbo.MT_System$WorkItem$Incident I ON I.BaseManagedEntityId = BME.BaseManagedEntityId
WHERE
BME.[IsDeleted] = 0 AND
BME.[LastModified] < @Retention AND
BME.BaseManagedTypeId = @TargetTypeId AND
I.Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'AED4C69E-1891-A855-AEB4-6E456C6FA33F'
As a partner delivering a solution to customers, you may want to groom your objects out of the database and possibly give the customers the control over when things are groomed. You can provide a user interface for creating these instances or updating them similar to what we did for work item grooming configuration above. Also, similar to what we did, you can create instances of this class during the setup of your solution on top of the Service Manager product.
Data Warehouse Grooming
For more information on DW grooming see this blog post:
http://blogs.technet.com/b/servicemanager/archive/2011/06/07/how-much-data-do-we-retain-in-the-...
Update Oct 25, 2012 - Content contributed by Manoj Parvathaneni
If you want to look at a history of the grooming job execution you can run this query in the ServiceManager database:
select
top 1000 * from InternalJobHistory (nolock)
--where
Command like 'Exec dbo.p_GroomManagedEntity A604B942%' -- Incident
grooming (once a day)
--where
Command like 'Exec
dbo.p_DataPurging'
-- Incident purging (once a day)
--where
Command like 'exec
dbo.p_GroomChangeLogs%'
-- History grooming (EntityChangeLog) (once a day)
--where
Command like 'exec dbo.p_GroomSubscriptionSpecificECL%' -- Grooming
subscription specifc EntityChangeLog rows (every fifteen minutes)
--where
Command like 'exec dbo.p_GroomSubscriptionSpecificRECL%' -- Grooming
subscription specifc RelatedEntityChangeLog rows (every fifteen minutes)
order
by InternalJobHistoryId desc
Each of these clauses queries a different type of grooming. The query will return the last 1000 occurrences of that type of grooming. If you see a non-NULL TimeFinished value and a Status of 1 that means grooming successfully completed. If you see a NULL value for TimeFinished and a Status of 0 or 2, it means something went wrong:
1. There were too many rows to groom and we couldn’t finish it within 30 minutes.
2. A deadlock happened and SQL killed the grooming spid.
3. Some other failure, in which case Status code would be 2.
In all these cases you should see an event log entry in the Management Server with Event Id 10880. Note that for the first two cases above we would retry once more before we give up. So you will see two calls back to back if the first one fails.