SQL Server fournit de nombreux outils de monitoring d’arrière-plan par défaut, comme le journal SQL Server, la session d’événements étendus system_health, mais aussi une trace SQL Profiler. Cette dernière est enregistrée dans un fichier dans le répertoire des journaux, et SQL Server conserve 6 fichiers de cette trace.

Elle capture, entre autres, les événements d’échec d’authentification, la création d’objets dans TempDB, les warnings sur Hash et Sort, les grossissements de fichiers, et de nombreux autres événements. Ceux-ci peuvent aider à comprendre, a posteriori, ce qui a causé une grande consommation de CPU, des latences disque, …

Comme cette trace est écrite sur disque, il peut malencontreusement arriver que ce dernier devienne plein, provoquant alors l’arrêt automatique et forcé de la trace. Ce léger incident (l’arrêt de la trace, pas l’absence d’espace disque libre !) n’empêche aucunement l’instance de fonctionner, ou au moins d’être accessible. Cependant, comment peut-on redémarrer cette trace sans redémarrer l’instance SQL Server ?

En effet, la documentation nous indique que la trace par défaut n’est démarrée que lorsque l’instance démarre aussi … nous allons voir que c’est incomplet. Une fois le disque supportant la trace disposant à nouveau d’espace disque libre, on constate que la trace par défaut est arrêtée à l’aide de l’instruction suivante :

Lorsque la trace par défaut est active, cette requête retourne une ligne, dont la colonne id est à 1. On peut vérifier que c’est la trace par défaut à l’aide de la valeur de la colonne is_default : 1 si c’est le cas, 0 à l’inverse :

Dans la situation de l’incident que nous évoquons, cette même requête ne retournera pas de ligne (ou tout du moins aucune dont la colonne is_default est à 1). Pour démarrer la trace par défaut, il suffit de s’en remettre à l’option de configuration d’instance default trace enabled :

On voit que c’est une option avancée et dynamique, ce qui signifie qu’un redémarrage de l’instance SQL Server n’est pas nécessaire. Dès lors, pour réactiver la trace par défaut, il suffit de positionner cette option à 0, puis immédiatement ensuite à 1, ce qui est sa valeur par défaut :

Sur exécution de ce lot, l’onglet Messages du panneau de résultats de SQL Server Management Studio affiche :

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘default trace enabled’ changed from 1 to 0. Run the RECONFIGURE statement to install.
Configuration option ‘default trace enabled’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install.

Et l’on voit alors que la trace a été démarrée, en interrogeant une nouvelle fois la vue sys.traces.
On trouvera aussi dans les journal de SQL Server les lignes suivantes :

Ici on voit bien que la trace par défaut a été démarré par le système (le SPID est inférieur à 50), quelques millisecondes après le démarrage de celui-ci. Elle a ensuite été redémarrée par nos soins un peu plus tard.

Petite note finale : les fonctionnalités SQL Trace et SQL Profiler sont obsolètes, et remplacées par la fonctionnalité d'événements étendus, plus puissante et performante. Vous pouvez voir comment les utiliser dans cette vidéo, en gardant bien à l’esprit que dès SQL Server 2012, une interface graphique intégrée à SQL Server Management Studio permet de créer une session sans écrire de code, et de visualiser les événements capturés à la façon de ce que permet SQL Profiler.

Enfin, pour interroger un fichier de trace SQL Profiler, il suffit d’utiliser la fonction sys.fn_trace_gettable(). Donc pour en faire l’analyse, on peut utiliser le lot suivant :

Alors bon traçage à tous !