juin
2010
Un article de zinzineti
Pour purger les tables d’une base de données (pour des raisons X ou Y), il est parfois indispensable de savoir :
– D’abord l’espace disque occupé par la base de données (pour mesurer après l’effet de la purge)
– Ensuite quelle(s) table(s) purgée(s) ?
* Quelles sont les tables les plus grosses de la base ?
* Quelles sont les tables qui contiennent plus de lignes ?
* …
Voici une requête T-SQL qui permet d’auditer et donc de préparer la purge d’une base de données
1 ) Infos au niveau base de données
Quel espace disque occupe la base qu’on désire purger (AdventureWorks est utilisé ici comme base exemple) ?
GO
EXEC sp_spaceused @updateusage = N'TRUE';
GO
2 ) Infos au niveau tables et index
— Créer dans la base msdb une table pour stocker les infos des tables et index à auditer
GO
IF OBJECT_ID('dbo.T_AUDIT_TABLES', 'U') IS NOT NULL
DROP TABLE dbo.T_AUDIT_TABLES
GO
CREATE TABLE msdb.dbo.T_AUDIT_TABLES
(
[NomTable] nvarchar(20),
[NbLignes] char(11) NULL,
[EspacePrevu] varchar(18) NULL,
[TailleData] varchar(18) NULL,
[TailleIndex] varchar(18) NULL,
[EspaceInutilise] varchar(18)
) ON [PRIMARY]
GO
-- Se positionner sur la base à auditer
USE AdventureWorks
GO
DECLARE @TabEnCoursTraitement VARCHAR(50),@SQL VARCHAR (100), @DEL VARCHAR (100)
-- Stocker le nom des tables de la base dans une table temporaire
CREATE TABLE #LIST_TABLES (nom_table varchar (50))
INSERT INTO #LIST_TABLES
SELECT TABLE_SCHEMA+'.'+TABLE_NAME AS [NomTable]
FROM information_schema.tables
WHERE table_type='BASE TABLE'
ORDER BY [NomTable]
-- Tant qu'il y a des lignes dans la table temporaire
WHILE EXISTS ( SELECT nom_table
FROM #LIST_TABLES
)
BEGIN
--Se positionner sur la 1ère ligne de la table temporaire
SET @TabEnCoursTraitement = (SELECT TOP 1 nom_table FROM #LIST_TABLES)
-- Insérer les infos de la table en cours de traitement dans la table qui stocke les infos à auditer
SET @SQL = 'INSERT INTO msdb.dbo.T_AUDIT_TABLES EXEC sp_spaceused '''+ @TabEnCoursTraitement + ''';'
EXEC (@SQL);
-- Supprimer la table traitée de la table temporaire
SET @DEL = 'DELETE FROM #LIST_TABLES WHERE nom_table = '''+ @TabEnCoursTraitement + ''';'
EXEC (@DEL)
END
--Supprimer la table temporaire
DROP TABLE #LIST_TABLES
3) AUDIT DES TABLES DE LA BASE DE DONNEES
–> Quelles sont les 10 tables les plus grosses
, cast(replace([TailleData],'KB','') as int) AS [TailleData (en Ko)]
, cast(replace([TailleIndex],'KB','') as int) AS [TailleIndex (en Ko)]
,cast(replace([EspacePrevu],'KB','') as int) AS [EspaceReservé (en Ko)]
,cast(replace([EspaceInutilise],'KB','') as int) AS [EspaceInutilisé (en Ko)]
FROM msdb.dbo.T_AUDIT_TABLES
ORDER BY [TailleData (en Ko)] DESC
–> Quelles sont les 10 tables ayant le plus de lignes
, cast([NbLignes] as int) AS [Nombre de Lignes]
FROM msdb.dbo.T_AUDIT_TABLES
ORDER BY [Nombre de Lignes] DESC
Remarque : Ce n’est pas toujours la table qui contient plus de lignes qui est la plus grosse 
Si par exemple l’objectif de la purge est de libérer de l’espace disque par exemple il faut se pencher sur la plus grosse !
*********************************************************************************
Auteur : Etienne ZINZINDOHOUE
*********************************************************************************