Taille des tables SQL Server et espace disque

Lister la taille des tables SQL Server et l’espace disque est pratique pour analyser l’utilisation du disque pour chaque table. Trois façons différentes, mais similaires sont présentées ici. Le but, afficher les tables SQL Server et l’espace disque utilisé par chaque table. La troisième solution montre également le nombre de lignes pour chaque table.

Afficher les noms des tables SQL Server avec leur taille et l’espace disque utilisé

Consulter ces requêtes SQL Server pour afficher la liste des tables SQL Server et leur taille de différentes manières.

Cette première requête utilise différentes tables système liées avec une jointure, à savoir sys.tables, sys.indexes, sys.partitions, sys.allocation_units et sys.schemas.

Elle affiche le nom du schéma, le nom de la table, le nombre de lignes et l’espace disque. Soit l’espace total, utilisé et inutilisé pour chaque table d’une base SQL Server.

SELECT
     sch.name as SchemaName,
     tab.name as TableName,
     par.rows as RowCounts, 
     sum(alc.total_pages) * 8 as TotalSpace,
     sum(alc.used_pages) * 8 as UsedSpace,
     (sum(alc.total_pages) - sum(alc.used_pages)) * 8 as UnusedSpace
FROM sys.tables tab 
INNER JOIN sys.indexes ind 
     ON tab.object_id = ind.object_id 
INNER JOIN sys.partitions par 
     ON ind.object_id = par.object_id 
     and ind.index_id = par.index_id 
INNER JOIN sys.allocation_units alc 
     ON par.partition_id = alc.container_id 
LEFT OUTER JOIN sys.schemas sch 
     ON tab.schema_id = sch.schema_id 
GROUP BY 
     tab.name, 
     sch.name, 
     par.rows 
ORDER BY 1,2;

Pour aller plus loin, l’objectif est généralement de vérifier et de libérer de l’espace disque sur le serveur hébergeant la base de données. Typiquement, lorsque le disque dur du serveur commence à être plein. Alors cette requête affiche la dernière fois qu’une table a été accédée ou mise à jour.

C’est très utile avant la suppression. Par exemple, si une table n’a pas été consultée au cours des trois dernières années, elle n’est peut-être plus nécessaire.

L’utilisation de l’espace disque dans SQL Server est un sujet de maintenance classique

En effet, toute base de données grandit, parce que le journal grandit, les données grandissent, le fichier journal grandit, le fichier de sauvegarde grandit, etc.

Pour remédier à cela il est obligatoire de planifier des scripts de maintenance pour nettoyer les tables de log et supprimer régulièrement les données obsolètes, comme par exemple une période de cinq années glissantes pour rester en ligne.

Pour des raisons de conformité légale, selon le domaine d’activité, il peut être nécessaire de conserver et de sauvegarder les données plus longtemps.

Lister la taille des tables SQL Server avec la vue système information_schema.tables

Cette approche consiste à utiliser les données stockées dans le schéma information_schema concernant les tables. La requête Microsoft SQL utilise la table système information_schema.tables pour afficher tous les attributs et métadonnées de la base de données système.

Elle affiche les quatre colonnes disponibles dans la vue système des tables :

• TABLE_CATALOG est le nom de la base de données.
• TABLE_SCHEMA est le schéma hébergeant la table, par défaut il s’agit du propriétaire de la base de données (dbo).
• TABLE_NAME est le nom de la table.
• TABLE_TYPE est le type de la table.

Notez que nous filtrons la requête sur la vue pour afficher uniquement le type de table de base. D’autres types de tables peuvent également être affichés.

SELECT 
     TABLE_CATALOG, 
     TABLE_SCHEMA, 
     TABLE_NAME, 
     TABLE_TYPE
FROM 	information_schema.tables
WHERE 	table_type='base table';

Lister toutes les tables SQL à partir de la table système sys.tables

La dernière requête utilise la table système sys.tables, elle est certainement la source de la vue système utilisée dans la première requête. Elle montre beaucoup plus d’informations et de métadonnées. Comme les données de création, la date de modification, et l’object_id réutilisable dans d’autres fonctions et procédures du système.

-- Utiliser la table système sys.tables
SELECT 	*
FROM 	sys.tables
WHERE 	[type_desc] = 'USER_TABLE';

Pour aller plus loin dans le sujet de la maintenance des bases de données SQL Server, consultez ce court tutoriel pour libérer de l’espace disque avec un script SQL Shrink Database.

Vider le journal des transactions SQL Server