Failed tasks aren’t groomed from the Operational Database


This appears to be present up to RC-SP1 version, build 6.0.6246.0

 

In the Task Status console view - I noticed an old failed task from 2 months ago..... however, my task grooming is set to 7 days.

 

To view the grooming process:

http://blogs.technet.com/kevinholman/archive/2007/12/13/how-grooming-and-auto-resolution-work-in-the-opsmgr-2007-operational-database.aspx

Basically – select * from PartitionAndGroomingSettings will show you all grooming going on.

Tasks are kept in the jobstatus table.

Select * from jobstatus will show all tasks.

p_jobstatusgrooming is called to groom this table.

Here is the text of that SP:

--------------------------------

USE [OperationsManager]

GO

/****** Object:  StoredProcedure [dbo].[p_JobStatusGrooming]    Script Date: 02/05/2008 10:49:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[p_JobStatusGrooming]

AS

BEGIN

SET NOCOUNT ON

DECLARE @Err int

DECLARE @Ret int

DECLARE @RowCount int

DECLARE @SaveTranCount int

DECLARE @GroomingThresholdLocal datetime

DECLARE @GroomingThresholdUTC datetime

DECLARE @TimeGroomingRan datetime

DECLARE @MaxTimeGroomed datetime

SET @SaveTranCount = @@TRANCOUNT

SET @TimeGroomingRan = getutcdate()

SELECT @GroomingThresholdLocal = dbo.fn_GroomingThreshold(DaysToKeep, getdate())

FROM dbo.PartitionAndGroomingSettings

WHERE ObjectName = 'JobStatus'

EXEC dbo.p_ConvertLocalTimeToUTC @GroomingThresholdLocal, @GroomingThresholdUTC OUT

IF (@@ERROR <> 0)

BEGIN

GOTO Error_Exit

END

-- Selecting the max time to be groomed to update the table

SELECT @MaxTimeGroomed = MAX(LastModified)

FROM dbo.JobStatus

WHERE TimeFinished IS NOT NULL

AND LastModified < @GroomingThresholdUTC  

IF @MaxTimeGroomed IS NULL

GOTO Success_Exit

BEGIN TRAN

-- Change the Statement below to reflect the new item

-- that needs to be groomed

DELETE FROM dbo.JobStatus

WHERE TimeFinished IS NOT NULL

AND LastModified < @GroomingThresholdUTC

SET @Err = @@ERROR

IF (@Err <> 0)

BEGIN

GOTO Error_Exit

END

UPDATE dbo.PartitionAndGroomingSettings

SET GroomingRunTime = @TimeGroomingRan,

        DataGroomedMaxTime = @MaxTimeGroomed

WHERE ObjectName = 'JobStatus'

SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT

IF (@Err <> 0 OR @RowCount <> 1)

BEGIN

GOTO Error_Exit

END

COMMIT TRAN

Success_Exit:

RETURN 0

Error_Exit:

-- If there was an error and there is a transaction

-- pending, rollback.

IF (@@TRANCOUNT > @SaveTranCount)

ROLLBACK TRAN

RETURN 1

END

------------------------------------

 

 

Here is the problem in the SP:

 

DELETE FROM dbo.JobStatus

WHERE TimeFinished IS NOT NULL

AND LastModified < @GroomingThresholdUTC

 

 

We only delete (groom) tasks that have a timestamp in TimeFinished.  If a failed task doesn’t finish – this field will be NULL and never gets groomed.

Comments (2)

  1. Brian W. says:

    So, I executed

    Select * from jobstatus

    where TimeFinished is null

    and found 41 rows that exist.  If I execute the stored proc, but change it to the below (removing the TimeFinished IS NOT NULL critera) will that create a problem?

    DELETE FROM dbo.JobStatus

    WHERE LastModified < @GroomingThresholdUTC

    my settings are below

    ObjectId ObjectName IsPartitioned InsertViewName GroomingSproc DaysToKeep GroomingRunTime DataGroomedMaxTime IsInternal

    1 Event 1 EventInsertView NULL 14 2013-03-09 05:00:01.060 2013-03-07 04:59:59.000 0

    2 PerformanceData 1 PerformanceDataInsertView NULL 8 2013-03-13 04:00:00.513 2013-03-05 04:59:55.000 0

    3 Alert 0 NULL p_AlertGrooming 7 2013-03-13 04:00:00.670 2013-03-06 04:11:06.277 0

    4 StateChangeEvent 0 NULL p_StateChangeEventGrooming 5 2013-03-13 07:22:38.140 2013-03-08 04:22:42.150 0

    5 MaintenanceModeHistory 0 NULL p_MaintenanceModeHistoryGrooming 3 2013-03-12 04:00:02.880 2013-03-10 19:17:29.023 0

    6 AvailabilityHistory 0 NULL p_AvailabilityHistoryGrooming 7 2013-03-09 05:00:07.983 2013-03-06 02:07:37.987 0

    7 JobStatus 0 NULL p_JobStatusGrooming 2 2013-03-13 04:00:09.980 2013-03-10 21:23:58.390 0

    8 MonitoringJobStatus 0 NULL p_MonitoringJobStatusGrooming 2 2013-03-13 04:00:10.193 2013-03-11 04:18:09.543 0

    9 PerformanceSignature 0 NULL p_PerformanceSignatureGrooming 2 2013-03-13 04:00:11.123 2012-06-03 00:00:00.000 0

    10 PendingSdkDataSource 0 NULL p_PendingSdkDataSourceGrooming 1 2012-06-03 00:00:00.000 2012-06-03 00:00:00.000 1

    11 InternalJobHistory 0 NULL p_InternalJobHistoryGrooming 60 2013-03-13 04:00:11.137 2012-06-03 00:00:00.000 1

    15 EntityChangeLog 0 NULL p_EntityChangeLogGroom 7 NULL NULL 1

    16 UserSettingsStore 0 NULL p_UserSettingsStoreGrooming 60 NULL NULL 1

Skip to main content