Chez un de mes clients qui utilise SCOM pour monitorer les jobs SQL en erreur voici ce que l’on peut voir comme message lorsqu’un job lié à la récupération des données avec data collector  (extraction ou updload) part en erreur : « Set_{xxxxxxxxxxxxxxxxx}_Master_package_Upload » Failed. Autant dire qu’avec ce type de message cela reste compliqué de savoir quel collection set, quel job SQL ou quel package SSIS est à l’origine de l’alerte. Voici un script SQL qui permet de déterminer rapidement ces informations.

Avant de poster le script SQL voici une situation concrète :

On voit ici que l’erreur a été déclenchée le 22.07.2012 avec une description d’erreur plus ou moins vague. En cherchant un peu et en connaissant un peu le fonctionnement du data collector on peut reconnaitre dans le message d’erreur un identifiant d’une collection set du data collector. Le script tient compte d’une date d’erreur pour afficher les lignes de log correspondant via la vue système syscollector_execution_log_full. Si aucune date n’est entrée il n’y aura pas de recherche d’erreur dans les logs.

USE msdb;
GO

– Identifier collection set
DECLARE @package_set_uid UNIQUEIDENTIFIER = ‘2DC02BD6-E230-4C05-8516-4E8C0EF21F95′;
– Date of the raise of the error
DECLARE @date_begin_log DATETIME = ‘20120722 15:30:02′;
 
– Check configuration of collection set
SELECT ‘Collection set configuration';

SELECT
cs.collection_set_uid,
cs.name AS collection_set_name,
cs.[description],
cs.is_system,
cs.is_running,
cs.logging_level,
cs.days_until_expiration,
ci.name AS collection_item_name,
ci.frequency,
t.name AS [type_name],
t.is_system,
jc.name AS job_collect_name,
pc.name AS package_collect_name,
CAST(CAST(pc.packagedata AS VARBINARY(MAX)) AS XML) AS package_collect_data,
t.collection_package_name,
ju.name as job_upload_name,
pu.name as package_upload_name,
CAST(CAST(pu.packagedata AS VARBINARY(MAX)) AS XML) AS package_upload_data
FROM dbo.syscollector_collection_sets AS cs
INNER JOIN dbo.syscollector_collection_items AS ci
  ON ci.collection_set_id = cs.collection_set_id
INNER JOIN dbo.syscollector_collector_types AS t
  ON t.collector_type_uid = ci.collector_type_uid
INNER JOIN dbo.sysjobs AS jc
  ON jc.job_id = cs.collection_job_id
INNER JOIN dbo.sysjobs as ju
  ON ju.job_id = cs.upload_job_id
INNER JOIN dbo.sysssispackages AS pc
  ON pc.id = t.collection_package_id
INNER JOIN dbo.sysssispackages AS pu
  ON pu.id = t.upload_package_id
WHERE cs.collection_set_uid = @package_set_uid;

– SQL Agent jobs concerned by the collection set
SELECT ‘SQL Agent Jobs';

SELECT
sci.name,
sci.is_running,
sci.[description] AS collection_set_desc,
j.name AS collect_job,
j2.name AS upload_job
FROM dbo.syscollector_collection_sets_internal sci
LEFT JOIN dbo.sysjobs AS j
  ON j.job_id = sci.collection_job_id
LEFT JOIN dbo.sysjobs AS j2
  ON j2.job_id = sci.upload_job_id
WHERE sci.is_running = 1
AND sci.collection_set_uid = @package_set_uid;
 
– Find error log for the collection set if wanted
IF @date_begin_log IS NOT NULL
BEGIN
SELECT ‘Error log';

SELECT
sci.name AS collection_set,
sci.[description] AS collection_set_desc,
lg.log_id,
lg.parent_log_id,
lg.name AS log_name,
lg.runtime_execution_mode,
lg.start_time,
lg.finish_time,
lg.last_iteration_time,
lg.duration,
lg.failure_message,
lg.package_execution_id
FROM dbo.syscollector_execution_log_full AS lg
INNER JOIN dbo.syscollector_collection_sets_internal sci
  ON sci.collection_set_id = lg.collection_set_id
WHERE lg.[status] = 2 — Erreur
AND sci.collection_set_uid = @package_set_uid
  AND lg.start_time > DATEADD(hh, -1, @date_begin_log)
ORDER BY start_time
END

Le résultat est le suivant :

…

On y voit un peu plus clair déjà. La collection set concernée est Query Statistics. Le script remonte également les jobs SQL concernés par cette collection set. Dans notre cas il y a bien 2 jobs SQL puisque la collection set en mode cache. Enfin on peut voir dans la dernière partie l’erreur provoquée. Dans mon cas c’est une erreur de timeout d’exécution du package de collecte des données (par défaut le timeout est fixé à 3600s soit 1H).

David BARBARIN (Mikedavem)
MVP SQL Server