Post reply

CHECKDB Job running for a very long time

  • July 19, 2016 at 5:32 am

    #320040

    Good Morning Experts,

    CHECKDB Job has been running for a very long time. I checked sys.sysprocesses table and it is showing lastwaittype as LATCH_EX and waitresource as DBCC_OBJECT_METADATA. It is also showing self-blocking. I have attached the output.Could you please help on how to fix.

    Thanks

  • July 19, 2016 at 5:44 am

    #1890816

    What does this return?SELECT wait_duration, wait_type, blocking_session_id

    FROM sys.dm_os_waiting_tasks

    WHERE session_id = 297

    You may wish to execute the query a few times to see whether it changes.

    John

    Edit - corrected typo in query

  • July 19, 2016 at 7:12 am

    #1890848

    Hi John,

    The job finally completed. I want to know what action do i need to take if it repeats again

    Thanks

  • July 19, 2016 at 7:20 am

    #1890852

    Nothing you've said indicates a problem that needs fixing.

    Sys.dm_exec_requests will frequently show a process as 'self-blocking' when that process runs in paralle (as CheckDB does) and the waits are normal ones to see when running CheckDB.

    The time you need to be concerned is when CheckDB takes much longer than usual, as that may indicate that it's found a problem which it has to investigate further.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2016 at 7:30 am

    #1890855

    Good Morning Gail,

    Actually, the job took much longer than usual to complete today. It was showing lastwaittype as LATCH_EX and waitresource as DBCC_OBJECT_METADATA. It was also showing self-blocking. I have attached the output. If this repeats again, what are the steps i should follow to fix

    Thanks

  • July 19, 2016 at 7:32 am

    #1890857

    GilaMonster (7/19/2016)

    Sys.dm_exec_requests will frequently show a process as 'self-blocking' when that process runs in parallel (as CheckDB does) and the waits are normal ones to see when running CheckDB.

    Was the output of checkDB clean, or did it report corruption?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2016 at 11:03 am

    #1890917

    Hi Gail,

    The output was clean

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply