[sql serveur] vérifier l'espace occupé dans une base de données

Commenter

Contenu du snippet

Bonjour,

Dans le cadre de mon travail, je dois régulièrement suivre l'occupation des données
dans une base SQL Serveur.

Dans Entreprise Manager d'SQL 2000, on peut facilement visualiser l'occupation des données.
Sous SQL 2005, on peut aussi, mais seulement en changeant le mode de compatibilité,
ce que je ne souhaite pas faire.

Donc, j'ai créé un script qui me permet de récupérer les informations minimales et
nécessaire et les affiche.

Ca fait des années que je profite de toutes les informations du réseau code-sources,
à moi de donner maintenant.

Les points que je trouve intéressant dans mon script, en dehors de sa finalité, c'est :
- Gestion des tables temporaires avec tests de leurs présences.
- Exécution de procédures systèmes et affectation du résultat dans des tables.
- Lectures des emplacements physiques des données.
- Lecture des noms logiques, car certains traitements veulent ces caractéristiques.
- Conversion des valeurs des tables en Mégaoctets.

J'ai choisi de faire un script qui s'exécute indifféremment sous SQL 2000 ou 2005
pour avoir plus de libertés.

Source / Exemple : 


-- Test et suppression des tables temporaires si elles existent.
-- Cela permet d'executer ce script plusieurs fois sana avoir besoin de faire de changements
IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE ID = OBJECT_ID(N'TEMPDB..#TableTempInfosData'))
	DROP TABLE #TableTempInfosData

IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE ID = OBJECT_ID(N'TEMPDB..#TableTempInfosLog'))
	DROP TABLE #TableTempInfosLog

------------------------------------------------------------------------------------------------------
-- Création des tables temporaires
-- Cette table réceptionne les informations concernant le fichier de données
CREATE TABLE #TableTempInfosData (
	Identifiant INT,
	IndexGroupeFichier INT, 
	EspaceTotal INT, 
	EspaceUtiliser INT,
	NOM VARCHAR(1024),
	NomFichier VARCHAR(1024)
)

-- Cette table réceptionne les informations concernant le fichier de logs
CREATE TABLE #TableTempInfosLog
(
	NomBase VARCHAR(32),
	TailleLog REAL,
	EspaceUtilise REAL,
	Statut INT
)

------------------------------------------------------------------------------------------------------
DECLARE @NomDatabase VARCHAR(255) 

-- Récupération du nom de la base de données en cours
SELECT @NomDatabase = DB_NAME(dbid) FROM MASTER..SYSPROCESSES WHERE SPID = @@SPID

-- Récupération d'informations générales sur la base en cours
DECLARE @NomLogiqueFichierDonnees AS VARCHAR(255)
DECLARE @NomPhysiqueFichierDonnees AS VARCHAR(255)
DECLARE @TailleFichierDonnees AS INT

DECLARE @NomLogiqueFichierLog AS VARCHAR(255)
DECLARE @NomPhysiqueFichierLog AS VARCHAR(255)
DECLARE @TailleFichierLog AS INT

-- Récupération des noms logiques des fichiers de la base en cours
SET @NomLogiqueFichierDonnees = FILE_NAME ( 1 ) 
SET @NomLogiqueFichierLog = FILE_NAME ( 2 ) 

-- Récupération des noms physiques des fichiers de la base en cours
SELECT @NomPhysiqueFichierDonnees = FILENAME FROM MASTER..SYSALTFILES WHERE NAME = @NomLogiqueFichierDonnees
SELECT @NomPhysiqueFichierLog = FILENAME FROM MASTER..SYSALTFILES WHERE NAME = @NomLogiqueFichierLog

------------------------------------------------------------------------------------------------------
-- Remplissage des tables temporaires avec les procédures systèmes
-- Avec suppression des messages d'informations sans intérêts

-- Cette procédure système n'est pas documentée, mais elle est bien utile.
-- Elle donne l'occupation des données dans la base active
INSERT INTO #TableTempInfosData 
EXECUTE ('DBCC SHOWFILESTATS WITH NO_INFOMSGS')

-- Cette procédure donne l'occupation des données dans le fichier de log
INSERT INTO #TableTempInfosLog 
EXECUTE ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS')

