Vous êtes ici :
Le problème des alertes de l’agent SQL Server est qu’aucun historique n’est conservé : seule le nombre d’occurences et la date de dernière occurence sont disponibles.
La solution, utilisée d’ailleurs par certains logiciels de monitoring, consiste déclencher un job qui va journaliser l’évènement d’alerte dans une table (en utilisant les tokens de l’alerte).
Les scripts suivants mettent en place cette solution :
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 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
/*------------------------------------------------------------------- [SCRIPT] Alert trap [DATABASE] master [DESCRIPTION] Log SQL Agent alerts events in a table [MAJ PAR] DATAFLY - Arian Papillon -------------------------------------------------------------------*/ USE [msdb] GO -- configure les tokens EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1 GO -- crée la table de log CREATE TABLE AlertHistory ( LogTimeUTC DATETIME2 DEFAULT GETUTCDATE(), -- Generated DatabaseName sysname NULL, -- A-DBN ServerName sysname NULL, -- A-SVR ErrorNumber int NULL, -- A-ERR Severity int NULL, -- A-SEV MessageText VARCHAR(MAX) NULL, -- A-MSG ) -- Script du job USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 -- catégorie : modifier si nécessaire IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END -- job DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AlertTrap', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Job Step [Log Alert] EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log Alert', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'INSERT INTO [dbo].[AlertHistory] ([DatabaseName] ,[ServerName] ,[ErrorNumber] ,[Severity] ,[MessageText] ) VALUES (''$(ESCAPE_SQUOTE(A-DBN))'' ,''$(ESCAPE_SQUOTE(A-SVR))'' ,CAST(''$(ESCAPE_SQUOTE(A-ERR))'' AS INT) ,CAST(''$(ESCAPE_SQUOTE(A-SEV))'' AS INT) ,''$(ESCAPE_SQUOTE(A-MSG))'' ) GO', @database_name=N'msdb', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO |
Pour configurer les alertes déjà existantes et leur faire déclencher le job, vous pouvez utiliser le script suivant (notez que cela écrase la configuration des alertes qui déclencheraient déjà un job) :
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Configurer alertes existantes USE [msdb]; GO DECLARE @sql VARCHAR(MAX) = ''; DECLARE @jobid VARCHAR(50) SELECT @jobid = job_id FROM sysjobs WHERE name = 'AlertTrap' SELECT @sql = @sql + 'EXEC msdb.dbo.sp_update_alert @name=N''' + name + ''',@job_id=N''' +@jobid+ '''' + CHAR(10) FROM sysalerts; PRINT @sql EXEC( @sql ) |
Table of Contents