Une des tâches d’un administrateur de bases de données consiste à surveiller la taille des données dans un fichier d’une ou plusieurs bases de données. Un fichier de données dimensionné pour une durée d’exploitation conséquente contient de l’espace inutilisé. Dans ce cas là comment surveiller l’évolution des données à l’intérieur de ce fichier ? Un script proposé par Elsuket permet de surveiller les fichiers d’une base de données en particulier. Le code qui suit est une évolution du précédent : vous pouvez auditer l’ensemble des fichiers d’une base de données.

DECLARE @t_spaceused TABLE
(
nomBase SYSNAME,
nomLogique VARCHAR(100),
nomPhysique VARCHAR(100),
tailleFichier_MB DECIMAL(14,2),
accroissement VARCHAR(15),
espaceOccupe_MB DECIMAL(8,2),
espaceLibre_MB DECIMAL(14,2),
[%libre] DECIMAL(5,2)
);

DECLARE @sql VARCHAR(1000);
SET @sql = ‘USE [‘ + ‘?’ + ‘];
WITH fichiers_bases AS
(
    SELECT
    DB_NAME() AS nomBase,
    name AS nomLogique,
    physical_name AS nomPhysique,
    CASE is_percent_growth
    WHEN 0 THEN CAST(growth * 8 / 1024 AS VARCHAR(8)) +  » MB  »
    ELSE CAST(growth AS VARCHAR(8)) +  » % »
    END AS accroissement,
    (size * 8.0) / 1024 AS tailleFichier_MB,
    (FILEPROPERTY(name,  »SpaceUsed ») * 8.0) / 1024 AS espaceOccupe
FROM sys.database_files
WHERE type = 1 — Données et journaux
)
SELECT
    nomBase,
    nomLogique,
    nomPhysique,
    tailleFichier_MB,
    accroissement,
    espaceOccupe AS espaceOccupe_MB,
    CAST(tailleFichier_MB – espaceOccupe AS DECIMAL(14,2)) AS espaceLibre_MB,
    CAST((tailleFichier_MB – espaceOccupe) / tailleFichier_MB * 100.0 AS DECIMAL(5,2)) AS [%libre]
FROM fichiers_bases;';

INSERT INTO @t_spaceused
EXEC sp_MSForEachDB @sql;

SELECT *
FROM @t_spaceused
WHERE nomBase NOT IN (‘master’,’msdb’,’model’) — Bases utilisateurs + tempdb
ORDER BY [%libre] ASC;

Bon audit de fichier !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon