Voici quelques procédures stockées systèmes et quelques suites de clics dans SQL Server Management Studio pour gérer un peu plus finement les fichiers d’information et d’erreur du moteur de bases de données de SQL Server, et de l’Agent SQL Server.

Commençons d’abord par l’emplacement de ces fichiers, qui sont en fait de simples fichiers texte : par défaut il s’agit de :
C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\

Le .n indique le numéro de l’instance s’il s’agit d’une instance nommée : dans le cas contraire, le .n est absent.
On trouve dans ce dossier :

=> Des fichiers ERRORLOG et ERRORLOG.[n], où [n] est un entier qui est d’autant plus grand qu’il est ancien.
Ce sont les fichiers de journaux d’erreurs de moteur de base de données.
Si la configuration des journaux d’événements n’a jamais été modifiée, on a ERRORLOG.6, car comme nous allons le voir, SQL Server ne conserve que 6 journaux d’erreur.

=> Des fichiers SQLAGENT et SQLAGENT.[n], où [n] est utilisé de la même façon que précédemment.
On l’aura compris, ces fichiers sont les journaux d’événements de l’Agent SQL Server.

=> Des fichiers FDLAUNCHERRORLOG et FDLAUNCHERRORLOG.[n], qui sont les journaux d’événements du moteur de recherche en texte intégral de SQL Server.

=> Des fichiers log_xx.trc, qui sont les fichiers de trace SQL Profiler par défaut de SQL Server.
En effet une trace SQL Profiler est démarrée par défaut à chaque démarrage d’un instance SQL Server.
En interrogeant la vue système sys.traces, vous verrez que cette trace est toujours démarrée et porte l’id de valeur 1.

Tous les fichiers présents dans ce répertoire sont interprétables par n’importe quel éditeur texte, à l’exception des fichiers de trace par défaut.
On peut ouvrir ces derniers directement par un double-clic qui exécutera SQL Profiler.

Dans ce billet nous nous limiterons à la gestion des fichiers d’événements :
Рdu moteur de bases de donn̩es SQL Server
– de l’Agent SQL Server

On peut tout à fait changer le nombre de journaux d’erreur à converser, et on peut aussi automatiser leur renouvellement, par exemple avec un fichier par jour.
Cela permet, lorsqu’on audite ces fichiers ou qu’on conduit des investigations à la suite d’un problème, d’avoir un fichier de journal assez petit, en ne se focalisant que sur un seul jour.

Voyons d’abord comment lire ces fichiers journaux à l’aide de SQL Server Management Studio.
Après avoir ouvert l’Explorateur d’Objets (F8), le nÅ“ud Gestion une fois développé, nous affiche un nÅ“ud Journaux SQL Server:

Un double-clic sur l’un d’entre eux affiche leur contenu, et on peut accéder aux autres journaux à partir du même dialogue :

A partir de cette fenêtre, on voit que l’on peut visualiser plusieurs types de journaux :

– Ceux de l’Agent SQL Server
РCeux du moteur de bases de donn̩e relationnelles
– Ceux de Windows
РCeux de la messagerie de bases de donn̩es

On peut bien sûr mélanger le contenu de plusieurs journaux de types différents, ce qui peut aider à corréler les tenants et aboutissants d’un événement touchant à SQL Server.

Enfin, à l’aide des boutons de la barre d’outils, il est possible de restreindre les dates entre lesquelles on souhaite afficher les messages, et aussi de rechercher dans le contenu de ceux-ci.
Ces deux fonctions sont assez rapides si la machine et SQL Server fonctionnent correctement, mais il en va parfois tout autrement lorsque des problèmes surviennent.
En effet, il m’est arrivé de devoir lire des fichiers de journaux faisant plusieurs centaines de Mo, et il est impossible d’arrêter la Visionneuse de Journaux, à moins de décocher tous les journaux, ce qui n’affiche plus rien. C’est là l’intérêt de gérer ces fichiers.

Voyons donc comment augmenter (ou diminuer) le nombre de journaux d’erreurs.
Dans l’Explorateur d’Objets (F8), après avoir développé le nÅ“ud Gestion, il suffit de faire un clic-droit sur Journaux SQL Server puis de choisir l’option Configurer :

On remarque que l’étiquette portée par les fichiers journaux comporte la date de dernière écriture dans le fichier.
Par défaut la case Limiter le nombre de fichiers de journaux d’erreurs avant qu’ils ne soient recyclés n’est pas cochée.
Dès lors qu’on la coche, on est libre de spécifier le nombre de journaux que l’on souhaite conserver :

