Auditer le taux d’occupation de vos disques de manière automatique

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  * * * * *

MVP Microsoft SQL Server

3 réflexions au sujet de « Auditer le taux d’occupation de vos disques de manière automatique »

  1. Avatar de zoltixzoltix

    J’ai aussi modifié le trigger pour éviter un bug présent dans 2005 SP 1

    Msg 22050, Level 16, State 1, Line 0
    Error formatting query, probably invalid parameters
    Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
    Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106

    Dont il est possible de corriger
    https://connect.microsoft.com/SQLServer/feedback/details/361954/sp-send-dbmail-fails-when-query-parameter-and-ole-automation-is-used-in-same-batch
    ou
    http://support.microsoft.com/kb/926642/en-us

    J’espère avoir le temps pour optimiser tout ça.

    A+

    alter TRIGGER [S_SYS].E_I_TSP
    ON S_SYS.T_A_TRACE_SPACE_DISK_TSP
    FOR INSERT
    AS
    BEGIN
    DECLARE @body VARCHAR(max)
    DECLARE @UNITE VARCHAR(1)
    DECLARE @TAILLE_MO DECIMAL(17,0)
    DECLARE @ESPACE_UTILISE_MO DECIMAL(17,2)
    DECLARE @OCCUPATION_POURCENT DECIMAL(17,4)
    DECLARE @TAUX_ALERTE_POURCENT int
    DECLARE @PERCENT DECIMAL(5,2)
    DECLARE @ETAT VARCHAR(20)

    SET @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é.’
    + CHAR(10)
    +’UNITE TAILLE_Gig ESPACE_UTILISE_Gig OCCUPATION_POURCENT TAUX_ALERTE_POURCENT % ETAT’
    DECLARE cur_Disk CURSOR
    FOR
    SELECT D.DSK_UNIT AS UNITE
    , TSP_SIZE_MO AS TAILLE_GIG
    , 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
    ,(CAST(TSP_USED_MO AS FLOAT) / CAST(TSP_SIZE_MO AS FLOAT) ) * 100 AS ‘%’
    , 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
    OPEN cur_Disk
    FETCH cur_Disk INTO
    @UNITE
    , @TAILLE_MO
    , @ESPACE_UTILISE_MO
    , @OCCUPATION_POURCENT
    , @TAUX_ALERTE_POURCENT
    , @PERCENT
    , @ETAT
    WHILE (@@fetch_status=0)
    BEGIN
    SET @body = @body + CHAR(10) + @UNITE
    SET @body = @body + SPACE(1) +STR(@TAILLE_MO/1024,14,3 )
    SET @body = @body + SPACE(7) +STR(@ESPACE_UTILISE_MO/1024,12,3 )
    SET @body = @body + SPACE(15)+STR(@OCCUPATION_POURCENT,5,2 )
    SET @body = @body + SPACE(18)+STR(@TAUX_ALERTE_POURCENT,3,2 )
    SET @body = @body + SPACE(1) +STR(@PERCENT,3,2 )
    SET @body = @body + SPACE(2) +@ETAT

    FETCH cur_Disk INTO
    @UNITE
    , @TAILLE_MO
    , @ESPACE_UTILISE_MO
    , @OCCUPATION_POURCENT
    , @TAUX_ALERTE_POURCENT
    , @PERCENT
    , @ETAT
    END
    CLOSE cur_Disk

    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
    )

    EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘smtp.in.Credoc.be’,
    @recipients = ‘SqlDba@credoc.be’,
    @subject = ‘Alerte automatique : quota d »espace libre de disque en dessous de la valeur fixée’,
    @body = @body,
    @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 = ‘ComLog’,
    @attach_query_result_as_file = 0,
    @query_result_header = 1;
    END
    GO

  2. Avatar de zoltixzoltix

    Merci pour ce tutoral mais je me permet de reporter quelques erreurs.
    Les primary dans les deux table n’ont pas identity ce qui provoque une erreur.
    Voici la modification que j’ai apporté a la modification des table

    CREATE TABLE [S_SYS].[T_A_DISK_DSK](
    [DSK_ID] [int] IDENTITY(1,1) NOT NULL,
    [DSK_UNIT] [char](1) NOT NULL,
    [DSK_ALERT_PC] [float] NOT NULL,
    CONSTRAINT [PK__T_A_DISK_DSK__03317E3D] PRIMARY KEY CLUSTERED
    (
    [DSK_ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT [UQ__T_A_DISK_DSK__0425A276] UNIQUE NONCLUSTERED
    (
    [DSK_UNIT] ASC
    )
    ) ON [PRIMARY]

    ET

    CREATE TABLE [S_SYS].[T_A_TRACE_SPACE_DISK_TSP](
    [TSP_ID] [int] IDENTITY(1,1) NOT NULL,
    [DSK_UNIT] [char](1) NOT NULL,
    [TSP_DATETIME] [datetime] NOT NULL,
    [TSP_SIZE_MO] [int] NOT NULL,
    [TSP_USED_MO] [int] NOT NULL,
    CONSTRAINT [PK__T_A_TRACE_SPACE___0BC6C43E] PRIMARY KEY CLUSTERED
    (
    [TSP_ID] ASC
    )
    ) ON [PRIMARY]

    Dans le SP, P_AUDIT_SPACE_DISK

    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

    INSERT INTO S_SYS.T_A_TRACE_SPACE_DISK_TSP (DSK_UNIT, TSP_SIZE_MO, TSP_USED_MO)
    SELECT HD_UNIT, HD_SIZE, HD_SIZE – HD_FREESPACE
    FROM #HD

    A la fin pour insérer dans la table T_A_DISK_DSK
    J’ai ajouté la liste des champs.
    INSERT INTO S_SYS.T_A_DISK_DSK(DSK_UNIT, DSK_ALERT_PC) VALUES (‘C’, 70);
    INSERT INTO S_SYS.T_A_DISK_DSK(DSK_UNIT, DSK_ALERT_PC) VALUES (‘D’, 60);
    INSERT INTO S_SYS.T_A_DISK_DSK(DSK_UNIT, DSK_ALERT_PC) VALUES (‘E’, 60);
    INSERT INTO S_SYS.T_A_DISK_DSK(DSK_UNIT, DSK_ALERT_PC) VALUES (‘F’, 60);

    Merci
    Bien a vous,

Laisser un commentaire