Resolve issues with SCOM Maintenance Mode

SCOM is having maintenance mode feature from 2007 R2 onwards which is useful when you need to set objects into maintenance mode from current start time to  a future time.

However, starting from SCOM 2016 we get the niche feature of scheduling maintenance mode in advance.

Even in earlier version of SCOM you can schedule maintenance mode. Check this link for more information

Now going to the things that make this possible.

Backend there is a SQL table “dbo.p_MaintenanceModeJob” which is queried by stored procedure stored procedure called “dbo.p_ScheduledJobsEveryFiveMinutue”, as the name says it runs every 5 minutes.

That is the reason, the minimum time for maintenance mode to set is 5 mins and also this is the reason that  sometimes it can take upto 5 minutes to get an object out of maintenance mode

 

So, if you are in a situation where you see that your scom objects are not coming out of maintenance mode or going into maintenance mode automatically have a check on this procedure. Check if this stored procedure is running or not.

The Stored procedure  that gets executed by the “dbo.p_ScheduledJobsEveryFiveMinutues”

USE [OperationsManager]

GO

/****** Object:  StoredProcedure [dbo].[p_ScheduledJobsEveryFiveMinutes]    Script Date: 9/25/2017 3:20:56 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[p_ScheduledJobsEveryFiveMinutes]

AS

BEGIN

    SET NOCOUNT ON

    EXEC dbo.p_MaintenanceModeJob

            EXEC dbo.p_MaintenanceScheduleJob

         

    EXEC dbo.p_JobStatusTimeout

    RETURN 0

END

and SQL query that it executes is

USE [OperationsManager]

GO

/****** Object:  StoredProcedure [dbo].[p_MaintenanceModeJob]    Script Date: 9/25/2017 3:18:33 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[p_MaintenanceModeJob]

AS

BEGIN

            SET NOCOUNT ON  

    DECLARE @Err int

    DECLARE @TranCount int

    DECLARE @StartTime datetime

    DECLARE @Now datetime

    DECLARE @TempMaintMode TABLE (BaseManagedEntityId uniqueidentifier,

                                 StartTime datetime)

    SET @TranCount = @@TRANCOUNT 

    SET @Now = getutcdate()

    INSERT INTO @TempMaintMode

    SELECT BaseManagedEntityId, StartTime

    FROM dbo.MaintenanceMode

    WHERE EndTime IS NULL AND ScheduledEndTime < @Now

    SET @Err = @@ERROR

    IF (@Err <> 0) GOTO Error_Exit      

    BEGIN TRAN

    UPDATE dbo.MaintenanceMode

    SET IsInMaintenanceMode = 0,

        EndTime = @Now,

        LastModified = @Now

    WHERE BaseManagedEntityId IN (SELECT BaseManagedEntityId FROM @TempMaintMode)

    SET @Err = @@ERROR

    IF (@Err <> 0) GOTO Error_Exit  

    UPDATE dbo.MaintenanceModeHistory

    SET EndTime = @Now,

        LastModified = @Now

    FROM @TempMaintMode AS TM

    WHERE MaintenanceModeHistory.BaseManagedEntityId = TM.BaseManagedEntityId

    AND MaintenanceModeHistory.StartTime = TM.StartTime

    SET @Err = @@ERROR

    IF (@Err <> 0) GOTO Error_Exit  

    COMMIT TRAN

    RETURN 0

Error_Exit:

    IF (@@TRANCOUNT > @TranCount)

    BEGIN

        ROLLBACK TRAN

    END

    RETURN 1

END

 

Advertisements

Author: tadgata

ITSM & System Center Consultant. Have experience on BMC Remedy, CA SDM, Service Now, and Microsoft system center tools including SCSM, SCOM, SCCM, SCORC, SCVMM.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s