locked
Job history of just 1 job missing RRS feed

  • Question

  • Hi,

    I have a couple of jobs on my SQL server 2005. For just 1 job, only the recent history is shown in the job history. Other jobs have the olders history.

    Could someone please help me with this?

    Monday, February 18, 2013 8:00 AM

Answers

  • Hi Tintu

    If you are not able to find history of one particular job where as for others its there then there can be no. of possibilities

    a. Job didn't run at all , b. There was a filter , c. someone have purged history of your job

    To find you can try below queries

    /* Job history should be available  ' &/ 
    select * from msdb.dbo.sysjobhistory
     
    
     
     
    /* To delete  history for a specific job */
    USE msdb ;
    GO
    EXEC dbo.sp_purge_jobhistory
    @job_name = N'Test job' ,
    @oldest_date = '2008-10-01'
     
     
    /*To check who is running job history purge you can try*/
     
     SELECT DB_NAME (DatabaseID) ,* FROM 
    fn_trace_gettable( convert (varchar(1000),
    'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_PROD2\MSSQL\Log\log_1.trc')  , default) 
    where DatabaseID = 4  --msdb
    order by starttime desc

    Thanks

    Saurabh Sinha

    • Marked as answer by Tintu Jose Monday, February 18, 2013 9:37 AM
    Monday, February 18, 2013 9:32 AM

All replies

  • Hello,

    Make sure that filter is not applied.

    Also get the job_id from "select * from msdb..sysjobs"  and check in : select * from msdb..sysjobhistory where job_id='<job_id>'

    Monday, February 18, 2013 9:12 AM
  • sysjobhistory table has data. But it is not shown in the SQL Agent Job History GUI. Only the recent 1 is showing there..

    No filter applied..

    Monday, February 18, 2013 9:24 AM
  • Hi Tintu

    If you are not able to find history of one particular job where as for others its there then there can be no. of possibilities

    a. Job didn't run at all , b. There was a filter , c. someone have purged history of your job

    To find you can try below queries

    /* Job history should be available  ' &/ 
    select * from msdb.dbo.sysjobhistory
     
    
     
     
    /* To delete  history for a specific job */
    USE msdb ;
    GO
    EXEC dbo.sp_purge_jobhistory
    @job_name = N'Test job' ,
    @oldest_date = '2008-10-01'
     
     
    /*To check who is running job history purge you can try*/
     
     SELECT DB_NAME (DatabaseID) ,* FROM 
    fn_trace_gettable( convert (varchar(1000),
    'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_PROD2\MSSQL\Log\log_1.trc')  , default) 
    where DatabaseID = 4  --msdb
    order by starttime desc

    Thanks

    Saurabh Sinha

    • Marked as answer by Tintu Jose Monday, February 18, 2013 9:37 AM
    Monday, February 18, 2013 9:32 AM
  • Thanks Saurabh..

    I think the History is purged..

    Monday, February 18, 2013 9:37 AM