Une alternative à xp_fixeddrives

Pour faire simple, le besoin est de pouvoir récupérer l’équivalent d’un df (UNIX) en Transact-SQL, c’est à dire ça:

$ df -k
Sys. de fich.        1K-blocs       Occupé     Disponible  Capacité     Monté sur
c:                    40957684     28061160    12896524    69%         /cygdrive/c
e:                   210170360     133011444   77158916    64%         /cygdrive/e
g:                    78153152     19149152    59004000    25%         /cygdrive/g
v:                    61440576     30980608    30459968    51%         /cygdrive/v

Il existe dans SQL Server une procédure stockée étendue xp_fixeddrives qui renvoie une approximation table-valuée du df, mais il y manque des informations importantes comme par exemple la taille du drive:

exec xp_fixeddrives

drive   Mo disponibles
-----   --------------
C	12594
E	75350
G	57621
V	29746

Il nous fallait donc réécrire notre propre xp_fixeddrives en le formattant comme un df (sans l’information du point de montage), sous la forme d’une fonction table valuée en SQLCLR *.

Le source de fn_fixeddrives.dll:

On récupère les propriétés des lecteurs assez simplement en utilisant la méthode GetDrives() de la classe DriveInfo, et on stocke le résultat dans un tableau, que l’on affiche ensuite ligne à ligne.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;

public partial class cFixedDrives
{
    static DriveInfo[] _t_drvlist;

    [Microsoft.SqlServer.Server.SqlFunction
        (
            FillRowMethodName = "_f_fill",
            TableDefinition = "Drive nvarchar(3), SizeMb bigint, UsedMb bigint, "+
            "AvailableMb bigint, UsePct bigint"
        )
    ]

    // Main
    public static IEnumerable fn_fixeddrives()
    {
        _t_drvlist = DriveInfo.GetDrives();
        ArrayList _t_index = new ArrayList();
        int i = 0;

        foreach (DriveInfo _drv in _t_drvlist)
        {
            if (("Fixed" == _drv.DriveType.ToString()) && (_drv.IsReady))
            {
                _t_index.Add(i);
            }
            i++;
        }

        return _t_index;
    }

    // _fill
    private static void _f_fill(Object _v_Obj, out string _v_drvName,
                                out long _v_totalsizeMb, out long _v_usedsizeMb,
                                out long _v_freesizeMb, out long _v_pctused)
    {
        int _t_row = (int)_v_Obj;

        _v_drvName = _t_drvlist[_t_row].Name;
        _v_totalsizeMb = _t_drvlist[_t_row].TotalSize / 1048576;
        _v_usedsizeMb = (_t_drvlist[_t_row].TotalSize - _t_drvlist[_t_row].TotalFreeSpace)
                          / 1048576;
        _v_freesizeMb = _t_drvlist[_t_row].TotalFreeSpace / 1048576;
        _v_pctused = ((_t_drvlist[_t_row].TotalSize - _t_drvlist[_t_row].TotalFreeSpace)
                        * 100 / _t_drvlist[_t_row].TotalSize);
    }
};

Globalement, pour qu’une fonction SQLCLR puisse retourner une table, elle doit renvoyer un type IEnumerable. L’élément important est dans le rôle de la fonction de traitement des lignes ( _f_fill() ). Cette fonction va être appelée de manière itérative par la fonction principale fn_fixeddrives() à chaque fois qu’une ligne du tableau est fetchée, un peu comme dans un curseur, et va traiter et retourner la valeur de chaque colonne de la ligne. A la fin de l’itération, on obtient une table.

La seule vague astuce consiste à stocker dans un Arraylist les positions dans le DriveInfo[] où les lecteurs sont de type ‘Fixed‘, car les propriétés de taille ou d’espace libre ne sont pas renvoyées pour les autres types de lecteurs (par exemple les lecteurs de DVD), et plutôt que de passer à _f_fill() le tableau DriveInfo[] contenant la liste des drives, on passe juste un petit tableau d’entiers contenant les positions des disques fixes, et on s’en sert comme d’un index pour affecter les colonnes en retour. On utilise un ArrayList à la place d’un int[] parce qu’on ne sait pas au départ combien de lecteurs fixes on aura sur la machine.

A noter le test && (_drv.IsReady) qui permet d’éviter de provoquer une IOException si le drive n’est pas accessible.

Déclaration de l’assembly et de la fonction dans SQL Server:

La méthode GetDrives() est considérée comme UNSAFE, en raison du type d’appels qu’elle effectue vers le kernel pour récupérer les propriétés et les tailles des disques. Pour charger l’assembly dans SQL Server, il faut activer ‘clr enabled‘ et paramétrer la base pour accepter de charger des assemblies UNSAFE:

sp_configure 'clr enabled',1
GO
reconfigure
GP
alter database clrtest set trustworthy on
GO

Enfin, créer l’assembly et la fonction:

use clrtest
GO
CREATE ASSEMBLY fixeddrives from 'E:\CAPDATA\DEV\CLR\fn_fixeddrives\bin\Debug\fn_fixeddrives.dll' WITH PERMISSION_SET = UNSAFE
GO
CREATE FUNCTION fn_fixeddrives ()
RETURNS TABLE ("Drive" nvarchar(3), "Size(Mb)" bigint, "Used(Mb)" bigint, "Available(Mb)" bigint, "Use%" bigint)
AS EXTERNAL NAME fixeddrives.cFixedDrives.fn_fixeddrives
GO

Finalement:

select * from fn_fixeddrives()
GO

Drive   Size(Mb)  Used(Mb)  Available(Mb)    Use%
----    --------  --------  --------------   -----
C:\	39997	  27403	    12593	     68
E:\	205244	  129893    75350	     63
G:\	76321	  18700	    57621	     24
V:\	60000	  30254	    29746	     50

A+ David B.

* Note: A noter qu’un précédent existe et a été publié  par Andy Novick dans mssqltips.com, mais sa fonction ne filtre pas sur les lecteurs DVD ou CD, et elle est obligée de passer par des pseudo-types SQL (sqlint64) pour tenir compte des colonnes nullable.

Continuez votre lecture sur le blog :

• Production SQL Server : Ordonnancement (Benjamin VESAN) [SQL Server]
• How-To: réduire la taille du journal de transactions sur disque (David BAFFALEUF) [SQL Server]
• How-To : réduire l’enveloppe de tempdb (David BAFFALEUF) [SQL Server]
• I/O asynchrones (épisode 1) (David BAFFALEUF) [Operating SystemSQL Server]
• Journées SQL Server 12/13 décembre (suite) (David BAFFALEUF) [SQL Server]

Tags: C#, sqlclr, xp_fixeddrives

This entry was posted on Mardi, août 17th, 2010 at 9:35 and is filed under SQL Server. You can follow any responses to this entry through the RSS 2.0 feed.