~ Scott Walker | Senior Support Escalation Engineer
In the first installment in this series, I discussed a few items related to grooming. In Part 2 we focused on the Internal Job History which keeps track of which grooming jobs ran, when they ran, and whether or not they completed successfully. Now in our third and final post in the series, we’re going to focus on some of the tools (SQL queries) you can use to answer the question "How much needs to be groomed?"
Note: Part 1 can be found here . Part 2 is here .
Many of the grooming related stored procedures call other stored procedures and functions, so rather than go through all the many depths of all the grooming jobs, I'm going to cover the most common ones I've seen in support.
One point of trouble that I often see involves grooming the Service Manager EntityChangeLog table. This table maps roughly to your History retention settings, and tracks changes for Service Manager "entities". Entities are Service Manager's way of referring to items stored in Service Manager such as Configuration Items, Work Items, etc. For more about entities and the way Service Manager models things in the database, I suggest reading Travis Wright's post here:
The System Center Platform in Service Manager
The EntityChangeLog and RelatedEntityChange log can grow for various reasons. The growth can be caused over time from using high History Retention settings in the console (not recommended), tracking lots of changes for many Configuration Items, and more.
Before I start down the recommendation path, I must throw out a disclaimer that every environment is different, so I'm going to just make a few assumptions and go from there. Just know that you may or may not see grooming issues depending on your specific Service Manager configuration. You must monitor the grooming on a regular basis to determine what is happening for you in your deployment.
So with that out of the way, if you have a fairly moderately sized environment with, for example, 5,000 Configuration Items, and you’re using Service Manager for Incident, Change, and Service Management, then setting your History to 365 days is likely OK. However, if you have a fairly large environment with, say 30,000 Configuration items, and you're tracking lots of Configuration Item Changes, and you have a very active IT service department using Service Manager for SLA/SLO, Incident Management, Change and Request Management, plus you have several connectors for AD, and possibly even Configuration Manager, then you should consider setting your History Retention to something around 6 months, or 120 days. This is because tracking all these changes adds more and more to the EntityChangeLog table. Again, this is very environment specific. If you have an extremely capable SQL configuration and EntityChangeLog contains between 25 and 50 million rows, you may not have a problem. As a rule of thumb, I like to start looking into possible grooming failures if the EntityChangeLog (ECL) is above 10 million rows. This applies to RelatedEntityChangeLog (RECL) as well.
If your ECL and/or RECL tables are over 10 million rows, you're not seeing any grooming failures, and performance in Service Manager is good, then don't worry, be happy, but be wary. Keep tabs on the total row counts for these tables and monitor the grooming jobs. A random grooming failure now and again is nothing to worry about, but consistent failure is not good, and the earlier you detect it the better, and the sooner something can be done to fix the problem before you start seeing performance issues in Service Manager.
Now let's get to the meat of this post, and that is how to tell how much needs to be groomed. EntityChangeLog has three different grooming procedures that all result in a final groom of EntityChangeLog:
GroomChangeLogs
GroomStagedChangeLogs
GroomSubscriptionSpecificECLRows
Let's start with GroomChangeLogs . You need to know your History Retention time from Service Manager –> Administration –> Settings –> Data Retention Settings –> History, or you can just use various settings in the queries below to see how it changes the counts that need to be groomed based on the number of days to retain history. I'm using 120 days in these examples.
How many Entities need to be groomed from ECL based on the GroomChangeLogs criteria?
--declare variables
DECLARE @RetentionDateTime DateTime;
DECLARE @SubscriptionWatermark bigint;
DECLARE @RetentionPeriod int = 120; --<<<<< This is where you set your retention days, 120 days in this example
--Set variables
SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();
SELECT @RetentionDateTime = DATEADD(d, -@RetentionPeriod, getutcdate())
--Count entities to be groomed
SELECT count (1) EntityChangeLogId
FROM (SELECT EntityChangeLogId,
LastModified,
EntityTransactionLogId,
ROW_NUMBER() OVER (PARTITION BY EntityId, EntityTypeId ORDER BY EntityChangeLogId DESC) AS RowNum
FROM dbo.EntityChangeLog
WHERE RelatedEntityId IS NULL
AND SubscriptionSpecific = 0) AS D
WHERE D.RowNum > 1 AND
D.LastModified < @RetentionDateTime AND
D.EntityTransactionLogId <= @SubscriptionWatermark;
How many Relationships need to be groomed from ECL based on the GroomChangeLogs criteria?
--declare variables
DECLARE @RetentionDateTime DateTime;
DECLARE @SubscriptionWatermark bigint;
DECLARE @RetentionPeriod int = 120; --<<<<< This is where you set your retention days, 120 days in this example
--Set variables
SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();
SELECT @RetentionDateTime = DATEADD(d, -@RetentionPeriod, getutcdate())
--Count Relationships to be groomed
SELECT count (1) EntityChangeLogId
FROM dbo.EntityChangeLog
WHERE RelatedEntityId IS NOT NULL AND
LastModified < @RetentionDateTime AND
EntityTransactionLogId <= @SubscriptionWatermark;
Next is GroomStagedChangeLogs . How many entities need to be groomed from ECL based on the GroomStagedChangeLogs criteria?
--declare variables
DECLARE @RetentionDateTime DateTime;
DECLARE @RetentionPeriod int = 120; --<<<<< This is where you set your retention days, 120 days in this example
--Set variables
SELECT @RetentionDateTime = DATEADD(d, -@RetentionPeriod, getutcdate())
--Count entities to be groomed by GroomStagedChangeLogs
SELECT count (1) EntityChangeLogId
FROM EntityChangeLog ECL
WHERE ECL.ChangeType = 3 AND -- 3:Staged
ECL.LastModified < @RetentionDateTime;
Finally, GroomSubscriptionSpecificECLRows . How many entities need to be groomed based on the GroomSubscriptionSpecificECLRows criteria?
--Declare and set the subscription watermark
DECLARE @SubscriptionWatermark bigint = dbo.fn_GetEntityChangeLogGroomingWatermark();
SELECT count (1) EntityChangeLogId
FROM dbo.EntityChangeLog ECL
WHERE ECL.SubscriptionSpecific = 1
AND ECL.EntityTransactionLogId <= @SubscriptionWatermark;
All three of the above stored procedures call into GroomChangeLogsInternal to do the actual work of the grooming. Keep in mind that GroomChangeLogsInternal keeps the database integrity intact during the groom process which can result in a cascading deletion of the specific Managed Type Log tables too. This means that the counts above are only an estimate. Running the above grooming jobs may actually result in more rows being groomed than the results of these queries show. This is especially true for the deletion of the EntityChangeLog rows.
You will likely find that one of the above grooming areas is more overgrown than another, so knowing the counts from each allows you to focus on a single area or areas. For instance, if you're seeing GroomStagedChangeLogs failures in the event log, and your counts are fairly high in this area, you can run the stored procedure manually. Just be sure that you have a good backup of the CMDB first, and then do this in quiet times after hours with the Services stopped on all the Service Manager Management Servers.
Below are the commands to run each of the above stored procedures manually. You must set the Data Retention time in minutes, so you'll need to convert your History Retention setting from days to minutes. In the examples below I'll again use 120 days as my retention setting. Note that only the first one takes a true retention period in minutes, the rest simply use 0 minutes.
The first parameter passed is for System.Entity , the second is the retention time in minutes, the third is NULL and not needed for these procedures, and the fourth is the batch size, which I've set specifically to 5000. You may want to raise this a bit more, or lower it depending on how long it takes each of these to run. You may find that your optimal setting is something other than the 5000 I have here in the examples:
GroomChangeLogs:
Exec dbo.p_GroomChangeLogs '55270A70-AC47-C853-C617-236B0CFF9B4C', 172800, N'', 5000
GroomStagedChangeLogs:
Exec dbo.p_GroomStagedChangeLogs '55270A70-AC47-C853-C617-236B0CFF9B4C', 0, N'' , 5000
GroomSubscriptionSpecificECLRows:
Exec dbo.p_GroomSubscriptionSpecificECLRows '55270A70-AC47-C853-C617-236B0CFF9B4C', 0, N'', 5000
Let me toss in one more wrinkle: Since RelatedEntityChangeLog is closely tied into EntityChangeLog by the way it works to track relationships, we need to look at one more stored procedure:
GroomSubscriptionSpecificRECLRows:
How many rows need to be deleted from RelatedEntityChangeLog based on the GroomSubscriptionSpecificRECLRows criteria?
--Declare and set the subscription watermark
DECLARE @SubscriptionWatermark bigint = dbo.fn_GetEntityChangeLogGroomingWatermark();
-- Count rows that need to be groomed from the RelatedEntityChangeLog
SELECT COUNT (1) RECL
FROM dbo.RelatedEntityChangeLog RECL
WHERE RECL.EntityTransactionLogId <= @SubscriptionWatermark
To run GroomSubscriptionSpecificRECLRows manually:
Exec dbo.p_GroomSubscriptionSpecificRECLRows '55270A70-AC47-C853-C617-236B0CFF9B4C', 0, N'',
One more query for you, and then we're done. To get an overall look at your top row counts in Service Manager, run the "Large Table Query" at the top of Kevin Holman's post for Operations Manager (see Useful Operations Manager 2007 SQL queries ). This works fine on the Service Manager database and gives you the top 1000 tables. Look at the row counts. If you've got some very big ones, you may need to investigate whether or not they're related to grooming and/or grooming-related issues.
This series of posts just touches the surface of what Service Manager grooming does behind the scenes to keep things neat and tidy in the Service Manager database. There are many more grooming stored procedures, and not all of them have "groom" in the name, so they can be difficult to spot.
Remember to always back up the Service Manager Database (CMDB) before running any stored procedures manually as these do delete rows from the database. That way if you notice something went awry and you're not happy with the results, you'll be happy have that backup.
Scott Walker | Senior Support Escalation Engineer | Microsoft GBS Management and Security Division
Get the latest System Center news on Facebook and Twitter :
System Center All Up: http://blogs.technet.com/b/systemcenter/
Configuration Manager Support Team blog:
http://blogs.technet.com/configurationmgr/
Data Protection Manager Team blog:
http://blogs.technet.com/dpm/
Orchestrator Support Team blog:
http://blogs.technet.com/b/orchestrator/
Operations Manager Team blog:
http://blogs.technet.com/momteam/
Service Manager Team blog:
http://blogs.technet.com/b/servicemanager
Virtual Machine Manager Team blog:
http://blogs.technet.com/scvmm
WSUS Support Team blog: http://blogs.technet.com/sus/
The RMS blog: http://blogs.technet.com/b/rms/
App-V Team blog: http://blogs.technet.com/appv/
MED-V Team blog: http://blogs.technet.com/medv/
Server App-V Team blog: http://blogs.technet.com/b/serverappv
The Surface Team blog: http://blogs.technet.com/b/surface/
The Application Proxy blog: http://blogs.technet.com/b/applicationproxyblog/
The Forefront Endpoint Protection blog :
http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog :
http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog:
http://blogs.technet.com/b/isablog/
The Forefront UAG blog:
http://blogs.technet.com/b/edgeaccessblog/
SCSM 2012 R2