Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • CONSUTING
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 547 other subscribers

Blog Views

1.789.740 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft products (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • 2 May 2018
  • 2
  • Database DMVs and Catalog Views Errors Little Known Features Hacks Maintenance SQL Server SQL Server Agent

SQL Server - How to view the entire return message from Job execution (even when it exceeds 4000 characters)

Views: 1.446
Reading Time: 4 minutes

Hey guys!
Is everything great with you?

In this post, I would like to share with you how to view the entire Job execution return message when the job output is longer than 4.000 characters.

Understanding the scenario and the problem

If we look at the structure of the msdb.dbo.sysjobhistory table, which is where job log messages are written, we can see that its type is nvarchar (8000) (and was varchar (1024) through 2008 version) and that on its own character overhead using UTF-8, supports 4.000 characters only:

That is, when the job message exceeds 4000 characters, it appears cut off (truncated) when you try to view the job execution history. For those who have come across this situation, you know how frustrating it is that you see that the job has failed, but cannot see the error message that caused the error.

This makes it impossible to return the entire message even when querying SQL Agent tables directly, as this limitation is in the table structure itself:

Transact-SQL
1
2
3
4
SELECT A.message
FROM msdb.dbo.sysjobhistory A
JOIN msdb.dbo.sysjobs B ON B.job_id = A.job_id
WHERE B.[name] = 'Teste Mensagem Longa Job'

Query Result:

Example of this scenario:

Simulating this scenario in your environment

To simulate this scenario, I created a job, with just 1 step, that ran this command:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @Contador INT = 1, @Total INT = 1000
 
WHILE(@Contador <= @Total)
BEGIN
    
    PRINT 'Teste do log do Job'
 
    SET @Contador += 1
 
END
 
-- Forçando um erro
SELECT 1/0

The solution to this post problem

Well, now that I've explained how and why this problem occurs, and demonstrated how you can simulate this scenario in your environment (if you are not experiencing this problem now .. lol), I will show you how to solve this problem.

First, let's enable the option to log the result of step into a table:

From now on, messages from this step will be written to the database (msdb.dbo.sysjobstepslogs).

1 Note: Remember that this step must be done for each step you want to enable.
2 Note: Only the last run is recorded in this log. If you want to store all message history, you must check the “Append output to existing entry in table” checkbox.

To view the log message, I will demonstrate some alternatives to do this:

Alternative #1: Using the SSMS Interface

To view the complete message through the SSMS interface, just click on the “View” button on the Step details screen:

This opens an instance of Notepad with the result of the job execution:

Alternative #2: Using the sp_help_jobsteplog System SP

Another way to get the full message of the result of executing the job in question is by using the sp_help_jobsteplog system SP:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE @Retorno TABLE (
    [job_id] UNIQUEIDENTIFIER,
    [job_name] NVARCHAR(128),
    [step_id] INT,
    [step_name] NVARCHAR(128),
    [step_uid] UNIQUEIDENTIFIER,
    [date_created] DATETIME,
    [date_modified] DATETIME,
    [log_size] BIGINT,
    [log] NVARCHAR(MAX)
);
 
INSERT INTO @Retorno
EXEC msdb.dbo.sp_help_jobsteplog
    @job_name = N'Teste Mensagem Longa Job'
 
SELECT SUBSTRING([log], CHARINDEX('Msg ', [log]), LEN([log]))
FROM @Retorno

Result:

Alternative #3: Using SQL Agent Tables

You can also directly query SQL Agent tables in the msdb database to retrieve job return information.

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    B.job_id,
    C.[name],
    A.[log],
    SUBSTRING(A.[log], CHARINDEX('Msg ', A.[log]), LEN(A.[log])) AS Msg_Erro,
    LEN(A.[log])
FROM
    msdb.dbo.sysjobstepslogs  AS A
    JOIN msdb.dbo.sysjobsteps AS B ON B.step_uid = A.step_uid
    JOIN msdb.dbo.sysjobs     AS C ON C.job_id = B.job_id
WHERE
    C.[name] = 'Teste Mensagem Longa Job'

Result:

Well guys, that's it!
Do you know other ways to get this information? Post here in the comments that I will update your post and reference it.

I hope you enjoyed this post and find it helpful 🙂
Regards and see you next post.

sql server sql agent log return message truncate truncated limit limited size 4000 characters characters

sql server sql agent log return message truncate truncated limit limited size 4000 characters characters

tags: errorssqlsql agentsql server

You may also like ...

  • Microsoft MCSE 70-776 (Performing Big Data Engineering on Microsoft Cloud Services) certification test for free (beta) through 08 / 09 / 2017

  • SQL Server - How to disable “sa” login minimizing impacts

  • SQL Server - Encrypting passwords with the symmetric encryption functions ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE

  • Next How was the 7th PASS Local Group SQL Server ES meeting?
  • Previous SQL Server - Study material for the 70-764 certification exam Administering a SQL Database

Comments

  • Comments2
  • Pingbacks0
  1. Male said:
    3 May 2018 to 11: 37

    Very good Congratulations!

    Reply
  2. Junior Galvão MVP said:
    2 May 2018 to 14: 31

    Dirceu, good afternoon.

    Very cool this solution.

    Reply

Leave a Comment Cancel reply

Dirceu Resende © 2021. All Rights Reserved.