locked
JobStatus table is 25GB with 40K records, is that normal? RRS feed

  • Question

  • I've noticed that the size consumed by the JobStatus table continues to increase even though the number of records generally remains flat.  It grows by about a gig a month and is currently 25GB.  I've got the system to set to groom job records over seven days old but the storage footprint never decreases and only continues to grow.  Is that at all normal or is there some sort of cleanup script/query that I can run?
    Friday, October 23, 2015 9:34 PM

Answers

All replies

  • Maybe this blog post is helpful: More Aggressively Grooming the JobStatus and WindowsWorkflowTaskJobStatus Tables

    Andreas Baumgarten | H&D International Group

    Saturday, October 24, 2015 10:24 PM
  • Just to expand on Andreas' answer, then you could also check if the grooming jobs is not running for whatever reason.

    The below SQL query will tell you that:

    SELECT 
       InternalJobHistoryId, 
       Command, 
       TimeStarted 
    FROM InternalJobHistory WITH(NOLOCK) 
    WHERE 
       TimeFinished IS NULL AND 
       StatusCode <> 1 

    Source: http://blogs.technet.com/b/mihai/archive/2012/07/13/service-manager-slow-perfomance.aspx

    Sunday, October 25, 2015 10:41 AM
  • Andreas, I implemented those measures around January.  Grooming settings were more than cut in half.

    Morten, there are a couple of grooming jobs that fail almost daily but I don't know if they are the one that grooms the JobStatus table.  I am pretty sure that it is being groomed as the number of records dropped from 70K to 35K after adjusting the job history grooming settings and it currently fluctuates between 30K and 40K records.

    The grooming jobs that fail are:

    Exec dbo.p_GroomStagedChangeLogs 55270A70-AC47-C853-C617-236B0CFF9B4C, 0, , 1000
    
    -- and 
    
    Exec dbo.p_GroomSubscriptionSpecificECLRows 55270A70-AC47-C853-C617-236B0CFF9B4C, 0, , 1000

    What's baffling me is why the table never did shrink and only continues to take up more and more space.  Now, I am getting the table disk space numbers from the 'Disk Usage by Top Tables' Report in the SQL Server Management Studio.

    Sunday, October 25, 2015 1:29 PM
  • I'm no DBA, but I'm pretty sure that the space reserved for the table will stay the same even though the actual size of the table has fallen. You could check that in the Reports yes. Not sure what to do about it other than shrinking the files, but probably good to talk to an DBA before doing so :). 

    Regarding the grooming jobs, then you could try to run them seperately to see if they time out or you receive an error.

    Sunday, October 25, 2015 1:43 PM
  • I had the same issiue.

    30K entrys and 20 GB of size.

    I delted all the entrys in the table and also changed the grooming to 5 days for the logs. Since then the JobStatus has 4 GB and 20K entrys.

    brgds

    Wednesday, November 25, 2015 2:17 PM