Datafly.pro

Les messages de backup : traceflag 3226

Une première cause de "pollution" de l'errorlog est le message de succès qui est émis pour chaque sauvegarde. Imaginez que vous sauvegardiez le journal de vos 100 bases de données toutes les 2 minutes : nous aurions alors 3000 messages de sauvegarde chaque heure !

Pour ce premier problème, depuis SQL 2008 il existe le traceflag 3226.
Une fois positionné (à configurer dans le gestionnaire de configuration SQL Server, ajouter -T 3226 aux paramètres démarrage), ces messages nous laisseront tranquilles.

Autre cas : filtrer et stocker en table

Mais il existe d'autre cas :  il m'est arrivé de trouver des serveurs avec un très grand nombre de bases (plus de 10000), toutes en auto-close. Sisi ça existe, chez certains éditeurs de progiciels dont je tairai le nom et qui confondent le SGBD avec un serveur de fichiers !
Dans ce cas, l'errorlog devient totalement inexploitable : dès qu'une base est utilisée et qu'un utilisateur si connecte, un message "Starting up database ..." est produit, ce qui peut rapidement remplir l'errorlog de dizaine de milliers de messages ! Allez donc y trouver quelque chose...

Dans ce cas, une autre stratégie s'impose avec un job de l'agent SQL Server qui fera régulièrement les tâches suivantes :

• Recycler l'errorlog pour éviter qu'il ne devienne trop volumineux
• Charger l'errorlog dans une table et le filtrer (à vous de personnaliser le filtre !)
• Purger la table pour conserver une rétention raisonnable

-- Etape 1 : recycler l'errorlog
EXEC sp_cycle_errorlog

-- Etape 2 : charger dans une table SQLErrorlog (table à créer dans msdb)
IF OBJECT_ID('tempdb..#ErrorLogParsing') IS NOT NULL
    DROP TABLE #ErrorLogParsing;
CREATE TABLE #ErrorLogParsing
    (
      LogDate DATETIME
    , ProcessInfo NVARCHAR(50)
    , Log_Entry VARCHAR(2000)
    )
INSERT  #ErrorLogParsing
        ( LogDate, ProcessInfo, Log_Entry )
        EXEC master.sys.xp_readerrorlog 1, 1;
DELETE  FROM #ErrorLogParsing
WHERE   Log_Entry LIKE 'Starting up database%'
INSERT  msdb..SQLErrorLog
        SELECT  *
        FROM    #ErrorLogParsing ORDER BY LogDate;

-- Etape 3 : Purger avec une durée de rétention de 3 mois
DELETE FROM msdb..SQLErrorLog WHERE LogDate < DATEADD(MONTH,-3,GETDATE())

Utilisation de xp_readerrorlog

Vous avez pu le voir juste au-dessus, xp_readerrorlog est une commande bien utile.

Syntaxe : EXEC sys.xp_readerrorlog 0, 1, 'search string', 'additional search string (facultatif)'

• Le premier paramètre est le numéro du fichier : 0 pour le fichier courant
• Le second paramètre est soit 1 pour l'errorlog de SQL Server, soit 2 pour l'errorlog de l'agent SQL
• Les 3ème et 4ème paramètre sont des chaines de recherche : pour n'extraire que les messages contenant ces chaines.

Ce qui est intéressant c'est que cette commande xp_readerrorlog peut nous permettre d'extraire très facilement des informations du journal d'erreurs, par exemple :

• Obtenir le port TCP d'écoute de l'instance : xp_readerrorlog 0, 1, N'Server is listening on', N'any'
• Obtenir le modèle et le fabricant de l'ordinateur : xp_readerrorlog 0, 1, N'Manufacturer'
• Rechercher les avertissements d'IO longues : xp_readerrorlog 0, 1, N'taking longer than 15 seconds'