Tâches quotidiennes du DBA : l’espace libre dans les bases de données

Comme tout DBA, une des tâche quotidienne consiste à vérifier qu’il y ait suffisamment d’espace disponible dans les bases de données afin de ne pas bloquer l’activité du serveur SQL.

Plus que l’espace disponible par fichier, je m’intéresse à l’espace disponible dans un groupe de fichier.

Pourquoi ?
J’ai la chance de pouvoir travailler sur du matériel m’offrant suffisamment de ressources disques pour mettre en place la stratégie de stockage suivante :

  • Une LUN TempDB Data
  • Une LUN TempDB Log
  • Trois LUN Database Data + Index
  • Une LUN Database Log
  • Une LUN Backup

Par ailleurs, il est conseillé de travailler avec différents groupes de fichiers :

  • Le filegroup PRIMARY dédié aux tables système
  • Un filegroup DATA pour les données (voire plusieurs si votre BD est volumineuse –> stratégie de backup par filegroup ou bien si vous utilisez le partitionnement de table)
  • Un filegroup INDEX … pour les index (non cluster, nous sommes bien d’accord !!! L’index cluster ne peut être dissocié des données)

Autre aspect notable des groupes de fichiers : la répartition des données. En effet si votre groupe de fichier est constitué de plusieurs fichiers, alors SQL Server va répartir de manière uniforme les données sur tous les fichiers de ce groupe. Souvenez vous aussi que lorsque vous créez une table ou un index, vous précisez sur quel groupe de fichier vous allez stocker l’information. Donc si votre groupe de fichier est constitué de 3 fichiers, il y aura 3 thread d’entrée/sortie créés.  Et si vos 3 fichiers sont basés sur des sous-systèmes disques différents, alors non seulement vous pourrez bénéficier d’excellentes performances, mais au delà, statistiquement, pour l’accès aux données, vous avez de grandes chances de réduire le verrouillage … Donc des gains de performances en vue.

Voilà pourquoi je préfère avoir une vue plus générale et suivre l’évolution de mes groupes de fichiers plutôt que celle de chaque fichier individuellement.

Comment suivre cet indicateur ?

Si l’on fait le tour des possibilités offertes, plusieurs méthodes seraient possibles :

  • Créer des alertes sur condition de performance dans l’agent SQL : désolé, on ne peut suivre que le pourcentage de remplissage de la log de SQL. Utile, voire crucial, mais ça ne répond pas à mon besoin.
  • Utiliser le Management Data Warehouse et activer le disk usage : oh les joli graphiques …. Intéressant pour suivre l’évolution de la volumétrie et anticiper un changement de matériel ou un ajout de LUN, mais ça ne répond pas à mon besoin spécifique.
  • Utiliser un outil de monitoring du commerce, Microsoft ou autre. Ca peut revenir assez cher et ne pas forcément correspondre à ce que l’on attend.
  • Passer les bases une par une pour afficher le rapport Disk Usage, à raison de 20 secondes par rapport (valeur tout juste réaliste, le temps d’afficher le rapport et de le consulter), si comme moi, vous avez plus de 200 bases ça vous prendra plus d’1 heure … Cool !!!
  • Trouver un stagiaire qui le fasse pour vous : pas bête,mais songez à son mémoire de stage !!!
  • Reste la solution propriétaire, écrire une belle petite requête qui vous donne l’espace disponible par groupe de fichiers. De manière ensuite à se focaliser sur ce qui est vraiment problématique …

Voici donc une petite requête qui satisfait à mon besoin, libre à vous de l’adapter …

Create table #AllDbSpace (
    DbName varchar(100),
    FgName varchar(100),
    NbFiles int,
    CurrentSizeMB int,
    FreeSpaceMB int,
    FreeSpacePct int
)

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SQLScript VARCHAR(6000)

DECLARE DatabaseCursor CURSOR FOR
                SELECT [name] FROM master..sysdatabases
                 where dbid > 4
                ORDER BY [name]

OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
        BEGIN

                  SET @SQLScript = ‘USE ‘ + @DatabaseName + ‘;
                                                  With dbSpace’+ @DatabaseName +’ as
                                                    (
                                                        SELECT DB_NAME() AS DbName,
                                                        fg.name AS FgName,
                                                        count(f.name) AS NbFiles,
                                                        sum(f.size/128) AS CurrentSizeMB,
                                                        sum(f.size/128-CAST(FILEPROPERTY(f.name, »SpaceUsed ») AS INT)/128) AS FreeSpaceMB
                                                        FROM sys.database_files f
                                                        inner join sys.filegroups fg on fg.data_space_id = f.data_space_id
                                                        group by fg.name
                                                    )
                                                    INSERT INTO #AllDbSpace
                                                    Select DbName,FgName,NbFiles,CurrentSizeMB,FreeSpaceMB,FreeSpaceMB*100/CurrentSizeMB as  »%FreeSpace »
                                                    FROM dbSpace’+ @DatabaseName +’;
                                                    ‘

                EXEC (@SQLScript)
                FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

        END

CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

SELECT DbName,FgName,NbFiles,CurrentSizeMB,FreeSpaceMB,FreeSpacePct as ‘%FreeSpace’ FROM #AllDbSpace
DROP TABLE #AllDbSpace

Certes il y a un beau curseur, mais c’est une solution relativement simple pour balayer toutes les bases d’une instance.

En retour, vous obtenez le liste des bases, avec les groupes de fichiers, le nombre de fichiers, l’espace total cumulé, l’espace libre cumulé et un pourcentage.

image

Il ne reste plus qu’a exécuter cette requête sur toutes vos instance ou bien d’utiliser la fonction de requête multi serveur de SQL Server Management Studio version 2008. Cela vous ajoute le nom de l’instance en première colonne. C’est une bonne solution.

Par ces temps de grosse chaleur (35°, 38° => entre chaque paragraphe, un plouf dans la piscine), on devient vite flémard. Tout les jours, rejouer la même requête …

Plusieurs solutions :

  • Vous prenez un stagiaire : hum pas très épanouissant non plus …
  • Vous êtes un/une fan invétéré d’Excel : pas de problème, vous pouvez vous connecter à votre instance, lancer la requête et faire de beaux graphiques
    image
  • Vous être un kimble de Reporting Services comme Jean-Pierre, vous faire un beau rapport (notez que le %FreeSpace, quand les groupes sont réduits indique la valeur minimale des filegroups de la base. Cela permet de parcourir plus rapidement la liste et de ne s’attarder que sur les valeurs faibles. Il est aussi possible de trier différemment le rapport.
    image
  • Rapport auquel vous pouvez vous abonner (recevoir par email, c’est assez sympa et ça fait très pro ..). Vous pouvez aussi le recevoir au format Excel, Word, PDF, CSV, etc …
    image
    Le rapport n’affiche que les valeurs agrégées, et si une valeur parait trop faible, il suffit de cliquer sur le lien présent en bas de l’email afin d’accéder au rapport complet.

Voilà une bonne chose de faite … Après la surveillance de l’espace disponible, il vous reste du temps pour vos autres tâches quotidiennes : vérification des sauvegardes et des divers jobs de l’agent SQL, l’error log de SQL Server, l’état du log shipping ou du mirroring, les réplications, les requêtes trop consommatrices de ressources. Sans compter avec des tâches hebdomadaires comme jeter un œil sur la fragmentation des index, etc …

 

Pfiou, que d’articles en prévision. mais il fait trop chaud pour travailler !!!!

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans SQL Server, est tagué . Ajoutez ce permalien à vos favoris.

2 commentaires pour Tâches quotidiennes du DBA : l’espace libre dans les bases de données

  1. Jean-Pierre dit :

    Avec SSRS 2008, tu aurais pu mettre une jauge pour le taux de remplissage…comme dans Excel

  2. Seb31tlse dit :

    Un peu de User Experience quoi ! :)Et hop, 20 minutes plus tard : http://www.dotmim.com/SiteFiles/Sample.jpgBon c\’est pas fini hein !

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s