SQL Server: Como definir a hora em um formato legível por humanos

O roteiro é terrível e a fonte... não tinha muita autoridade. :/

Para ser justo com sua pergunta, monitorar trabalhos do SQL Server Agent não é tão simples quanto se poderia pensar, embora o tempo legível por humanos seja suspeito do autor das consultas.

• As tabelas de trabalho do SQL Server Agent

• [dbo].[sysjobactivity]

  Registra a atividade e o status atual do trabalho do SQL Server Agent.

  dbo.sysjobactivity | Documentos da Microsoft

• [dbo].[sysjobhistory]

  Contém informações sobre a execução de trabalhos agendados pelo SQL Server Agent.

  NOTA: Os dados são atualizados somente após a conclusão da etapa de trabalho

  dbo.sysjobhistory | Documentos da Microsoft

• [dbo].[sysjobs]

  Armazena as informações de cada trabalho agendado a ser executado pelo SQL Server Agent.

  dbo.sysjobs | Documentos da Microsoft

• [dbo].[sysjobschedules]

  Contém informações de agendamento para trabalhos a serem executados pelo SQL Server Agent.

  NOTA: A tabela sysjobschedules é atualizada a cada 20 minutos, o que pode afetar os valores retornados pelo procedimento armazenado sp_help_jobschedule.

  dbo.sysjobschedules | Documentos da Microsoft

• [dbo].[sysjobservers]

  Armazena a associação ou relacionamento de um determinado trabalho com um ou mais servidores de destino

  dbo.sysjobservers | Documentos da Microsoft

• [dbo].[sysjobsteps]

  Contém as informações de cada etapa de um trabalho a ser executado pelo SQL Server Agent

  dbo.sysjobsteps | Documentos da Microsoft

• [dbo].[sysjobstepslogs]

  Contém o log de etapas de trabalho para todas as etapas de trabalho do SQL Server Agent configuradas para gravar a saída da etapa de trabalho em uma tabela.

  dbo.sysjobstepslogs | Documentos da Microsoft

• Como encontrar trabalhos com falha

Como o objetivo é a saúde do servidor, tente focar em um dos trabalhos que estão realmente falhando. Concentrar-se nas médias é bom, mas não é muito útil, a menos que você seja o proprietário do aplicativo e precise conhecer esses detalhes minuciosos. Mas mesmo assim, desafie o pedido para descobrir o porquê.

Me deparei com um excelente script que me permite encontrar todos os trabalhos com falha, a etapa em que falhou e todos os logs/mensagens relatados em nossos mais de 200 servidores .

USE msdb
GO
/* Create #Temp table to house job events */
IF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE NAme LIKE '#FailedJobs%')
    DROP TABLE #FailedJobs
CREATE TABLE #FailedJobs (Job VARCHAR(250)
                        , StepFailed VARCHAR(100)
                        , DateRun VARCHAR(30)
                        , TimeRun VARCHAR(30)
                        , Step_name VARCHAR(100)
                        , run_Duration INT
                        , LogOutput VARCHAR(MAX) )

DECLARE @JobName VARCHAR(250)
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE JobsCursor CURSOR FORWARD_ONLY
FOR SELECT Name 
    FROM sysjobs A 
    INNER JOIN dbo.sysjobschedules B ON A.job_id = B.job_id
    WHERE A.enabled = 1
        AND B.next_run_date > 0

    OPEN JobsCursor
    FETCH NEXT FROM JobsCursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @job_id = job_id FROM dbo.sysjobs WHERE [name] = @JobName        
    INSERT INTO #FailedJobs (Job, StepFailed, DateRun, TimeRun, Step_name, run_Duration, LogOutput )

    SELECT @JobName AS Job
        , 'Step ' + CAST(JH.step_id AS VARCHAR(3)) 
            + ' of ' + (    SELECT CAST(COUNT(*) AS VARCHAR(5)) 
                        FROM dbo.sysjobsteps 
                        WHERE job_id = @job_id
                        ) AS StepFailed
        , CAST(RIGHT(JH.run_date,2) AS CHAR(2)) 
            + '/' + CAST(SUBSTRING(CAST(JH.run_date AS CHAR(8)),5,2) AS CHAR(2)) 
            + '/' + CAST(LEFT(JH.run_date,4) AS CHAR(4)) AS DateRun
        , LEFT(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),2) 
            + ':' + SUBSTRING(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),3,2) 
            + ':' + LEFT(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),2) AS TimeRun
        , JS.step_name 
        , JH.run_duration 
        , CASE WHEN JSL.[log] IS NULL THEN JH.[Message]
               ELSE JSL.[log]
          END AS LogOutput
    FROM dbo.sysjobsteps JS 
    INNER JOIN dbo.sysjobhistory JH ON JS.job_id = JH.job_id 
                                   AND JS.step_id = JH.step_id 
    LEFT OUTER JOIN dbo.sysjobstepslogs JSL ON JS.step_uid = JSL.step_uid
    WHERE INSTANCE_ID > (SELECT MIN(INSTANCE_ID)
                         FROM (
                                SELECT top (2) INSTANCE_ID, job_id
                                FROM dbo.sysjobhistory
                                WHERE job_id = @job_id
                                AND STEP_ID = 0
                                ORDER BY INSTANCE_ID desc
                                ) A
                        ) 
        AND JS.step_id <> 0 
        AND JH.job_id = @job_id
        /*Status of the job execution:
            0 = Failed
            1 = Succeeded
            2 = Retry
            3 = Canceled
        https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql
        */
        AND JH.run_status = 0
    ORDER BY JS.step_id ASC

    FETCH NEXT FROM JobsCursor INTO @JobName
