Archives de catégorie : Uncategorized

Volumétrie de données et disques d’une instance SQL Server

Publié le 2019-01-06 par SQLpro

Voici un batch simple pour obtenir la volumétrie globale, rapidement d’une instance MS SQL Server.

[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]

[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 : Le lien de téléchargement Frédéric Brouard – SQLpro – ARCHITECTE DE DONNÉES – expert SGBDR et langage SQL … Continuer la lecture →

Publié dans Requêtes de métadonnées, Requetes MS SQL Server, scripts SQL, SQL Server, Uncategorized | Laisser un commentaire

Archives de catégorie : Uncategorized

Volumétrie de données et disques d’une instance SQL Server

Articles récents

Commentaires récents

Archives

Catégories

Méta