How to Query SQL Agent Startup History in SQL Server

Hey guys!
Good afternoon!

In this quick post, I will show you how to run a simple query in the msdb.dbo.syssessions system view to identify the date and time of each instance SQL Agent startup, responsible for controlling and monitoring SQL Server Jobs (as well as other things).

In a normal environment, SQL Agent is usually started together with the SQL Server instance. I don't remember having to stop SQL Agent manually for any process or maintenance. In the vast majority of cases, we can consider this SQL Agent startup history as the startup history of the instance itself, very useful information that I don't know of any other way to obtain (unless you create an Audit routine for this).

Querying history information

Checking some basic statistics

SELECT 
    MIN(agent_start_date) AS Dt_Primeira_Inicializacao, 
    COUNT(*) AS Qt_Inicializacoes,
    MAX(agent_start_date) AS Dt_Ultima_Inicializacao 
FROM 
    msdb.dbo.syssessions

Querying slightly more complex statistics

IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados
SELECT 
    A.agent_start_date,
    DATEDIFF(DAY, B.agent_start_date, A.agent_start_date) AS Qt_Diferenca,
    DAY(A.agent_start_date) AS Dia,
    DATEPART(HOUR, A.agent_start_date) AS Hora,
    DATENAME(WEEKDAY, A.agent_start_date) AS Dia_Semana
INTO
    #Dados
FROM 
    msdb.dbo.syssessions		A
    JOIN msdb.dbo.syssessions	B	ON	A.session_id = B.session_id + 1


IF (OBJECT_ID('tempdb..#Dia_Mais_Inicializado') IS NOT NULL) DROP TABLE #Dia_Mais_Inicializado
SELECT Dia, COUNT(*) AS Quantidade
INTO #Dia_Mais_Inicializado
FROM #Dados 
GROUP BY Dia

IF (OBJECT_ID('tempdb..#Hora_Mais_Inicializada') IS NOT NULL) DROP TABLE #Hora_Mais_Inicializada
SELECT Hora, COUNT(*) AS Quantidade
INTO #Hora_Mais_Inicializada
FROM #Dados 
GROUP BY Hora

IF (OBJECT_ID('tempdb..#Dia_Semana_Mais_Inicializado') IS NOT NULL) DROP TABLE #Dia_Semana_Mais_Inicializado
SELECT Dia_Semana, COUNT(*) AS Quantidade
INTO #Dia_Semana_Mais_Inicializado
FROM #Dados 
GROUP BY Dia_Semana

DECLARE @Qt_Media_Dias_Entre_Inicializacoes INT = (SELECT AVG(Qt_Diferenca) FROM #Dados)


SELECT 
    @Qt_Media_Dias_Entre_Inicializacoes AS Qt_Media_Dias_Entre_Inicializacoes,
    (SELECT TOP 1 Dia FROM #Dia_Mais_Inicializado ORDER BY Quantidade DESC) AS Qt_Dia_Com_Mais_Inicializacoes,
    (SELECT TOP 1 Hora FROM #Hora_Mais_Inicializada ORDER BY Quantidade DESC) AS Qt_Hora_Com_Mais_Inicializacoes,
    (SELECT TOP 1 Dia_Semana FROM #Dia_Semana_Mais_Inicializado ORDER BY Quantidade DESC) AS Qt_Dia_Semana_Com_Mais_Inicializacoes

That's it, folks!
Until next time!