Hello people!
Good afternoon!
In this quick post, I will show you how to perform a simple query in the msdb.dbo.syssessions system view to identify the date and time of each instance SQL Agent startup, which is responsible for controlling and monitoring SQL Server Jobs (in addition to of other things).
In a normal environment, SQL Agent is usually started together with the instance of SQL Server. I don't remember having to stop SQL Agent manually for some process or maintenance. In the vast majority of cases, we can consider this SQL Agent startup history to be the instance's own startup history, very useful information that I know of no other way to get (unless you create an Audit routine for it).
Querying History Information
Looking at some basic statistics
|
1 2 3 4 5 6 |
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 a bit more complex statistics
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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!
To the next!