END
CLOSE JobsCursor
DEALLOCATE JobsCursor

SELECT * 
FROM #FailedJobs

O formato retornado é muito perspicaz:

Observe como essa saída é legível. Você pode não apenas ver qual tarefa está falhando, mas também o step_id em que ela falhou! O LogOutput retornará o erro, tudo isso acionável .

Como você provavelmente desejará isso mais tarde, adicionei duas outras consultas que são úteis:

1. Encontre o local do log de erros - especialmente quando as consultas acima são truncadas

USE master
GO
EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, NULL, N'asc' 

https://www.mssqltips.com/sqlservertip/2506/identify-location-of-the-sql-server-error-log-file/

2. Analise o trabalho de Program_Name em sys.dm_exec_requests e sys.dm_exec_sessions

Sempre quis ver as sessões em que um trabalho está sendo executado e suas solicitações? bem, agora você pode verificar isso facilmente através de alguns scripts

- Pré-Solução

SELECT convert(varbinary(32), substring([Program_name], 30, 34), 1) AS Job_ID
     , sjob.name
     , s.*
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
INNER JOIN msdb.dbo.sysjobs sjob 
    ON convert(varbinary(32), substring([Program_name], 30, 34), 1) = sjob.job_id
WHERE program_name like 'SQLAgent - TSQL JobStep %';

Apenas analise a coluna Program_Name para recuperar o Job_ID e certifique-se de convertê-lo para o formulário VARBINARY. Observe também que você precisa especificar o tipo de conversão para 1!

- Pós-Solução

Espero que isso o leve muito mais à frente e se concentre em informações acionáveis .

Demorou 1 ano, mas eu tenho.

Segui este Guia da Microsoft e consegui o que queria: agora posso filtrar o trabalho por data , hora e até duração .

SELECT
sJobHis.[server],
sJobStep.database_name,
SJob.name,
SJob.enabled,
--CASE WHEN SJob.enabled = 0 THEN '0'
--WHEN sJobStep.subsystem = 'TSQL' AND sJobStep.command LIKE '%--%' 
--AND AVG(CAST(SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,7,2) AS INT)) < 1 THEN '0'
--WHEN sJobStep.subsystem = 'TSQL' AND sJobStep.command LIKE '%*/%'
--AND AVG(CAST(SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,7,2) AS INT)) < 1 THEN '0' ELSE '1' END AS  [ActiveStep],
sLogin.name [JobOwner],
sJobHis.step_id,
sJobHis.step_name,
sJobStep.subsystem AS [CommandType],
sJobStep.command AS [Command],
sJobHis.run_date,
--[sJobSch].next_run_time AS [Scheduled_Time],
STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST([sJobSch].next_run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'Scheduled_Time',
--sJobHis.run_time,
--sJobHis.run_duration,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sJobHis.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sJobHis.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)',
--AVG( CAST( SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,4,2)AS INT)) AS [AvgRunDuration_In_Min], 
--AVG(CAST(SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,7,2) AS INT)) AS [AvgRunDuration_In_Sec],
COUNT(*) AS [PerDay]

FROM msdb.dbo.sysjobs AS [SJob] 
LEFT JOIN msdb.dbo.sysjobhistory AS [sJobHis] ON SJob.job_id = sJobHis.job_id
LEFT JOIN msdb.dbo.sysjobsteps AS [sJobStep] ON sJobHis.job_id = sJobStep.job_id AND sJobHis.step_id = sJobStep.step_id
INNER JOIN master.dbo.syslogins [sLogin] ON SJob.owner_sid = sLogin.sid
INNER JOIN msdb.dbo.sysjobschedules [sJobSch] ON SJob.job_id = [sJobSch].job_id
WHERE  SJob.[enabled]=0 OR ( sJobHis.step_id > 0  AND (sJobHis.run_date > 20180825 AND sJobHis.run_date < 20180829)) -- PUT HERE YOUR DATES
GROUP BY sJobHis.[server],
sJobStep.database_name,
SJob.name,
SJob.enabled,
sLogin.name ,
sJobHis.step_id,
sJobHis.step_name,
sJobStep.subsystem ,
sJobStep.command ,
sJobHis.run_date,
sJobHis.run_time,
sJobHis.run_duration,
[sJobSch].next_run_time
ORDER BY SJob.enabled DESC, SJob.name, sJobHis.run_date DESC

Eu mantive as fileiras antigas para que você possa ver a mudança que fiz.