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

Soyez le premier à donner votre avis sur cette source.

Snippet vu 19 140 fois - Téléchargée 16 fois

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

Ajouter un commentaire

Commentaires

lfresel
Messages postés
1
Date d'inscription
vendredi 28 septembre 2007
Statut
Membre
Dernière intervention
9 décembre 2009
-
Personnellement j'ai modifié les lignes 81/83 par ceci

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

car j'avais un dépassement de capacité, que ce soit dans un float, un int ou un bigint

merci de ton code source !
Luc
dymsbess
Messages postés
56
Date d'inscription
mercredi 29 septembre 2004
Statut
Membre
Dernière intervention
4 janvier 2010
1 -
Voici un autre lien pour la volumétrie des tables :
http://www.xoowiki.com/Article/SQL-Server/volumetrie-des-tables-174.aspx
cs_NonoNantes
Messages postés
5
Date d'inscription
mardi 3 avril 2007
Statut
Membre
Dernière intervention
25 juin 2009
1 -
Re ...
Apres avoir continué mes recherches hier, j'ai trouvé un élément de solution et j'ai développé le truc ...
J'ai posté ca là (ou qqn cherchait exactement la meme chose que moi):
http://www.sqlfr.com/forum/sujet-TAILLE-TOUTES-TABLES-DANS-BASE_955109.aspx
voilà.
++
Nono
cs_NonoNantes
Messages postés
5
Date d'inscription
mardi 3 avril 2007
Statut
Membre
Dernière intervention
25 juin 2009
1 -
Bonjour,

Oui, ce script est intéressant, j'ai juste eu à mettre quelques infos en minuscules, ma base étant Case Sensitive (FRENCH_CS_AS)...

Par contre, je que je cherche, c'est un script qui permette, pour toutes les tables d'une base d'afficher le nombre de lignes et la taille utilisée.
(infos que l'on a en faisant click droit propriétés sur une table, mais quand on a 100 tables ... :-( )

Si qqn a ca sous le coude :-)
crn_c21
Messages postés
303
Date d'inscription
samedi 24 janvier 2004
Statut
Membre
Dernière intervention
4 février 2011
-
Très intéressant
Merci

Je l'ai incorporé à une proc de gestion d'espace disque avec un envoi de mail d'alerte lorsque le rapport taille disque/taille base devient inférieur à un certain seuil

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.