> Data > Evitez la zone rouge

Evitez la zone rouge

Data - Par iTPro.fr - Publié le 24 juin 2010
email

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

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

Microsoft 365 : HP Subscription Management Services en détail

Microsoft 365 : HP Subscription Management Services en détail

Collaboration à distance, environnements de travail et productivité optimisés, gestion évolutive des licences, accélérez la transformation de votre business pour le faire entrer dans l’ère de la collaboration hybride. Découvrez comment le service de gestion des abonnements HP peut vous aider à optimiser vos investissements et votre stratégie de gestion de vos abonnements Microsoft 365.

Data - Par iTPro.fr - Publié le 24 juin 2010

A lire aussi sur le site

Revue Smart DSI

La Revue du Décideur IT