Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.448 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 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') November 5th, 2020
  • August 23st, 2015
  • 0
  • Database DMVs and Catalog Views Maintenance SQL Server SQL Server Agent

How to query SQL Agent startup history in SQL Server

Views: 494
Reading Time: 2 minutes

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
SQL Agent History - syssessions

Looking at some basic statistics

Transact-SQL
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

SQL Agent History - 2 syssessions

Querying a bit more complex statistics

Transact-SQL
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

SQL Agent History - 3 syssessions

That's it folks!
To the next!

tags: boot historymsdb.dbo.syssessionsquerysqlsql agentsql agent historysql serversyssessions

You may also like ...

  • SQL Server - Introduction to Performance Tuning Study

  • Running a command on all instance databases in SQL Server

  • How to register and create server groups in SQL Server Managment Studio (And set the color of each server)

  • Next The undocumented SQL Server extended procedures
  • Previous How to calculate shipping amount and lead time using Post Office WebService in SQL Server

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.