La surveillance de l'espace disque pour le DBA
Par Arian Papillon le mardi 28 mai 2013, 09:15 - Lien permanent
Une des tâches essentielles du DBA est de surveiller l'espace disque disponible pour les bases de données sous peine d'arriver un jour à saturation.
Voici quelques requêtes utiles avec en prime un rapport tout prêt pour utiliser dans Management Studio...!
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...!
Commentaires
Merci Arian pour cette exploration des DMV, pour ma part je viens de découvrir MDW (Management Data Warehouse) qui est disponible à partir de la version SQL 2008. Voici le lien microsoft : [http://technet.microsoft.com/en-us/library/dd939169(v=sql.100).aspx].
Cela permet de recueillir cpu, mémoire et espace disque sur les instances et de centraliser le tout. Les rapports sont fournis, intégrés à Management Studio et la possibilité d'enrichir les collections de données est offerte.