Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 21 '13 at 20:08

This question came from our site for professional and enthusiast programmers.

3 Answers 3

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.

share|improve this answer

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*/
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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