question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked db042190-2983 answered

why does view history on sql agent jobs sometimes show more than 1 line for a job step?

Hi we run 2014 enterprise. I am wondering why view history on sql agent jobs in ssms sometimes shows 2 or 3 lines for the same job step.

201515-picturemultiplestepsinagenthistory.png


sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The messages do not seem to be the same. That seems like it have something to do with it. Have you looked at these messages in more details. What kind of job steps are they?

(112 job step, egads!)

0 Votes 0 ·

Hi @db042190-2983,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @db042190-2983,

Welcome to Microsoft Q&A!
The default message that is saved in the job history is 1024 characters. In most cases this may be enough, but if you have a long running process you may need to store more than 1024 characters.

Best regards,
Seeya


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

thx seeya. why would the duration impact the size of the message?

erland, i'm going back to look at these but as i recall there was nothing useful or out of the ordinary in the additional entries.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered db042190-2983 commented

Are you sure you are looking at the same job name?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered ErlandSommarskog commented

hi all. the job step that has 3 entries has the following messages...and was a long running step relatively speaking at close to 2hrs...

Executed as user: xxxx. The step succeeded.
started...finished....elapsed...
Microsoft (R) SQL Server Execute Package Utility Version 12.0.6433.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 6:14:49 AM DTExec: The package execution returned DTSER_SUCCESS (0).

a step that has 2 entries from that day and ran close to 4 minutes had the following...

Executed as user: xxxx. The step succeeded.
Microsoft (R) SQL Server Execute Package Utility Version 12.0.6433.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 8:28:02 AM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 8:28:02 AM Finished: 8:31:51 AM Elapsed: 229.094 seconds

a step that has one entry and ran for 18 seconds looked like this

Executed as user: xxxx. Microsoft (R) SQL Server Execute Package Utility Version 12.0.6433.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 8:31:53 AM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 8:31:53 AM Finished: 8:32:08 AM Elapsed: 15.609 seconds. The package executed successfully. The step succeeded.





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So it seems that no matter there is one, two or three messages, there is the same information. For some reason when the jobs runs for a longer period of time, the information is split up on multiple messages. Why? Because the sky is blue, I guess.

Or do you have a real problem because of the multiple messages?

0 Votes 0 ·
JingyangLi avatar image
0 Votes"
JingyangLi answered

Can you run this query to see whether you can get more information about your job steps?
select
j.name ,
s.step_id ,
s.step_name ,
message,
* From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h
ON s.job_id = h.job_id
AND s.step_id = h.step_id
AND h.step_id <> 0
--where j.name = 'yourjobname'

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered JingyangLi commented

ok, i've done that and see the same granularity that the ssms history shows for that run date.

i used the snipping tool to grab the last few chars of the job name, step id and first few letters of step name on a step that has 3 entries.

What would you like me to do now?

203637-snippedofjobhistinfoformsdn.png




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Expand the message column from the query to see the detailed info for these entries. Not sure whether they same or not but it may provide more than the job history UI can.

0 Votes 0 ·
db042190-2983 avatar image
0 Votes"
db042190-2983 answered

thx erland, no problems, just started poking my nose in on a step that on occasion runs too long.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.