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.