[SQL Server] Monotorer les jobs SQL Server en cours d'exécution
-- =============================================
-- Author: KHARROUBI Zouhaier
-- Create date: 08/11/2020
-- Description: Monotorer les jobs SQL Server sur les différentes instances SQL Server
-- =============================================
-- Add the parameters for the stored procedure here
DECLARE @Liste_Serveurs NVARCHAR(MAX)=NULL
,@current_execution_status TINYINT=3
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Serveurs_LiesTemp]'))
BEGIN
DROP TABLE #Serveurs_LiesTemp
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Serveurs_Lies]'))
BEGIN
DROP TABLE #Serveurs_Lies
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Resultat]'))
BEGIN
DROP TABLE #Resultat
END
CREATE TABLE #Serveurs_LiesTemp
(
SRV_NAME VARCHAR(150),
SRV_PROVIDERNAME VARCHAR(150),
SRV_PRODUCT VARCHAR(150),
SRV_DATASOURCE VARCHAR(150),
SRV_PROVIDERSTRING VARCHAR(150),
SRV_LOCATION VARCHAR(150),
SRV_CAT VARCHAR(150)
)
CREATE TABLE #Serveurs_Lies
(
Num_Lig INT IDENTITY (1,1),
SRV_NAME VARCHAR(150),
SRV_DATASOURCE VARCHAR(150),
ServerName VARCHAR(150) NULL
)
CREATE NONCLUSTERED INDEX [IDX_#Serveurs_Lies_ServerName] ON #Serveurs_Lies
(
ServerName ASC
)
CREATE TABLE #Resultat
(
Num_Lig INT IDENTITY (1,1)
,job_id uniqueidentifier
,originating_server nvarchar(30) NULL
,Environnement varchar(100) NULL
,[name] sysname NULL
,[enabled] tinyint NULL
,[description] nvarchar(512) NULL
,start_step_id int NULL
,category sysname NULL
,[owner] sysname NULL
,notify_level_eventlog int NULL
,notify_level_email int NULL
,notify_level_netsend int NULL
,notify_level_page int NULL
,notify_email_operator sysname NULL
,notify_netsend_operator sysname NULL
,notify_page_operator sysname NULL
,delete_level int NULL
,date_created datetime NULL
,date_modified datetime NULL
,version_number int NULL
,last_run_date int NULL
,last_run_time int NULL
,last_run_outcome int NULL
,next_run_date int NULL
,next_run_time int NULL
,next_run_schedule_id int NULL
,current_execution_status int NULL
,current_execution_step sysname NULL
,current_retry_attempt int NULL
,has_step int NULL
,has_schedule int NULL
,has_target int NULL
,[type] int NULL
)
DECLARE @Requete NVARCHAR(MAX)
,@Nbre_Lig INT
,@Num_Lig INT
,@Serveur_Lie VARCHAR(150)
,@ServerName VARCHAR(150)
,@LocalHot VARCHAR(50)
,@Environnement VARCHAR(100)
,@VersionSQLServer NVARCHAR(350)
SELECT @LocalHot = QUOTENAME(@@SERVERNAME)
IF ISNULL(@Liste_Serveurs,'')=''
BEGIN
INSERT INTO #Serveurs_LiesTemp
EXEC sp_linkedservers
INSERT INTO #Serveurs_Lies (SRV_NAME,SRV_DATASOURCE)
SELECT DISTINCT SRV_NAME,SRV_DATASOURCE FROM #Serveurs_LiesTemp
END
ELSE
BEGIN
INSERT INTO #Serveurs_Lies (SRV_NAME,SRV_DATASOURCE)
SELECT Valeur,Valeur FROM [dbo].[ufn_SplitString] (@Liste_Serveurs,',')
END
SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Num_Lig) FROM #Serveurs_Lies
WHILE @Num_Lig <= @Nbre_Lig
BEGIN
BEGIN TRY
SELECT @Serveur_Lie =SRV_DATASOURCE,@ServerName=NULL,@Environnement=NULL
FROM #Serveurs_Lies WHERE Num_Lig=@Num_Lig
--Récupérer le nom réel de l'instance SQL Server
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT @ServerName_Out=ServerName,@VersionSQLServer_Out=VersionSQLServer FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + N',''SELECT @@SERVERNAME AS ServerName,@@VERSION AS VersionSQLServer'')' ELSE 'SELECT @ServerName_Out=@@SERVERNAME,@VersionSQLServer_Out=@@VERSION' END
EXECUTE sp_executesql @Requete,N'@ServerName_Out VARCHAR(150) OUTPUT,@VersionSQLServer_Out AS NVARCHAR(350) OUTPUT',@ServerName_Out=@ServerName OUTPUT,@VersionSQLServer_Out=@VersionSQLServer OUTPUT
IF NOT EXISTS (SELECT ServerName FROM #Serveurs_Lies WHERE ISNULL(ServerName,'')=@ServerName)
BEGIN
UPDATE #Serveurs_Lies
SET
ServerName=@ServerName
WHERE Num_Lig=@Num_Lig
--Récupérer l'environnement de l'instance SQL Server : DEVELOPPEMENT, HOMOLOGATION, PRODUCTION
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT @Environnement_Out = Environnement FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie)
+ N',''SELECT Environnement = CASE WHEN Command LIKE ''''%BACKUP_DEVELOPPEMENT%'''' THEN ''''DEVELOPPEMENT''''
WHEN Command LIKE ''''%BACKUP_HOMOLOGATION%'''' THEN ''''HOMOLOGATION''''
WHEN Command LIKE ''''%BACKUP_PRODUCTION%'''' THEN ''''PRODUCTION''''
ELSE ''''INCONNU''''
END
FROM (
SELECT TOP 1 Command
FROM [msdb].[dbo].[sysjobsteps]
WHERE command LIKE ''''%COMP_LOG_BACKUP%'''' OR command LIKE ''''%COMP_DIFF_BACKUP%''''
OR command LIKE ''''%COMP_FULL_BACKUP%''''
) T1'')'
ELSE
N'SELECT @Environnement_Out = CASE WHEN Command LIKE ''%BACKUP_DEVELOPPEMENT%'' THEN ''DEVELOPPEMENT''
WHEN Command LIKE ''%BACKUP_HOMOLOGATION%'' THEN ''HOMOLOGATION''
WHEN Command LIKE ''%BACKUP_PRODUCTION%'' THEN ''PRODUCTION''
ELSE ''INCONNU''
END
FROM (
SELECT TOP 1 Command
FROM [msdb].[dbo].[sysjobsteps]
WHERE command LIKE ''%COMP_LOG_BACKUP%'' OR command LIKE ''%COMP_DIFF_BACKUP%''
OR command LIKE ''%COMP_FULL_BACKUP%''
) T1'
END
EXECUTE sp_executesql @Requete,N'@Environnement_Out VARCHAR(150) OUTPUT',@Environnement_Out=@Environnement OUTPUT
--Récupérer les informations des jobs
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN 'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END
+ N'EXEC msdb.dbo.sp_help_job'
+ CASE WHEN @VersionSQLServer LIKE '%SQL Server 2008%' OR @VersionSQLServer LIKE '%SQL Server 2005%' OR @VersionSQLServer LIKE '%SQL Server 2000%' THEN ''
ELSE ' WITH RESULT SETS
(
(
job_id UNIQUEIDENTIFIER,
originating_server NVARCHAR(30),
name SYSNAME,
[enabled] TINYINT,
[description] NVARCHAR(512),
start_step_id INT,
category SYSNAME,
[owner] SYSNAME,
notify_level_eventlog INT,
notify_level_email INT,
notify_level_netsend INT,
notify_level_page INT,
notify_email_operator SYSNAME,
notify_netsend_operator SYSNAME,
notify_page_operator SYSNAME,
delete_level INT,
date_created DATETIME,
date_modified DATETIME,
version_number INT,
last_run_date INT,
last_run_time INT,
last_run_outcome INT,
next_run_date INT,
next_run_time INT,
next_run_schedule_id INT,
current_execution_status INT,
current_execution_step SYSNAME,
current_retry_attempt INT,
has_step INT,
has_schedule INT,
has_target INT,
[type] INT
)
)' END
+ CASE WHEN @LocalHot<>@Serveur_Lie THEN ''')' ELSE '' END
INSERT INTO #Resultat ([job_id],[originating_server],[name],[enabled],[description],[start_step_id],[category],[owner]
,[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator]
,[notify_netsend_operator],[notify_page_operator],[delete_level],[date_created],[date_modified],[version_number]
,[last_run_date],[last_run_time],[last_run_outcome],[next_run_date],[next_run_time],[next_run_schedule_id]
,[current_execution_status],[current_execution_step],[current_retry_attempt],[has_step],[has_schedule],[has_target],[type])
EXECUTE sp_executesql @Requete
UPDATE #Resultat
SET
Environnement=@Environnement
WHERE originating_server=@ServerName
END
END TRY
BEGIN CATCH
SELECT @Serveur_Lie AS Serveur_Lie, ERROR_MESSAGE() AS Message_Erreur,@Requete AS Requete
END CATCH
SELECT @Num_Lig = @Num_Lig + 1
END
SELECT job_id, originating_server AS ServeurOrigine,Environnement,[name] AS Nom
, Active = CASE [enabled] WHEN 1 THEN 'OUI' ELSE 'NON' END
, Etat= CASE current_execution_status WHEN 1 THEN 'Exécution : '
WHEN 2 THEN 'Attente de Thread : '
WHEN 3 THEN 'Entre les Tentatives : '
WHEN 4 THEN 'Inactif'
WHEN 5 THEN 'Suspendu'
WHEN 6 THEN 'Obsolète'
WHEN 7 THEN 'PerformingCompletionActions'
ELSE 'Inconnu' END
+ CASE WHEn current_execution_status IN (1,2,3) THEN current_execution_step ELSE '' END
,ResultatDerniereExecution= CASE last_run_outcome WHEN 0 THEN 'Echec'
WHEN 1 THEN 'Réussite'
WHEN 3 THEN 'Annulé'
WHEN 5 THEN 'Inconnu'
ELSE 'Pas d''information'
END
,DerniereExecution= CASE WHEN ISDATE(last_run_date)=1 THEN CONVERT(VARCHAR(19),CONVERT(datetime,STUFF(STUFF(CAST(last_run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' + STUFF(STUFF(SUBSTRING(CAST(1000000 + last_run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120),121) ELSE 'Non Planifié' END
,ProchaineExecution = CASE WHEN ISDATE(next_run_date)=1 THEN CONVERT(VARCHAR(19),CONVERT(datetime,STUFF(STUFF(CAST(next_run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' + STUFF(STUFF(SUBSTRING(CAST(1000000 + next_run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120),121) ELSE 'Non Planifié' END
,MinutesRestantesProchaineExecution=NULL
,JobErreur=SPACE(60)
,category AS Categorie
--,[Executable]
,Planifie= CASE has_schedule WHEN 1 tHEN 'OUI' ELSE 'NON' END
,NombreEtapes=has_step
,NombrePlanification = has_schedule
,NombreServeursCibles = has_target
,TypeJob= CASE [type] WHEN 0 THEN 'Pas de serveurs cibles'
WHEN 1 THEN 'Job Local'
WHEN 2 THEN 'Job Multi Serveurs'
END
INTO #ResultatFinal
FROM #Resultat
WHERE current_execution_status <= @current_execution_status
ORDER BY Environnement,originating_server,[name]
UPDATE #ResultatFinal
SET
MinutesRestantesProchaineExecution = DATEDIFF(minute,GETDATE(),ProchaineExecution)
WHERE ISDATE(ProchaineExecution)=1
UPDATE #ResultatFinal
SET
JobErreur = '!!! ATTENTION LE JOB TOURNE DEPUIS PLUSIEURS MINUTES !!!'
WHERE ISNULL(MinutesRestantesProchaineExecution,0)<0
SELECT job_id, ServeurOrigine,Environnement,Nom
, Active
, Etat
,DerniereExecution
,ProchaineExecution
,MinutesRestantesProchaineExecution
,JobErreur
,Categorie
--,[Executable]
,Planifie
,NombreEtapes
,NombrePlanification
,NombreServeursCibles
,TypeJob
FROM #ResultatFinal
ORDER BY Environnement,ServeurOrigine,Nom
DROP TABLE #Serveurs_LiesTemp
DROP TABLE #Serveurs_Lies
DROP TABLE #Resultat
DROP TABLE #ResultatFinal
END