4

I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1.
Fist result have the icon for Success with the result :

Message
Executed as user: companyname\sa. The step succeeded.

The second one have the step 1 also, but with the icon for In Progress with the result :

Message
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.4000.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 11:50:00 PM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 11:50:00 PM
Finished: 11:50:26 PM
Elapsed: 26.645 seconds

My problem is I have a report to show all the job with their status not equal to 1 (success), my report do not work anymore because of this result "In progress".
Here is the sql I use to run my report :

SELECT *  from  msdb..sysjobhistory WHERE run_status != 1

My questions are why do I have 2 results for my step ? I think I should have only one. And what should I do when I have a job with a step status left "In progress" ?

For more information, my job is running daily and succeed every time, thank you.

0
1

This will give you the desired results :

select h.server as [Server],
    j.[name] as [Name],
    h.message as [Message],
    h.run_date as LastRunDate, 
    h.run_time as LastRunTime
from msdb.dbo.sysjobhistory h
    inner join msdb.dbo.sysjobs j on h.job_id = j.job_id
        where j.enabled = 1 -- only check for enabled jobs
        and h.instance_id in
        (select max(h.instance_id)
            from msdb.dbo.sysjobhistory h group by (h.job_id))
        and h.run_status != 1 
/*0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled*/
0

Unfortunately I am seeing the same behavior on one of my jobs as well - I'm on SQL Server 2005. The only thing I could find online was that Microsoft was going to fix this in future editions of SQL Server, but it looks like it still occurs given your experience. http://connect.microsoft.com/SQLServer/feedback/details/265568/log-file-viewer. I am seeing this on one job only, and it's been happening over the past week (that's as much history on this job I can view). The job runs a DTS package. We have other jobs that run DTS packages that do not have this behavior.

-2

Check the advanced properties of the job step this is happening on. Are "Output file:", "Log to table" and "Include step output in history" checked? If so, uncheck them and see if this issue goes away.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.