Voici un ensemble de codes SQL utilisant des procédures systèmes et l’agent SQL pour scruter le taux d’occupation des disques et remonter une alerte en cas de dépassement.

1) création des objets dans la basez de données MSDB.

Création des tables de suivi de l’évolution de l’espace disque et de leur taux d’occupation. Notez l’utilisation du schéma S_SYS dans msdb.

USE msdb; 

GO 

 

CREATE SCHEMA S_SYS 

 

CREATE TABLE T_A_DISK_DSK 

( DSK_ID        INT      NOT NULL PRIMARY KEY, 

  DSK_UNIT      CHAR(1)  NOT NULL UNIQUE CHECK (DSK_UNIT COLLATE French_CI_AS BETWEEN 'C' AND 'Z'), 

  DSK_ALERT_PC  FLOAT    NOT NULL DEFAULT 30.0 CHECK (DSK_ALERT_PC BETWEEN 0.0 AND 100.0)) 

 

CREATE TABLE T_A_TRACE_SPACE_DISK_TSP 

( TSP_ID        INT      NOT NULL PRIMARY KEY, 

  DSK_UNIT      CHAR(1)  NOT NULL FOREIGN KEY REFERENCES T_A_DISK_DSK (DSK_UNIT), 

  TSP_DATETIME  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 

  TSP_SIZE_MO   INT      NOT NULL, 

  TSP_USED_MO   INT      NOT NULL); 

GO   

   

CREATE INDEX X_TSP_DTM ON S_SYS.T_A_TRACE_SPACE_DISK_TSP (TSP_DATETIME, DSK_UNIT); 

GO

2) création de la procédure de capture des données d’espace disque

CREATE PROCEDURE S_SYS.P_AUDIT_SPACE_DISK 

AS 

 

SET NOCOUNT ON; 

 

DECLARE @HDL int, 

        @FSO int, 

        @HD char(1), 

        @DRV int, 

        @SZ varchar(20), 

        @MB bigint ;  

 

SET @MB = 1048576; 

 

CREATE TABLE #HD (HD_UNIT      char(1) PRIMARY KEY, 

                  HD_FREESPACE int NULL, 

                  HD_SIZE      int NULL); 

 

INSERT INTO #HD (HD_UNIT, HD_FREESPACE)  

EXEC master.dbo.xp_fixeddrives; 

 

DELETE FROM #HD 

WHERE  HD_UNIT NOT IN (SELECT DSK_UNIT  

                       FROM   S_SYS.T_A_DISK_DSK); 

 

EXEC @HDL = sp_OACreate 'Scripting.FileSystemObject',@FSO OUT; 

IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO; 

 

DECLARE C CURSOR LOCAL FAST_FORWARD 

FOR SELECT HD_UNIT  

    FROM   #HD; 

 

OPEN C; 

 

FETCH NEXT FROM C INTO @HD; 

 

WHILE @@FETCH_STATUS=0 

BEGIN 

 

        EXEC @HDL = sp_OAMethod @FSO, 'GetDrive', @DRV OUT, @HD 

        IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO; 

         

        EXEC @HDL = sp_OAGetProperty @DRV, 'TotalSize', @SZ OUT 

        IF @HDL <> 0 EXEC sp_OAGetErrorInfo @DRV; 

 

        UPDATE #HD 

        SET    HD_SIZE = CAST(@SZ AS FLOAT) / @MB 

        WHERE  HD_UNIT = @HD; 

 

        FETCH NEXT FROM C INTO @HD; 

 

END 

 

CLOSE C; 

DEALLOCATE C; 

 

EXEC @HDL=sp_OADestroy @FSO; 

IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO; 

 

INSERT INTO S_SYS.T_A_TRACE_SPACE_DISK_TSP (TSP_UNIT, TSP_SIZE_MO, TSP_USED_MO) 

SELECT                                      HD_UNIT,  HD_SIZE,     HD_SIZE - HD_FREESPACE 

FROM   #HD 

 

DROP TABLE #HD; 

 

RETURN; 

GO

3) mise en place dans l’agent SQL Server serveur d’une routine journalière de scrutation à 5h du matin

USE [msdb] 

GO 

 

EXEC msdb.dbo.sp_add_job  

   @job_name=N'Scrutation espace disque',  

   @enabled=1,  

   @notify_level_eventlog=0,  

   @notify_level_email=2,  

   @notify_level_netsend=2,  

   @notify_level_page=2,  

   @delete_level=0,  

   @category_name=N'Data Collector',  

   @owner_login_name=N'SA'; 

 

EXEC msdb.dbo.sp_add_jobserver  

   @job_name=N'Scrutation espace disque',  

   @server_name = N'ServerSQL[\instance]'; 

 

