Fala pessoal!
Tudo ótimo com vocês ?
Neste post, eu gostaria de compartilhar com vocês como visualizar toda a mensagem de retorno da execução do Job quando a saída do job possui mais de 4.000 caracteres.
Entendendo o cenário e o problema
Se analisarmos a estrutura da tabela msdb.dbo.sysjobhistory, que é onde as mensagens de log dos jobs são gravadas, podemos observar que seu tipo é nvarchar(8000) (e era varchar(1024) até a versão 2008) e que por conta do overhead dos caracteres utilizando UTF-8, suporta 4.000 caracteres apenas:
Ou seja, quando a mensagem do job ultrapassa os 4000 caracteres, ela aparece cortada (truncada) quando você tenta visualizar o histórico de execução do job. Para quem já se deparou com essa situação, sabe como ela é frustrante de você visualizar que o job falhou, mas não consegue ver a mensagem de erro que originou o erro.
Isso faz com que, mesmo realizando consultas direto nas tabelas do SQL Agent, não seja possível retornar toda a mensagem, já que essa limitação é na própria estrutura da tabela:
| 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' |
Resultado da consulta:
Exemplo desse cenário:
Simulando esse cenário em seu ambiente
Para simular esse cenário, criei um job, com apenas 1 step, que executava esse comando:
| 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 |
A solução para o problema deste post
Pois bem, agora que já expliquei como e porque esse problema ocorre, e demonstrei como você pode simular esse cenário no seu ambiente (caso você não esteja enfrentando esse problema agora.. rs), vou mostrar como resolver esse problema.
Primeiramente, vamos ativar a opção de logar o resultado do step em uma tabela:
A partir de agora, as mensagens desse step serão gravadas no banco de dados (msdb.dbo.sysjobstepslogs).
Observação 1: Vale lembrar que esse passo deve ser feito para cada step que você deseja ativar o recurso.
Observação 2: Apenas a última execução fica registrada nesse log. Caso você queira armazenar todo o histórico das mensagens, você deve marcar o checkbox “Append output to existing entry in table”.
Para visualizar a mensagem do log, vou demonstrar algumas alternativas para fazer isso:
Alternativa #1: Utilizando a interface do SSMS
Para visualizar a mensagem completa pela interface do SSMS, basta clicar no botão “View” na tela dos detalhes do Step:
Com isso, uma instância do bloco de notas será aberta com o resultado da execução do job:
Alternativa #2: Utilizando a SP de sistema sp_help_jobsteplog
Uma outra forma de se obter a mensagem completa do resultado da execução do job em questão, é utilizando a SP de sistema sp_help_jobsteplog:
| 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 |
Resultado:
Alternativa #3: Utilizando tabelas do SQL Agent
Você também pode consultar diretamente as tabelas do SQL Agent no database msdb para recuperar a informação do retorno do job.
| 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' |
Resultado:
Bom pessoal, é isso aí!
Você conhece outras formas de conseguir essa informação? Poste aqui nos comentários que irei atualizar o seu post e referenciá-lo.
Espero que tenham gostado desse post e que lhes seja útil 🙂
Um abraço e até o próximo post.
sql server sql agent log return message truncate truncated limit limited size 4000 characters caracteres
sql server sql agent log return message truncate truncated limit limited size 4000 characters caracteres
Muito bom, parabens!
Dirceu, boa tarde.
Muito legal esta solução.