Toad World® Forums

Toad SQL Agent Jobs - History - Job Step Details - Can Layout be improved?

Feature Request: Add some formatting to the Agent Job history output.

One thing I find annoying is the task of reading through agent job history in either SSMS or in Toad because the job step details end up being displayed as one big run-on sentence.

I end up copying it out to a word document, and then doing simple things like using search and replace of every Exec statement to start with a new paragraph mark.

Any plans to improve this output in Toad?

Darren

PS - Here’s an example of the output of a script I was troubleshooting:
Executed as user: FAM\SQLServer. …2500.0 for 64-bit Copyright © Microsoft Corporation 2010. All rights reserved. Started: 1:00:00 AM Progress: 2013-04-28 01:00:04.54 Source: {1DE0E9E4-8AD4-4F7F-9EFB-47DA313749B6} Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb…sp…”.: 100% complete End Progress Progress: 2013-04-28 01:00:07.02 Source: Check Database Integrity Task Executing query "USE [ReportServer] ".: 50% complete End Progress Progress: 2013-04-28 01:00:09.01 Source: Check Database Integrity Task Executing query "DBCC CHECKDB(N’ReportServer’) WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2013-04-28 01:00:09.02 Source: Check Database Integrity Task Executing query "USE [ReportServerTempDB] ".: 50% complete End Progress Progress: 2013-04-28 01:00:09.54 Source: Check Database Integrity Task Executing query “DBCC CHECKDB(N’ReportServerTempDB’) WITH NO_INFOM…”.: 100% complete End Progress Progress: 2013-04-28 01:00:09.54 Source: Check Database Integrity Task Executing query "USE [BL_Fam_Playground] ".: 50% complete End Progress Error: 2013-04-28 01:00:18.09 Code: 0xC002F210 Source: Check Database Integrity Task Execute SQL Task Description: Executing the query “DBCC CHECKDB(N’BL_Fam_Playground’) WITH NO…” failed with the following error: "The In-row data USED page count for object “wv-Configuration”, index ID 9, partition ID 2611151441559552, alloc unit ID 2611151441559552 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data RSVD page count for object “wv-Configuration”, index ID 9, partition ID 2611151441559552, alloc unit ID 2611151441559552 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data USED page count for object “wv-SessionVariablesTable”, index ID 2, partition ID 655506720882688, alloc unit ID 655506720882688 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data RSVD page count for object “wv-SessionVariablesTable”, index ID 2, partition ID 655506720882688, alloc unit ID 655506720882688 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data USED page count for object “wv-SessionVariablesTable”, index ID 3, partition ID 936981697593344, alloc unit ID 936981697593344 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data RSVD page count for object “wv-SessionVariablesTable”, index ID 3, partition ID 936981697593344, alloc unit ID 936981697593344 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data USED page count for object “wv-StringsTable”, index ID 2, partition ID 657603880353792, alloc unit ID 657603880353792 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data RSVD page count for object “wv-StringsTable”, index ID 2, partition ID 657603880353792, alloc unit ID 657603880353792 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data USED page count for object “wv-StringsTable”, index ID 3, partition ID 939078857064448, alloc unit ID 939078857064448 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data RSVD page count for object “wv-StringsTable”, index ID 3, partition ID 939078857064448, alloc unit ID 939078857064448 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data USED page count for object “wv-StringsTable”, index ID 4, partition ID 1220553833775104, alloc unit ID 1220553833775104 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data RSVD page count for object “wv-StringsTable”, index ID 4, partition ID 1220553833775104, alloc unit ID 1220553833775104 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data USED page count for object “wv-StringsTable”, index ID 6, partition ID 1783503787196416, alloc unit ID 1783503787196416 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data RS… The package execution fa… The step failed.

Here’s the same output after pasting it into a word document and doing 2 things:

  1. Replacing ". " with “. ^p” (the paragraph symbol)
  2. Replacing “Exec” with “^pExec” to have it start each exec statement on a new line.

Here’s the (much easier to read) output after doing just those two simple things. I was hoping we could see something similar to that simple parsing code in Toad:

Executed as user: FAM\SQLServer.

…2500.0 for 64-bit Copyright © Microsoft Corporation 2010.

All rights reserved.

Started: 1:00:00 AM Progress: 2013-04-28 01:00:04.54 Source: {1DE0E9E4-8AD4-4F7F-9EFB-47DA313749B6}

Executing query "DECLARE @Guid UNIQUEIDENTIFIER

EXECUTE msdb…sp…".: 100% complete End Progress Progress: 2013-04-28 01:00:07.02 Source: Check Database Integrity Task

Executing query "USE [ReportServer] ".: 50% complete End Progress Progress: 2013-04-28 01:00:09.01 Source: Check Database Integrity Task