EXEC msdb.dbo.sp_add_jobstep  

   @job_name=N'Scrutation espace disque',  

   @step_name=N'Rapporte l''état du volume du disque',  

   @step_id=1,  

   @cmdexec_success_code=0,  

   @on_success_action=1,  

   @on_fail_action=2,  

   @retry_attempts=0,  

   @retry_interval=0,  

   @os_run_priority=0,  

   @subsystem=N'TSQL',  

   @command=N'EXEC S_SYS.P_AUDIT_SPACE_DISK;',  

   @database_name=N'msdb',  

   @flags=0; 

 

EXEC msdb.dbo.sp_update_job  

   @job_name=N'Scrutation espace disque',  

   @enabled=1,  

   @start_step_id=1,  

   @notify_level_eventlog=0,  

   @notify_level_email=2,  

   @notify_level_netsend=2,  

   @notify_level_page=2,  

   @delete_level=0,  

   @description=N'',  

   @category_name=N'Data Collector',  

   @owner_login_name=N'ServerSQL[\instance]',  

   @notify_email_operator_name=N'',  

   @notify_netsend_operator_name=N'',  

   @notify_page_operator_name=N''; 

 

EXEC msdb.dbo.sp_add_jobschedule 

   @job_name=N'Scrutation espace disque',  

   @name=N'Planification espace disque',  

   @enabled=1,  

   @freq_type=4,  

   @freq_interval=1,  

   @freq_subday_type=1,  

   @freq_subday_interval=0,  

   @freq_relative_interval=0,  

   @freq_recurrence_factor=1,  

   @active_start_date=20090312,  

   @active_end_date=99991231,  

   @active_start_time=50000,  

   @active_end_time=235959; 

GO

Vous devez remplacer « ServerSQL[\instance] » par le nom de votre serveur et SA par le compte de connexion sous lequel cette routine doit tourner.

4) création d’un déclencheur pour envoi par mail d’une alerte immédiate

-- trigger pour envoi d'un mail 

CREATE TRIGGER E_I_TSP 

ON S_SYS.T_A_TRACE_SPACE_DISK_TSP 

FOR INSERT 

AS 

BEGIN 

   IF EXISTS(SELECT * 

             FROM   inserted AS i 

                    INNER JOIN S_SYS.T_A_DISK_DSK AS D 

                          ON i.DSK_UNIT = D.DSK_UNIT 

             WHERE  (CAST(TSP_USED_MO AS FLOAT) / CAST(TSP_SIZE_MO AS FLOAT) ) * 100 > DSK_ALERT_PC 

      master..sp_send_dbmail @profile_name = 'profile_name', 

                             @recipients = 'mail_destinataire1;mail_destinataire2;mail_destinataire3;...', 

                             @subject = 'Alerte automatique : quota d''espace libre de disque en dessous de la valeur fixée',  

                             @body = 'Au moins un des disques que vous suivez via la procédure cyclique S_SYS.P_AUDIT_SPACE_DISK à atteint un quota d''espace disque inférieur au seuil d''alerte fixé.', 

                             @body_format = 'TEXT', 

                             @importance = 'High', 

                             @query = 'SELECT D.DSK_UNIT AS UNITE, TSP_SIZE_MO AS TAILLE_MO, TSP_USED_MO AS ESPACE_UTILISE_MO,  

       (CAST(TSP_USED_MO AS FLOAT) / CAST(TSP_SIZE_MO AS FLOAT) ) * 100 AS OCCUPATION_POURCENT, 

       DSK_ALERT_PC AS TAUX_ALERTE_POURCENT, 

       CASE  

          WHEN (CAST(TSP_USED_MO AS FLOAT) / CAST(TSP_SIZE_MO AS FLOAT) ) * 100 > DSK_ALERT_PC THEN ''CRITIQUE !'' 

          ELSE ''normal''  

       END AS ETAT    

FROM   S_SYS.T_A_TRACE_SPACE_DISK_TSP AS T 

       INNER JOIN S_SYS.T_A_DISK_DSK AS D 

             ON T.DSK_UNIT = D.DSK_UNIT 

WHERE  TSP_DATETIME = (SELECT MAX(TSP_DATETIME) 

                       FROM   S_SYS.T_A_TRACE_SPACE_DISK_TSP) 

ORDER  BY 1 ', 

                             @execute_query_database =  'msdb', 

                             @attach_query_result_as_file = 0, 

                             @query_result_header = 1; 

END 

GO

Dans ce code, vous devez spécifier l’un des profil mail que vous avez mis en place lors de l’activation de db_mail à la place de « profile_name » de même vous devez spécifier les emails des destinataires à la place de « mail_destinataire1;mail_destinataire2;mail_destinataire3;… »

Exemple :

Alimentons la table des disques avec nos disques :

INSERT INTO S_SYS.T_A_DISK_DSK VALUES ('C', 70); 

INSERT INTO S_SYS.T_A_DISK_DSK VALUES ('D', 50);

Nous surveillons deux disques C et D et voulons êtres alertés lorsque C dépasse un taux d’occupation de 70% et lorsque D dépasse un taux d’occupation de 50%.

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *