Datafly.pro

Nous allons explorer ici les quelques méthodes qui nous permettent d'obtenir facilement ces informations sans sortir des outils SQL Server : requêtes TSQL et vues de management dynamiques bien sûr.

Curieusement, SQL Server Management Studio, pourtant bien pourvu en rapports divers, n'en fournit aucun pour surveiller l'espace disque ! Pour les fans des rapports SSMS, vous trouverez en bas de l'article le lien pour télécharger sur ce site un rapport tout prêt, et les explications pour l'utiliser :

Mais passons aux choses sérieuses : comment faire en TSQL ?

A partir de SQL 2008 R2 SP1

Si vous avez SQL Server 2008 R2 SP1 ou ultérieur, vous avez de la chance : la dmv sys.dm_os_volume_stats est apparue avec cette version et nous retourne les informations sur les volumes où sont stockés nos fichiers de bases de données.

La syntaxe est : SELECT * from sys.dm_os_volume_stats(@Database_ID, @File_ID)

• @Database_ID est l'identifiant de la base de données, que vous pouvez obtenir avec la fonction DB_ID()
• @File_ID est le numéro du fichier, que vous pouvez obtenir en interrogeant la vue sys.database_files

Vous pouvez donc obtenir par exemple les informations sur le volume disque où se trouve le 1er fichier de la base tempdb :

SELECT * FROM sys.dm_os_volume_stats(DB_ID('tempdb'), 1) 

La vue dm_os_volume_stats vous renvoie les colonnes suivantes :

• database_id
• file_id
• volume_mount_point
• volume_id
• logical_volume_name
• file_system_type
• total_bytes
• available_bytes
• supports_compression
• supports_alternate_streams
• supports_sparse_files
• is_read_only
• is_compressed

Grâce à la fonction CROSS APPLY, rien de plus facile que d'obtenir un jeu d'informations pour plusieurs fichiers :

Tous les fichiers de tempdb :

SELECT f.name,v.* FROM tempdb.sys.database_files f 
CROSS APPLY sys.dm_os_volume_stats(DB_ID('tempdb'), file_id) AS v

Tous les fichiers de base de données de l'instance :

SELECT name, v.* FROM sys.master_files 
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id) AS v

Mieux encore, le script suivant vous offre des informations synthétiques par volume disque et tient compte de la croissance potentielle des fichiers :

/*-------------------------------------------------------------------
[SCRIPT]  Disk space 
[DATABASE] Toutes
[DESCRIPTION] Pour SQL2008R2 SP1 et ultérieur
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 05-2013 
-------------------------------------------------------------------*/
SELECT volume_mount_point AS Volume
     , CAST(MIN(VolumeTotalMo) AS DECIMAL(18, 2)) AS VolumeSpaceMo
     , CAST(SUM(FileSizeMo) AS DECIMAL(18, 2)) AS DBFilesSpaceMo
     , CAST(MIN(VolumeTotalMo)-SUM(FileSizeMo)-MIN(VolumeAvailableMo) AS DECIMAL(18,2)) AS OtherMo
     , CAST(MIN(VolumeAvailableMo) AS DECIMAL(18, 2)) AS VolumeFreeSpaceMo
     , CAST(SUM(FileGrowthMo) AS DECIMAL(18, 2)) AS DBGrowthSpaceMo
     , CAST(MIN(VolumeAvailableMo) - SUM(FileGrowthMo) AS DECIMAL(18, 2)) AS FreeSpaceAfterGrowthMo
     , CAST(MIN(VolumeAvailableMo) / MIN(VolumeTotalMo) * 100 AS DECIMAL(6, 2)) AS [Pct_VolumeFree]
     , CAST(( MIN(VolumeAvailableMo) - SUM(FileGrowthMo) )
       / MIN(VolumeTotalMo) * 100 AS DECIMAL(6, 2)) AS [Pct_FreeAfterGrowth]
FROM       ( SELECT    DB_NAME(f.database_id) AS [DatabaseName]
               , f.file_id
               , CAST(f.size AS FLOAT) * 8 / 1024 AS FileSizeMo
               , CASE    WHEN f.is_percent_growth = 1
                    THEN ( CAST(f.size AS FLOAT) * f.growth / 100 ) * 8
                        / 1024
                    ELSE CAST(f.growth AS FLOAT) * 8 / 1024
                END AS FileGrowthMo
               , vs.volume_mount_point
               , CAST(vs.total_bytes AS FLOAT) / 1024 / 1024 AS VolumeTotalMo
               , CAST(vs.available_bytes AS FLOAT) / 1024 / 1024 AS VolumeAvailableMo
               , ( CAST(vs.available_bytes AS FLOAT)
                  / CAST(vs.total_bytes AS FLOAT) ) * 100 AS [Space Free %]
        FROM        sys.master_files AS f
                CROSS APPLY sys.dm_os_volume_stats(f.database_id,
                                            f.file_id) AS vs
       ) AS FileSizes
GROUP BY volume_mount_point
ORDER BY volume_mount_point

Avant SQL Server 2008 R2 SP1

