SQL Server : lister les tables par taille espace disque

Les administrateurs de SGBD doivent souvent gérer des bases de données de plusieurs Go, voire le To. Quand une BDD gonfle de manière exponentielle et sans explication apparente, le plus simple est de regarder quelle table occupe le plus d’espace. Une simple requête nous donnera la réponse, pour éviter d’avoir à vérifier chacune des centaines de tables que peuvent utiliser les applications métier, un ERP ou un logiciel de comptabilité.

Il s’agit ici de voir la taille occupée par les tables de la database au format MSSQL, c’est-à-dire contenues dans le fichier MDF. Le journal (log) de cette DB se trouve quant à lui dans le fichier LDF mais ce n’est pas l’objet de ce guide.

Pour réaliser ce tutoriel, le logiciel gratuit Microsoft SQL Server Management Studio a été utilisé puisqu’il s’agit de l’utilitaire officiel pour gérer un serveur de bases de données Microsoft SQL Server. On peut cependant utiliser un autre programme pour sortir des informations d’une database SQL Server.

Requête SQL Server pour lister les tables par taille, nombre de lignes et espace disque occupé

1. Ouvrir le logiciel Microsoft SQL Server Management Studio ou tout autre outil permettant d’exécuter des requêtes sur une base SQL Server.

2. Se connecter avec un compte utilisateur SQL ou par une authentification Windows.

3. Faire un clic droit sur la base de données et choisir Nouvelle requête.

4. Dans le champ vide, taper la requête suivante :

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY UsedSpaceKB DESC

5. Cliquer sur le bouton « Exécuter » pour lancer la recherche.

6. Le résultat s’affiche avec une liste de toutes les tables de la BDD, triées par « UsedSpaceKB » donc l’espace utilisé en KB.

On peut maintenant cibler les recherches pour diminuer la taille de la table la plus volumineuse et ainsi réduire l’espace disque utilisé par le fichier MDF de la base SQL Server.