Skip to content

Dirceu Resende

DBA SQL Server e Analista de BI (PowerBI, SSAS, SSIS, SSRS)

  • Consultoria
    • Consultoria de BI
    • Consultoria de Power BI
    • Consultoria SQL Server
  • Arquivo
  • Séries
    • Certificação
    • Segurança e Auditoria
    • Performance Tuning
    • O que mudou no T-SQL ?
    • Proteção de Dados
  • Vagas de Emprego
  • Eventos Data Platform
  • Sobre
  • Contato

Other Languages

Assinar blog por e-mail

Digite seu endereço de e-mail para assinar este blog e receber notificações de novas publicações por e-mail.

Junte-se a 536 outros assinantes

Visualizações do Blog

1.645.436 views

Categorias

  • Apache / .htaccess (9)
  • Banco de Dados (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Auditoria (15)
      • Azure (2)
      • CLR (53)
      • Desenvolvimento de Query (83)
      • DMVs e Views de Catálogo (31)
      • Erros (22)
      • Ferramentas (12)
      • Formatação e Validação de Dados (23)
      • Funcionalidades pouco conhecidas (19)
      • Hacks (17)
      • Integrações (30)
      • Manipulação de Arquivos (13)
      • Manutenção (80)
      • Monitoramento (35)
      • O que não fazer (7)
      • OLE Automation (19)
      • Performance Tuning (22)
      • Python (1)
      • Segurança (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Carreira e Cursos (13)
  • Carreira, Cursos e Certificações (28)
  • Celulares (1)
  • Eventos e Palestras (63)
  • Programação (57)
    • C# (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • Javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Sem categoria (10)
  • SEO (4)
  • Virtualização (5)

Microsoft MVP Data Platform

Minhas Certificações

Treinamentos

Arquivo de Posts

Posts recentes

  • Descontos da “Black Friday” nos Treinamentos de SQL Server (Comprem meu curso kkkkk) 27 de novembro de 2020
  • SQL Server – As “novas” funções GREATEST e LEAST 27 de novembro de 2020
  • SQL Server – Como saber a data do último login de um usuário 9 de novembro de 2020
  • Azure na Prática Gratuito #07 – Administrando Banco de Dados no Azure 5 de novembro de 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’) 5 de novembro de 2020
  • 2 de maio de 2018
  • 2
  • Banco de Dados DMVs e Views de Catálogo Erros Funcionalidades pouco conhecidas Hacks Manutenção SQL Server SQL Server Agent

SQL Server – Como visualizar toda a mensagem de retorno da execução do Job (mesmo quando ela ultrapassa os 4000 caracteres)

Visualizações: 1.284
Tempo de Leitura: 4 minutos

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:

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'

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:

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

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:

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

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.

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'

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

Tags: errossqlsql agentsql server

You may also like...

  • Como identificar a porta utilizada pela instância do SQL Server

  • Prova de certificação Microsoft MCSE 70-774 (Perform Cloud Data Science with Azure Machine Learning) de graça (beta) até 31/03/2017

  • Webcast – Comemoração 300 posts + Novidades do SQL Server 2019 (04/10/2018 – 21h)

  • Next Como foi o 7º encontro do PASS Local Group SQL Server ES ?
  • Previous SQL Server – Material de estudo para a prova de certificação 70-764 Administering a SQL Database

2 Responses

  • Comments2
  • Pingbacks0
  1. Masculino disse:
    3 de maio de 2018 às 11:37

    Muito bom, parabens!

    Responder
  2. Junior Galvão MVP disse:
    2 de maio de 2018 às 14:31

    Dirceu, boa tarde.

    Muito legal esta solução.

    Responder

Deixe uma resposta Cancelar resposta

Dirceu Resende © 2020. All Rights Reserved.