none
FIM Reporting and SCSM Database Query Issue RRS feed

  • Question

  • Hello,

    We have been having issues with FIM Reporting, the ETL Process for some reason seems to be failing, we further drilled down and found that there was a SQL Query running on the SCSM database Server for a very long time.

    "CREATE PROCEDURE dbo.[p_GroomManagedEntity]  (      @TargetId uniqueidentifier,      @RetentionPeriodInMinutes int,      @GroomingCriteria nvarchar(max),      @BatchSize int  )  AS  BEGIN      DECLARE @LastErr int;      DECLARE @RowCount int = 1;      DECLARE @TotalRowCount int = 0;      DECLARE @RetentionDateTime DATETIME;      DECLARE @SelectEntitiesToBeGroomedStmt nvarchar(max);      DECLARE @CoreDeleteTypedEntitiesTable TypedManagedEntityType;      DECLARE @TimeGenerated DATETIME = getutcdate();      DECLARE @Command nvarchar(MAX)      DECLARE @GroomHistoryId bigint      DECLARE @Comment nvarchar(max);          SET @Command = N'Exec dbo.p_GroomManagedEntity ' + CAST(@TargetId AS nvarchar(40)) + ', ' + CAST(@RetentionPeriodInMinutes  AS nvarchar(10)) + ', ' + CAST(@GroomingCriteria  AS nvarchar(100)) + ', ' + CAST(@BatchSize AS nvarchar(10))         -- Call the grooming history insert sproc       EXEC @LastErr = dbo.p_InternalJobHistoryInsert @Command, @GroomHistoryId OUT      IF @LastErr <> 0          GOTO Err;        CREATE TABLE #BaseManagedEntitiesToDelete      (          BaseManagedEntityId uniqueidentifier      );          -- Figure out the retention datetime      SELECT @RetentionDateTime = DATEADD(mi, -@RetentionPeriodInMinutes, getutcdate())        -- Execute the grooming filter statement, hence populate the table variable, with "BatchSize" many entities.      WHILE (@RowCount > 0)      BEGIN          INSERT #BaseManagedEntitiesToDelete EXEC sp_executesql @GroomingCriteria, N'@Retention DATETIME,@TargetTypeId uniqueidentifier,@NumOfEntities INT',                   @Retention = @RetentionDateTime, @TargetTypeId = @TargetId, @NumOfEntities = @BatchSize;            SELECT @LastErr = @@ERROR, @RowCount = @@ROWCOUNT;          IF @LastErr <> 0              GOTO Err;                    IF (@RowCount > 0)          BEGIN              -- Convert the BMEIds to TMEIds.              INSERT @CoreDeleteTypedEntitiesTable              SELECT TME.TypedManagedEntityId              FROM #BaseManagedEntitiesToDelete D              JOIN dbo.TypedManagedEntity TME                  ON D.BaseManagedEntityId = TME.BaseManagedEntityId              WHERE TME.IsDeleted = 0;                                       SELECT @LastErr = @@ERROR;              IF @LastErr <> 0                  GOTO Err;                                -- Use existing DDP code to delete the instances captured in the temp table.                  EXEC @LastErr = dbo.p_DDPWrapperForGroomManagedEntity @TimeGenerated, @CoreDeleteTypedEntitiesTable;              IF @LastErr <> 0                  GOTO Err;                                TRUNCATE TABLE #BaseManagedEntitiesToDelete;              SELECT @LastErr = @@ERROR;              IF @LastErr <> 0                  GOTO Err;          END                    SET @TotalRowCount = @TotalRowCount + @RowCount;      END            -- Call the grooming history insert sproc to update status to success      SET @Comment = N'BaseManagedEntity: ' + CAST(@TotalRowCount AS nvarchar(10))      EXEC @LastErr = dbo.p_InternalJobHistoryUpdate @GroomHistoryId, 1, @Comment      IF @LastErr <> 0          GOTO Err;        RETURN 0        Err:        -- Call the grooming history insert sproc to update status to failure.      SET @Comment = N'BaseManagedEntity: ' + CAST(@TotalRowCount AS nvarchar(10))      EXEC @LastErr = dbo.p_InternalJobHistoryUpdate @GroomHistoryId, 2, @Comment      IF @LastErr <> 0          GOTO Err;        RETURN 1  END"

    Can somebody advise on what this query is really about and what is its fuction, we are thinking of killing this query since it has been running for a very long time, will that hamper or cause the database to corrupt.

    Rgds,

    Abhishek.

    Monday, May 5, 2014 11:04 AM