Voici un batch simple pour obtenir la volumétrie globale, rapidement d’une instance MS SQL Server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
[code language="sql"]CREATE TABLE ##STORAGE (DATABASE_NAME sysname, NATURE VARCHAR(16), FILE_NAME sysname, FILE_SIZE_MB DECIMAL(24,3), FILE_FREE_MB DECIMAL(24,3), FILE_FREE_PERCENT DECIMAL(5,3), DISK_NAME NVARCHAR(512), DISK_SIZE_MB DECIMAL(24,3), DISK_FREE_MB DECIMAL(24,3), DISK_FREE_PERCENT DECIMAL(5,3)); DECLARE @SQL NVARCHAR(max) = N'USE [?]; WITH T AS ( SELECT DB_NAME() AS DATABASE_NAME, type_desc AS NATURE, name AS FILE_NAME, db.size/128.0 AS FILE_SIZE_MB, (db.size - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 AS FILE_FREE_SPACE_MB, total_bytes / 1048576.0 AS DISK_SPACE_MB, volume_mount_point, available_bytes / 1048576.0 AS DISK_FREE_SPACE_MB FROM sys.database_files AS db CROSS APPLY sys.dm_os_volume_stats(DB_ID(), file_id) ) INSERT INTO ##STORAGE SELECT DATABASE_NAME, NATURE, FILE_NAME, FILE_SIZE_MB, FILE_FREE_SPACE_MB, 100.0 * FILE_FREE_SPACE_MB / FILE_SIZE_MB AS FILE_PERCENT_FREE, volume_mount_point AS DISK_NAME, DISK_SPACE_MB, DISK_FREE_SPACE_MB, 100.0 * DISK_FREE_SPACE_MB / DISK_SPACE_MB AS DISK_PERCENT_FREE FROM T;' EXEC sp_MSforeachdb @SQL; SELECT * FROM ##STORAGE; DROP TABLE ##STORAGE;[/code] |
Voici un exemple du résultat :
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *