Speak 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:
| 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:
| 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:
| 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.
| 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
Very good Congratulations!
Dirceu, good afternoon.
Very cool this solution.