Je le mets toujours au maximum, 99, parce que je l’assortis d’un job de l’Agent SQL Server pour recycler les journaux (ce que nous allons voir par la suite).
En scriptant le changement, on obtient le script suivant :

C’est à dire que le nombre de journaux de l’instance SQL Server n’est pas stocké dans une des bases de données système (on aurait pu le stocker dans la base de données master ou msdb).

La procédure stockée sp_cycle_errorlog permet de forcer la création d’un nouveau fichier de journal.
Le cas échéant, elle supprime également le fichier de journal le plus ancien si son numéro incrémenté dépasse le nombre de journaux que l’on a spécifié vouloir conserver.
On peut donc créer un job de l’Agent SQL Server qui s’exécute tous les jours à minuit pour forcer la création d’un nouveau journal d’erreur.
Créons donc ce job, ce que l’on peut faire très simplement par un clic-droit sur le nÅ“ud Agent SQL Server de l’Explorateur d’Objets :

On spécifie le nom du job, et un petit commentaire sur le rôle du job ne fait jamais de mal :

Puis on peut passer à la page Étapes :

EDIT 09/02/2012: il faut mettre le contexte de base de données à msdb, et non pas le laisser sur master

On peut ensuite basculer dans la page Planifications.
Après avoir cliqué sur le bouton Nouvelle, on obtient la fenêtre suivante :

En cliquant sur OK dans ce dialogue et dans le dialogue parent (Propriétés du travail), on valide la création du travail.
On le trouve dès lors dans l’Explorateur d’Objets dans la liste des travaux :

On remarque au passage le nÅ“ud Journaux d’erreurs, enfant du nÅ“ud Agent SQL server : c’est à partir de celui-ci que l’on peut lire les journaux de l’Agent SQL Server :

De la même façon, il est possible de configurer la rétention d’informations :

Comme on le voit, à la différence des options pour les journaux du moteur de bases de données relationnelles, il est ici possible de recycler les journaux de l’Agent SQL Server.

Cette fois, c’est la procédure stockée sp_cycle_agent_errorlog qui permet de forcer le recyclage des journaux de l’Agent SQL Server.
On peut donc tout à fait l’ajouter en seconde étape de notre travail DBA – Cycle Event Logs :

Après avoir cliqué sur Nouveau …, on peut modifier le travail pour la régénération des journaux de l’Agent SQL Server :

Une fois que l’on a cliqué sur OK, puis de la même façon dans le dialogue parent (Propriétés du travail), le travail est maintenant modifié pour renouveler les journaux du moteur de bases de données et de l’Agent SQL Server, tous les jours à minuit.

Il est enfin possible de lire les fichiers journaux de ces deux types (SGBDR et Agent) à l’aide de la procédure stockée étendue système xp_readerrorlog, qui n’est pas documentée.
Comprenez par là que l’utilisation à vos risques et périls, et que rien ne garantit que Microsoft mettra toujours à disposition cette fonctionnalité dans une future version de SQL Server.
Ici on peut se permettre de l’utiliser car cela n’a aucun impact sur le moteur de bases de données : on ne fait que de la lecture de fichiers texte qui sont stockés en dehors des structures de stockage gérées par SQL Server.

Voici donc la document officieuse de cette procédure stockée étendue.
Elle peut prendre quatre paramètres en entrée, qui sont les suivants :

=> le premier indique est un int, et indique l’indice du journal à lire : passez zéro si vous souhaitez lire le journal courant, 1 si vous souhaitez l’archive la plus récente. On peut également de rien passer, et on obtient alors le contenu du fichier de journal du SGBDR courant.
=> le deuxième est un int, et précise le type de journal à lire : passez 1 pour les journaux du SGBDR, et 2 pour l’Agent SQL Server
=> le 3e et le 4e sont des varchar(255), et sont tous deux des chaînes à rechercher dans le contenu des messages.

Vous l’aurez compris, aucun des paramètres n’est obligatoire.
Comme il s’agit d’une procédure stockée étendue, il est impossible d’en obtenir le nom des paramètres, ce qui fait que tout appel se fait par une suite de valeurs.

Si l’on souhaite donc chercher les échecs d’authentification dans le journal courant du SGBDR, on exécutera :

EXEC xp_readerrorlog 0, 1, 'Failed', 'login'

Bonnes investigations des journaux SQL Server !

ElSüket