par Gregory A. Larsen - Mis en ligne le 26/11/2003
Suivez ce processus en 2 étapes pour suivre et surveiller l'utilisation des disques
Vos clients ou managers vous ont-ils
jamais demandé de combien leurs
bases de données avaient grandi au
cours de l'année écoulée ? Avez-vous
dû prévoir la capacité disque nécessaire
pour la prochaine année d'après
le taux de croissance moyen de la base
de données au cours des 12 derniers
mois ?Vos clients ou managers vous ont-ils
jamais demandé de combien leurs
bases de données avaient grandi au
cours de l'année écoulée ? Avez-vous
dû prévoir la capacité disque nécessaire
pour la prochaine année d'après
le taux de croissance moyen de la base
de données au cours des 12 derniers
mois ? Compte tenu du présent taux de
croissance, combien de temps durera
votre espace disque non alloué actuel ?
Pour répondre à ce genre de questions
sur la croissance de la base de données
ou sur l'espace disque, il faut disposer
d'un historique de l'utilisation de l'espace
touchant à vos bases de données.
J'ai créé un processus qui vous permettra
de collecter automatiquement
des statistiques d'utilisation de l'espace
pour chacune de vos bases de
données. Muni de ces chiffres, il vous
sera facile d'effectuer un simple calcul
de taux de croissance.
Voilà quelques mois, j'ai décidé
d'élaborer un processus de capture
d'informations sur l'utilisation de l'espace
disque pour chaque base de données
d'un système, afin d'en suivre la
consommation au fil du temps. Je voulais
connaître la quantité d'espace allouée
et utilisée pour les fichiers de
données et de journalisation. En fait, je
recherchais la même information que
celle que l'on voit dans le panneau
Database Details d'Enterprise Manager
quand on consulte l'information Space
Allocated, mais il me fallait cette information
en code T-SQL. En utilisant
SQL Server Profiler, j'ai constaté
qu'Enterprise Manager obtient des informations
sur l'espace alloué en utilisant
deux instructions DBCC. La première,
SQLPERF, est documentée ; la
seconde, SHOWFILESTATS, ne l'est
pas. En exécutant manuellement
DBCC SHOWFILESTATS sur chaque base de données et en comparant la
sortie obtenue avec celle qu'Enterprise
Manager affichait, j'ai vu que cette
commande me fournirait l'information
sur l'espace disque par base de données.
SQL Server 2000 et SQL Server 7.0
utilisent tous deux ces instructions
DBCC pour peupler l'écran Space
Allocated d'Enterprise Manager.
L'instruction DBCC SQLPERF(LOGSPACE)
renvoie des informations sur
l'espace de journalisation des transactions
- la taille du journal allouée pour
chaque base de données en méga-octets
et le pourcentage d'espace de
journalisation utilisé pour chaque base
de données - et ce pour toutes les
bases de données. Un simple calcul
permet de convertir facilement le
pourcentage de l'espace journal utilisé,
en méga-octets. Cette instruction
DBCC m'a aidé à connaître l'espace de
fichier log que je voulais suivre.
J'ai utilisé l'instruction DBCC
SHOWFILESTATS non documentée
qui donne des informations sur l'utilisation
de l'espace pour les données
d'une base de données, pour obtenir
le reste des statistiques d'espace
disque souhaité. Cette instruction renvoie
un enregistrement par fichier de
données physique. Chaque enregistrement
statistique ainsi obtenu occupe
six colonnes : Fileid, FileGroup,
TotalExtents, UsedExtends, Name et
FileName. On peut utiliser la colonne
TotalExtents pour déterminer l'espace
total alloué aux données et la colonne
UsedExtents pour déterminer l'espacetotal utilisé pour les données. En additionnant
les valeurs TotalExtents et
UsedExtents de tous les fichiers d'une
base de données, puis en convertissant
le nombre d'extensions en méga-octets,
j'ai calculé l'espace total alloué
aux données et l'espace total utilisé
pour elles. Ces calculs m'ont fourni
l'information d'utilisation de l'espace
que je voulais suivre dans le temps. La
figure 1 montre un exemple de sortie
de la commande DBCC SHOWFILESTATS
après qu'on l'aie appliquée à la
base de données maîtresse.
J'ai intégré ces deux instructions
DBCC dans un processus qui collecte
l'information d'espace par base de
données. Il s'exécute périodiquement
et enregistre les statistiques sur l'utilisation
de l'espace dans une table de
base de données. Le processus est
constitué d'un job SQL Server Agent
qui contient deux étapes. La première
exécute une procédure stockée nommée
u
Evitez la zone rouge
La procédure stockée usp_get_dbstats,
que montre le listing 1, est la partie
principale du processus de collecte de
statistiques d’utilisation de l’espace.
Elle interroge les tables système et génère
et exécute des instructions PRINT
pour produire un script T-SQL qui, à
l’exécution, utilise deux instructions
DBCC pour extraire l’information actuelle
sur l’utilisation de l’espace.
Voyons une après l’autre les sections
de cette procédure stockée.
Le code du renvoi A dans le listing
1 recueille l’information sur l’utilisation
de l’espace de journalisation. Ce
bloc de code, comme les autres du listing
1, génère et exécute une suite
d’instructions PRINT qui deviennent le
script T-SQL qui recueille les statistiques
d’utilisation de l’espace. Ce
code produit un ensemble d’instructions
T-SQL qui créent une table temporaire
appelée #tmplg, puis peuple
celle-ci avec la sortie provenant
de DBCC SQLPERF(LOGSPACE).
L’instruction INSERT INTO qui a l’option
EXECUTE met la sortie de l’instruction
DBCC dans la table #tmplg,
qui pourra contenir un enregistrement
pour chaque base de données présente
sur le serveur. Chaque enregistrement
contiendra des informations qui vont dans les colonnes libellées
DBName, LogSize, LogSpace et Status.
Vous pouvez trouver les définitions de
chacune de ces colonnes dans SQL
Server Books Online (BOL) sous le
titre « DBCC SQLPERF ».
Le renvoi B montre le code qui
crée la table temporaire #tmp_stats.
Chaque enregistrement de cette table
contiendra les informations sur l’utilisation
de l’espace de données et de
journal pour une base de données. Les
blocs de code suivants peupleront et
utiliseront cette table temporaire.
Cette section de code exécute une
suite d’instructions PRINT à ajouter au
script T-SQL que le code du renvoi A a
démarré.
Le code du renvoi C génère l’instruction
DBCC SHOWFILESTATS pour
chaque base de données. Ce morceau
de code génère aussi les instructions
T-SQL Server qui fusionnent l’information
DBCC SHOWFILESTATS avec les
enregistrements de la table #tmplg
pour chaque base de données, de manière
à produire un enregistrement par
base de données contenant les statistiques
d’espace disque, données et log.
Là encore, le code utilise des instructions
PRINT pour générer le code SQL
qui collectera l’information DBCC
SHOWFILESTATS.
Rappelez-vous que l’information
DBCC SQLPERF génère l’information
de taille de log pour toutes les bases de
données, tandis que l’instruction
DBCC SHOWFILESTATS ne collecte les
tailles de données que pour la base de
données courante. Cette limitation du
champ d’action de la commande
DBCC SHOWFILESTATS implique
donc que la procédure stockée génère
le code qui appliquera l’instruction
DBCC SHOWFILESTATS à chaque base
de données sur la boîte SQL Server.
Le code du renvoi C utilise un curseur,
AllDatabases, qui contiendra une
liste des bases de données du système.
Ce curseur permet à la procédure stockée
de balayer la liste des bases de
données à l’intérieur d’une boucle
WHILE pour générer une instruction
DBCC SHOWFILESTATS pour chaque base de données. A l’intérieur de la
boucle WHILE, la procédure stockée
crée le code destiné à créer une table
temporaire, #tmp_sfs, qui contiendra
la sortie de l’instruction DBCC SHOWFILESTATS,
suivie d’une instruction INSERT
INTO. Là encore, j’ai utilisé l’instruction
INSERT INTO avec l’option
EXECUTE pour insérer l’information
de l’instruction DBCC SHOWFILESTATS
dans la table temporaire. Les dernières
lignes de code de cette section
génèrent le code qui convertira la colonne
LogSpaceUsed en table #tmplg
à partir d’un pourcentage d’espace utilisé
dans une valeur exprimée en
méga-octets. Après quoi, le code
peuple la table #tmp_stats avec les statistiques
courantes d’utilisation d’espace
de données et log pour la base de
données courante.
Le code du renvoi D génère les instructions
T-SQL chargées de mettre les
statistiques d’utilisation de l’espace de
données et log dans une table permanente.
Cette section utilise une instruction
INSERT INTO simple pour
peupler une table permanente, DBSTATS,
avec les statistiques courantes
d’utilisation d’espace de base de données
calculées que la table permanente
#tmp_stats contient.
Le listing Web 1 montre un
exemple de script T-SQL tel qu’il se
présenterait si vous exécutiez
usp_get_dbstats sur un serveur qui ne
comporte qu’un petit nombre de bases
de données. Cette sortie a été produite
sur un système qui n’avait que les
bases de données SQL Server standard
installées (master, model, msdb, Northwind, Pubs et tempdb) plus une
base de données définie par l’utilisateur
(DBA). Notez que dans ce listing,
un morceau de code collecte l’information
sur l’espace des journaux de
transactions en utilisant la commande
DBCC SQLPERF(LOGSIZE). Sept sections
de code similaires, une pour
chaque base de données, utilisent l’instruction
DBCC SHOWFILESTATS pour
recueillir les statistiques d’utilisation
de l’espace de données.
Téléchargez cette ressource
![Guide de Reporting Microsoft 365 & Microsoft Exchange](https://www.itpro.fr/wp-content/uploads/2023/06/Promodag-Reports-pour-Microsoft-Exchange-Solution-de-reporting-detaille-de-lutilisation-de-Microsoft-Exchange-2023-1.jpg)
Guide de Reporting Microsoft 365 & Microsoft Exchange
Comment bénéficier d’une vision unifiée de vos messageries, mieux protéger vos données sensibles, vous conformer plus aisément aux contraintes réglementaires et réduire votre empreinte carbone ? Découvrez la solution de reporting complet de l’utilisation de Microsoft Exchange, en mode on-premise ou dans le Cloud.
Les articles les plus consultés
- L’utilisation des données pour survivre !
- Stockage autonome, Evolutivité & Gestion intelligente, Pure Storage offre de nouvelles perspectives aux entreprises
- 9 défis de transformation digitale !
- Big Data -> 5 ans après : phénomène de mode ou réalité ?
- ActiveViam fait travailler les data scientists et les décideurs métiers ensemble
Les plus consultés sur iTPro.fr
- Impact de l’IA générative sur le lieu de travail
- Red Sift : une proposition clé pour sécuriser le patrimoine numérique
- Cloud Temple : le cloud de confiance de référence du marché français et européen
- Fusion d’identités avec Binary Tree Directory Sync Pro
- Contrôler et optimiser les dépenses informatiques dans le contexte macroéconomique actuel
![Revue Smart DSI](https://www.itpro.fr/wp-content/uploads/2024/07/SMART-DSI-Numero-34-Juin-2024.jpg)