Vous êtes moins chanceux : il n'existe aucune dmv qui vous offre ces informations. Une procédure stockée xp_fixeddrives existe, mails elle ne vous fournit que l'espace disponible sur tous les disques du serveur. Une solution envisageable est d'utiliser OLE Automation pour aller lire la taille des fichiers (vous devrez activer cette fonctionnalité dans la facette "configuration de la surface d'exposition" de votre instance). Je vous propose le script suivant, inspiré de nombreux exemples sur internet, le petit ajout concerne la croissance potentielle des fichiers :

/*-------------------------------------------------------------------
[SCRIPT]  Disk space 
[DATABASE] Toutes
[DESCRIPTION] Avec OLE Automation
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 2013 
-------------------------------------------------------------------*/
DECLARE    @hr INT 
DECLARE    @fso INT 
DECLARE    @drive CHAR(1) 
DECLARE    @odrive INT 
DECLARE    @TotalSize VARCHAR(20) 
DECLARE    @MB BIGINT;
SET @MB = 1048576 
CREATE TABLE #drives
    (
     drive CHAR(1) PRIMARY KEY
    ,FreeSpace INT NULL
    ,TotalSize INT NULL
    ) 
INSERT    #drives
        ( drive, FreeSpace )
        EXEC master.dbo.xp_fixeddrives 
EXEC @hr= sp_OACreate 'Scripting.FileSystemObject', @fso OUT 
IF @hr <> 0 
    EXEC sp_OAGetErrorInfo @fso 

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR
    SELECT    drive
    FROM    #drives
    ORDER BY drive 
OPEN dcur 
FETCH NEXT FROM dcur INTO @drive 
WHILE @@FETCH_STATUS = 0 
    BEGIN 
        EXEC @hr = sp_OAMethod @fso, 'GetDrive', @odrive OUT, @drive 
        IF @hr <> 0 
            EXEC sp_OAGetErrorInfo @fso 
        EXEC @hr = sp_OAGetProperty @odrive, 'TotalSize', @TotalSize OUT 
        IF @hr <> 0 
            EXEC sp_OAGetErrorInfo @odrive 
        UPDATE    #drives
        SET        TotalSize = @TotalSize / @MB
        WHERE    drive = @drive 
        FETCH NEXT FROM dcur INTO @drive 
    END 
CLOSE dcur 
DEALLOCATE dcur 

EXEC @hr= sp_OADestroy @fso 
IF @hr <> 0 
    EXEC sp_OAGetErrorInfo @fso 

SELECT    #Drives.drive
       ,FreeSpace AS 'FreeMB'
       ,TotalSize AS 'TotalMB'
       ,CAST(( FreeSpace / ( TotalSize * 1.0 ) ) * 100.0 AS INT) AS 'Free_pct'
       ,DbSizeMo
       ,DbGrowthMo
       ,FreeSpace - DbGrowthMo AS LeftAfterGrowthMo
FROM    #drives
        LEFT JOIN ( SELECT    LEFT(physical_name, 1) AS drive
                           ,SUM(size) * 8 / 1024 AS DbSizeMo
                           ,SUM(CASE WHEN is_percent_growth = 1
                                     THEN ( size * growth / 100 ) * 8 / 1024
                                     WHEN is_percent_growth = 0
                                     THEN growth * 8 / 1024
                                END) AS DbGrowthMo
                    FROM    sys.master_files
                    GROUP BY LEFT(physical_name, 1)
                  ) AS DbFiles ON #drives.drive = DbFiles.drive
WHERE DBSizeMo IS NOT NULL
ORDER BY #drives.drive 
DROP TABLE #drives 

Et l'interface graphique ?

Comme promis, voici un rapport à utiliser dans SQL Server Management Studio. Mais dommage pour les autres, il sera réservé aux heureux possesseurs de SSMS 2012 ou ultérieur. Comme il s'appuie lui aussi sur la vue dm_os_volume_stats, il ne pourra fonctionner qu'avec des instances SQL 2008R2/SP1 et ultérieures... Mais rien ne vous empêche de faire le vôtre !

Pour chaque disque où se situent des fichiers de bases de données, vous trouverez :

Un graphique

Un indicateur :

• vert si il restera plus de 10% d'espace libre après une croissance de tous les fichiers, 
• orange sinon mais s'il reste tout de même plus de 10% d'espace libre sur disque actuellement,
• Rouge s'il reste moins de 10% d'espace libre sur disque

Un tableau de détail, les chiffres (sauf les pourcentages) sont en Mo :

Deux jauges pour évaluer l'espace libre sur disque actuel, et l'espace libre si tous les fichiers grossissent de leur incrément.

Pour l'installer : (update décembre 2013)

• Téléchargez le fichier "SSMS_Disk_Space.rdl" sur http://ssmsdiskspace.codeplex.com et copiez le sur votre disque local. Le répertoire Documents\SQL Server Management Studio\Custom Reports déjà présent dans votre profil est le meilleur endroit.
• Dans SSMS, cliquez avec le bouton droit sur votre instance dans l'Explorateur d'objets, pointez sur Rapports et cliquez avec le bouton gauche sur Rapports personnalisés. Dans la boîte de dialogue Ouvrir le fichier, choisissez le fichier "SSMS_Disk_Space.rdl" que vous avez copié. Si un message d'avertissement s'affiche, validez.
• Lors des utilisations suivantes, vous retrouverez ce rapport dans la liste des rapports récemment utilisés.

Bon monitoring...!