Wie zit er aan mijn SQL Agent jobs?

De HiX Datawarehouse verversing van ChipSoft haalt de data uit een tijdelijk statische HiX secondary database op basis van logshipping op. Om de database statisch te maken, zorgt de software van HiX Datawarehouse voor de correcte afhandeling van de logship procedures. Het is voor gebruikers en beheerders nadrukkelijk niet de bedoeling om zélf de logship, met name de LSRestore job, in- of uit te schakelen. Maar… monitor dat maar eens! Microsoft SQL Server heeft daar zélf geen voorziening voor én het goede nieuws is dat deze voorziening eenvoudig zélf te schrijven is.

Functioneel
Wanneer een SQL Agent job in- of uitgeschakeld wordt, wordt dit in de tabel [dbo].[sysjobs] binnen de msdb database opgeslagen. Deze wijziging kunnen we afvangen en de wijziging kan in een aparte beheertabel worden vastgelegd.

Technisch
Met een AFTER UPDATE trigger op de tabel dbo.sysjobs binnen de msdb database wordt het mogelijk om de wijziging in de job status (ingeschakeld / uitgeschakeld) af te vangen. De gewijzigde informatie wordt vervolgens in een beheerdatabase / beheertabel opgeslagen, zodat hier later naar gekeken kan worden.

De code hiervoor …

USE msdb;
GO
CREATE TRIGGER [dbo].Alert_on_job_stat
   ON  [dbo].[sysjobs]
   AFTER UPDATE
AS 
BEGIN
DECLARE @old_status BIT, @new_status BIT, @job_name VARCHAR(1024)
 
SELECT @old_status = enabled FROM deleted
SELECT @new_status = enabled, @job_name = name FROM inserted
 
  IF(@old_status <> @new_status)
  BEGIN
    INSERT INTO [audit_database].dbo.[audit_tabel] (login_name, job_name, status_, datetime)
    VALUES(ORIGINAL_LOGIN(), @job_name, @new_status, GETDATE())
    
    DECLARE @body_content VARCHAR(1024) = '';
    SET @body_content = 'SQL job : ' + @job_name + ' has been ' + 
              CASE @new_status WHEN 1 THEN 'Enabled' ELSE 'Disabled' END 
              + ' @ ' + CAST(GETDATE() AS VARCHAR(30))

    /* Eventueel een e-mail sturen
    EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQL Alert', 
    @recipients = 'beheerders@domeinnaam.com',  
    @subject = 'SQL job status Change Alert',  
    @body = @body_content
    */
    ;  
  END
END

Aandachtspunten:

  1. De database audit_database is een zelfgekozen naam en dient uiteraard te worden aangemaakt
  2. De tabelnaam is een zelfgekozen naam en dient uiteraard te worden aangemaakt
  3. E-mail is standaard door mij uitgeschakeld
  4. De e-mail instellingen moeten goed worden geconfigureerd
  5. Gebruikers die SQL Agent Jobs in en uit kunnen schakelen, dienen voldoende schrijfrechten op de audit database en audit tabel te krijgen

Met dank aan Jignesh Raiyani voor het idee en de uitwerking in het Engels; Link naar de webpagina