Executing query "DBCC CHECKDB(N’ReportServer’) WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2013-04-28 01:00:09.02 Source: Check Database Integrity Task

Executing query "USE [ReportServerTempDB] ".: 50% complete End Progress Progress: 2013-04-28 01:00:09.54 Source: Check Database Integrity Task

Executing query “DBCCCHECKDB(N’ReportServerTempDB’) WITHNO_INFOM…”.: 100% complete EndProgress Progress: 2013-04-2801:00:09.54 Source: Check DatabaseIntegrity Task

Executing query "USE [BL_Fam_Playground] ".: 50% complete End Progress Error: 2013-04-28 01:00:18.09 Code: 0xC002F210 Source: CheckDatabase Integrity Task

Execute SQL Task Description:

Executing the query “DBCCCHECKDB(N’BL_Fam_Playground’) WITHNO…” failed with the following error: "The In-row data USED pagecount for object “wv-Configuration”, index ID 9, partition ID 2611151441559552,alloc unit ID 2611151441559552 (type In-row data) is incorrect.

Run DBCC UPDATEUSAGE.

The In-row data RSVDpage count for object “wv-Configuration”, index ID 9, partition ID2611151441559552, alloc unit ID 2611151441559552 (type In-row data) isincorrect.

Run DBCC UPDATEUSAGE.

The In-row data USEDpage count for object “wv-SessionVariablesTable”, index ID 2,partition ID 655506720882688, alloc unit ID 655506720882688 (type In-row data)is incorrect.

Run DBCC UPDATEUSAGE.

The In-row data RSVDpage count for object “wv-SessionVariablesTable”, index ID 2,partition ID 655506720882688, alloc unit ID 655506720882688 (type In-row data)is incorrect.

Run DBCC UPDATEUSAGE.

The In-row data USEDpage count for object “wv-SessionVariablesTable”, index ID 3,partition ID 936981697593344, alloc unit ID 936981697593344 (type In-row data)is incorrect.

Run DBCC UPDATEUSAGE.

The In-row data RSVDpage count for object “wv-SessionVariablesTable”, index ID 3,partition ID 936981697593344, alloc unit ID 936981697593344 (type In-row data)is incorrect.

Run DBCC UPDATEUSAGE.

The In-row data USEDpage count for object “wv-StringsTable”, index ID 2, partition ID657603880353792, alloc unit ID 657603880353792 (type In-row data) is incorrect.

Run DBCC UPDATEUSAGE.

The In-row data RSVDpage count for object “wv-StringsTable”, index ID 2, partition ID657603880353792, alloc unit ID 657603880353792 (type In-row data) is incorrect.

Run DBCC UPDATEUSAGE.

The In-row data USEDpage count for object “wv-StringsTable”, index ID 3, partition ID939078857064448, alloc unit ID 939078857064448 (type In-row data) is incorrect.

Run DBCC UPDATEUSAGE.

The In-row data RSVDpage count for object “wv-StringsTable”, index ID 3, partition ID939078857064448, alloc unit ID 939078857064448 (type In-row data) is incorrect.

Run DBCC UPDATEUSAGE.

The In-row data USEDpage count for object “wv-StringsTable”, index ID 4, partition ID1220553833775104, alloc unit ID 1220553833775104 (type In-row data) isincorrect.

Run DBCC UPDATEUSAGE.

The In-row data RSVDpage count for object “wv-StringsTable”, index ID 4, partition ID1220553833775104, alloc unit ID 1220553833775104 (type In-row data) isincorrect.

Run DBCC UPDATEUSAGE.

The In-row data USEDpage count for object “wv-StringsTable”, index ID 6, partition ID1783503787196416, alloc unit ID 1783503787196416 (type In-row data) isincorrect.

Run DBCC UPDATEUSAGE.

The In-row data RS…

The package

Execution fa…

The step failed.

Hi Darren,

Thank you for your new feature request.
Can you please give us more background on what you do with job history, i.e.:

  • how often you are reviewing history?
  • what’s the most important you are trying to find there?
  • what’s your action based on findings?

I suppose the reason to review history is different depending on what the job is doing.

In this example I was trying to figure out why a weekly database maintenance plan kept failing. In this context, the action I wanted to take was to actually run the DBCC CheckDB commands and subsequently the DBCC UPDATEUSAGE TableName commands.

How often? Weekly
Most important: Pass/Fail.
Next most important: For anything that did fail, out of all the dump of data that gets output in that agent history where is the actionable information? What actually failed vs. what’s just status information. Highlighting that somehow would save time.
Getting rid of the massive run-on-sentence-with-no-paragraph-breaks would also save time by making it easier to read.

Actions? Depends on cause of failure. Sometimes the results actually provide specific directions as to what to do to fix the issue.

Hi Darren,
Just for the reference - CR109839 created for this request.

thanks for the post.