Les requêtes de cette page lisent des informations de l'instance sans modifier vos données. Les commandes qui modifient quelque chose sont signalées dans le texte. Testez toujours sur un environnement non critique avant la production, et relisez chaque requête avant de l'exécuter.
1. Taille et espace libre des fichiers
À exécuter dans la base à analyser.
-- Fichiers de la base courante : taille, espace utilisé et libre, paramètres de croissance
SET NOCOUNT ON;
SELECT
DB_NAME() AS base,
f.name AS fichier_logique,
f.type_desc AS type_fichier,
f.physical_name AS chemin,
CONVERT(decimal(18, 1), CONVERT(bigint, f.size) * 8 / 1024.0) AS taille_mo,
CONVERT(decimal(18, 1), CONVERT(bigint, FILEPROPERTY(f.name, 'SpaceUsed')) * 8 / 1024.0) AS utilise_mo,
CONVERT(decimal(18, 1), (CONVERT(bigint, f.size) - CONVERT(bigint, FILEPROPERTY(f.name, 'SpaceUsed'))) * 8 / 1024.0) AS libre_mo,
CASE WHEN f.is_percent_growth = 1
THEN CONCAT(f.growth, ' %')
ELSE CONCAT(CONVERT(bigint, f.growth) * 8 / 1024, ' Mo') END AS croissance,
CASE f.max_size WHEN -1 THEN 'illimitée'
WHEN 0 THEN 'croissance désactivée'
ELSE CONCAT(CONVERT(bigint, f.max_size) * 8 / 1024, ' Mo') END AS taille_max
FROM sys.database_files AS f
ORDER BY f.type, f.file_id;
-- Variante instance entière : taille des fichiers de toutes les bases
SELECT
DB_NAME(mf.database_id) AS base,
mf.type_desc AS type_fichier,
COUNT(*) AS nb_fichiers,
CONVERT(decimal(18, 1), SUM(CONVERT(bigint, mf.size)) * 8 / 1024.0) AS taille_mo
FROM sys.master_files AS mf
GROUP BY mf.database_id, mf.type_desc
ORDER BY base, type_fichier;- croissance en pourcentage : déconseillée sur les gros fichiers (10 % d'un fichier de 500 Go font 50 Go d'un coup). Préférez une valeur fixe en Mo.
- libre_mo faible sur un fichier de données : planifiez une extension manuelle, hors des heures de pointe, au lieu d'attendre l'extension automatique.
- taille_max : une limite sert de garde-fou, mais atteinte, elle bloque la base. Surveillez-la.
2. Extensions automatiques récentes
SQL Server enregistre les extensions de fichiers dans la trace par défaut (default trace), conservée sur quelques fichiers tournants. Elle est active par défaut. Ce script la lit.
-- Extensions automatiques (autogrowth) récentes, lues dans la trace par défaut
-- Droit requis : ALTER TRACE ou VIEW SERVER STATE selon la version.
SET NOCOUNT ON;
-- La trace par défaut est-elle active ? (1 = oui)
SELECT value_in_use AS trace_par_defaut_active
FROM sys.configurations
WHERE name = N'default trace enabled';
-- Chemin du fichier de trace : on remplace le nom courant par "log.trc" pour lire tous les fichiers tournants
DECLARE @chemin nvarchar(260);
SELECT @chemin = REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT
t.DatabaseName AS base,
t.FileName AS fichier_logique,
CASE t.EventClass WHEN 92 THEN 'Données' WHEN 93 THEN 'Journal' END AS type_fichier,
t.StartTime AS debut,
t.Duration / 1000 AS duree_ms,
CONVERT(decimal(18, 1), t.IntegerData * 8 / 1024.0) AS extension_mo,
t.ApplicationName AS application,
t.LoginName AS login,
t.SPID AS session_id
FROM sys.fn_trace_gettable(@chemin, DEFAULT) AS t
WHERE t.EventClass IN (92, 93) -- 92 = extension fichier de données, 93 = fichier journal
ORDER BY t.StartTime DESC;- duree_ms : une extension qui dure plusieurs secondes bloque les transactions en cours. C'est typique des fichiers journaux, qui doivent être initialisés à zéro, et des fichiers de données quand l'initialisation instantanée des fichiers n'est pas activée.
- Fréquence : des extensions nombreuses et petites indiquent une croissance mal réglée. Agrandissez le fichier en une fois, puis fixez une croissance de quelques centaines de Mo.
- application et login : l'origine de la charge qui a rempli le fichier, utile pour identifier un traitement batch ou un chargement.
- La trace par défaut garde peu d'historique, et son contenu peut tourner vite sur une instance très chargée. Pour un suivi durable, un événement étendu dédié est plus adapté : voir l'article sur les XEvents.