------------------------------------------------------------------------------------------------------
-- Traitement des données
DECLARE @TailleData FLOAT
DECLARE @DataUtilise FLOAT
DECLARE @DataVide FLOAT
DECLARE @PourcentDataPleine FLOAT
DECLARE @PourcentDataVide FLOAT

DECLARE @TailleLog FLOAT
DECLARE @EspaceLogUtilise FLOAT
DECLARE @EspaceLogVide FLOAT
DECLARE @PourcentLogPlein FLOAT
DECLARE @PourcentLogVide FLOAT

-- Lecture des valeurs du fichier de données
SELECT @TailleData = (SUM(EspaceTotal) * 64 * 100) / 1024, 
@DataUtilise = (SUM(EspaceUtiliser) * 64 * 100) /1024 
FROM #TableTempInfosData

-- Calcul pour avoir la taille en Mega à deux décimales
SET @TailleData = @TailleData / 100
SET @DataUtilise = @DataUtilise / 100
SET @DataVide = @TailleData - @DataUtilise

-- Calcul des poucentages d'occupations
SET @PourcentDataVide = (1 - @DataUtilise / @TailleData ) * 100
SET @PourcentDataPleine = 100 - @PourcentDataVide

-- Lecture des valeurs du fichier de log
SELECT @TailleLog = TailleLog, 
@EspaceLogUtilise = (TailleLog * EspaceUtilise) / 100
FROM #TableTempInfosLog 
WHERE NomBase = @NomDatabase

-- Calcul l'espace libre dans le fichier de log
SET @EspaceLogVide = @TailleLog - @EspaceLogUtilise

-- Calcul des poucentages d'occupations
SET @PourcentLogVide = (1 - @EspaceLogUtilise / @TailleLog ) * 100
SET @PourcentLogPlein = 100 - @PourcentLogVide

------------------------------------------------------------------------------------------------------
-- Affichage des différents résultats
PRINT '--------------------------------------------------------------------------------------------------------- ' 
PRINT 'BASE :  ' + @NomDatabase + '   |   Nom Logique Données :  ' + @NomLogiqueFichierDonnees+ '   |   Nom Logique Logs :  ' + @NomLogiqueFichierLog
PRINT '--------------------------------------------------------------------------------------------------------- ' 
PRINT 'NOM PHYSIQUE DONNEES | ' + @NomPhysiqueFichierDonnees
PRINT 'NOM PHYSIQUE LOGS    | ' + @NomPhysiqueFichierLog
PRINT '--------------------------------------------------------------------------------------------------------- ' 
PRINT 'FICH DATA TAILLE     | ' + CAST(ROUND(@TailleData, 2) AS VARCHAR) + ' Mo'
PRINT 'FICH DATA PLEIN      | ' + CAST(ROUND(@DataUtilise, 2) AS VARCHAR) + ' Mo - ' + CAST(ROUND(@PourcentDataPleine, 2) AS VARCHAR) + ' %'
PRINT 'FICH DATA VIDE       | ' + CAST(ROUND(@DataVide, 2) AS VARCHAR) + ' Mo - ' + CAST(ROUND(@PourcentDataVide, 2) AS VARCHAR) + ' %'
PRINT '--------------------------------------------------------------------------------------------------------- ' 
PRINT 'FICH LOG TAILLE      | ' + CAST(ROUND(@TailleLog, 2) AS VARCHAR) + ' Mo'
PRINT 'FICH LOG PLEIN       | ' + CAST(ROUND(@EspaceLogUtilise, 2) AS VARCHAR) + ' Mo - ' + CAST(ROUND(@PourcentLogPlein, 2) AS VARCHAR) + ' %'
PRINT 'FICH LOG VIDE        | ' + CAST(ROUND(@EspaceLogVide, 2) AS VARCHAR) + ' Mo - ' + CAST(ROUND(@PourcentLogVide, 2) AS VARCHAR) + ' %'
PRINT '--------------------------------------------------------------------------------------------------------- '

Conclusion :


J'espère que vous trouverez un intérêt à cette source.
Les seules sources s'en approchant ici, parle de la place sur disque dur,
mais rien sur l'occupation dans l'enveloppe de la base.

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.