USE [HCITools] GO /****** Object: StoredProcedure [dbo].[Replication_Check] Script Date: 16.04.2024 07:40:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Replication_Check] @in_debug int = null, @in_CheckType smallint = 0, @in_Email smallint AS /*============================================================================= Explication du traitement realise par la SP ------------------------------------------- Cette SP sert à vérifier un ensemble de points critiques sur la réplication Contexte d'utilisation ---------------------- Cette SP est appelée par le job DR92160 - Replication Check Parametres ---------- @in_debug : non utilisé @in_CheckType : 0 = Toutes les vérifications 1 = Check des jobs de maintenance de la réplication 2 = Check des articles présents dans la publication 3 = Check des linked servers 4 = Liste des postes non abonnées (manquants) 5 = Check de la disponibilité du share 6 = Check du mode d'authentification des souscriptions 7 = Check de l'etat des souscriptions pour chaque poste 8 = Check All Agents Status 9 = Check jobs de réplication à double 10 = Vérifications des commandes non distribuées @in_Email : 1 = Envoie d'email à HCI_DBA 2 = Pas d'email, uniquement enregisté dans la base de données Creation : 28.08.17 / FLA Modification : 12.07.17 / FLA Ajout de la gestion des environnements pour executer que sur N+1 et PROD 24.04.18 / RTC Ajout du test des commandes non distribuées et mise à jour du schedule 21.11.18 / SPE Ajout d'une vérification d'état des linked server des postes (job replication stoppé depuis plus de 2 minutes) 20.04.20 / SPE Create alert for monitoring replication after MSI deployment 19.05.20 / SPE Envoi de mail seulement si erreur critique 04.09.20 / RTC Adapt sp after shared file config changes 29.10.20 / FLA Manage agent error with more than one replication / manage others errors 23.09.20 / RTC TFS : 62550 Update version check to avoid wrong error message. 26.02.21 / SPE TFS : 63953 Adapt stored procedure [dbo].[CheckActivePosPublication] to new settings 01.04.21 / SPE TFS : 60931 Remove undist commands fix after 2014 04.05.21 / RTC TFS : 64808 Update check D91030 - Backup ActivePos_Read 04.05.21 / RTC TFS : 65443 Update check replication performance 17.03.22 / FLA : Change DBA mail 24.11.22 / TSC : OCTPDBA-412 Replace ArizonaCash.dbo.pr_point_of_sales with arizona.dbo.point_of_sale 27.01.23 / SPE : OCTPDBA-478: Resolve SQL 2019 compatibility issue when getting SQL replication subscription info 03.02.23 / RTC : OCTPDBA-485: Remove check for windows authentication in the replication monitoring 03.06.29 / RTC : OCTPDBA-666: Update replication jobs check 16.04.24 / TSC : TPDT-456 Remove job "'D91030 - Backup ActivePos_Read" from the checks for job present or disabled =============================================================================*/ SET NOCOUNT ON; /*------------------- Declaration des variables --------------------*/ DECLARE @result_sp int, @CheckJob int, @Status int, @Rate int, @AlertID int, @errfound bit, @isProd bit, @countok tinyint, @countko tinyint, @counttot tinyint, @percent tinyint, @LksrvPos sysname, @CheckDate datetime2(0), @tsql nvarchar(max), @Message varchar(max), @query varchar(max), @fileName varchar(400), @errmsg varchar(255), @JobName varchar(100), @server varchar(100), @Article varchar(50), @POS varchar(50), @dbVersion varchar(20), @versionNumberSrv varchar(17), @versionNumberPos varchar(17), @ouCode varchar(15), @distribution_db sysname, @publication sysname, @publisher_db sysname, @subscriber sysname, @Agent_type sysname, @Agent_id int, @EndTime datetime2(0), @comments varchar(250), @TableSys sysname, @AlertKey varchar(20) /*------------ Affectation des parametres aux variables ------------*/ SET @message = '' SET @result_sp = 0 SET @errfound = 0 SET @CheckDate = getdate() SELECT @counttot = count(*) FROM [Arizona].[dbo].[Bmc_application_key] [bak] WITH (NOLOCK) JOIN [Arizona].[dbo].[Bmc_application_default] [bad] WITH (NOLOCK) ON [bad].[BAPD_bmc_application_key] = [bak].[Bmc_application_key_ID] JOIN [Arizona].[dbo].[Organizational_unit] OUA WITH (NOLOCK) ON OUA.[Organizational_unit_ID] = [bad].[BAPD_value] JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK) ON [ou].[OU_code] = [OUA].[OU_code] JOIN [Arizona].[dbo].[Point_of_sale] [pos] ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit] WHERE [bak].[BAPK_key] = 'cvCurrentOrganizationalUnit' AND [pos].[POS_active] = 1 AND [pos].[POS_number] <= 39 AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */ --AND OU.CROU_locally_used = 1 /*-------------------------- Traitement ---------------------------*/ IF @in_Email NOT IN (0, 1) BEGIN SET @errmsg = 'Probleme avec le paramètre @in_Email'; RAISERROR (@errmsg,16,1); END /* Check replications only if POS are registred on this pharmacy */ IF @counttot > 0 BEGIN /* Exclude all N+2 */ IF EXISTS (SELECT * FROM [master].[cfg].[InstanceContext] WHERE Type in ('VALI','ACCE','PROD') AND Business in ('TPCENT','TPUCENT','TPPHAR') AND Behavior = 'Enable') BEGIN /* Alerts are critical or warning only if PROD pharmacie */ IF EXISTS (SELECT 1 FROM [master].[cfg].[InstanceContext] WHERE Type = 'PROD') BEGIN SET @isProd = 1 END ELSE BEGIN SET @isProd = 0 END CREATE TABLE #LinkedServerPb(posname varchar(255)) /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ /* 1: Check des jobs de maintenance de la réplication */ /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ BEGIN TRY IF (@in_CheckType = 1 or @in_CheckType = 0) BEGIN SET @message = '' SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'Agent history clean up: distribution' AND enabled = 1 IF @CheckJob <> 1 SET @message = @message + '- Agent history clean up: distribution'+CHAR(13)+CHAR(10) SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'Distribution clean up: distribution' AND enabled = 1 IF @CheckJob <> 1 SET @message = @message + '- Distribution clean up: distribution'+CHAR(13)+CHAR(10) SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'Expired subscription clean up' AND enabled = 1 IF @CheckJob <> 1 SET @message = @message + '- Expired subscription clean up'+CHAR(13)+CHAR(10) SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'Reinitialize subscriptions having data validation failures' AND enabled = 1 IF @CheckJob <> 1 SET @message = @message + '- Reinitialize subscriptions having data validation failures'+CHAR(13)+CHAR(10) SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'Replication agents checkup' AND enabled = 1 IF @CheckJob <> 1 SET @message = @message + '- Replication agents checkup'+CHAR(13)+CHAR(10) --SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'D91030 - Backup ActivePos_Read' --IF @CheckJob <> 1 -- SET @message = @message + '- D91030 - Backup ActivePos_Read'+CHAR(13)+CHAR(10) SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'D00480 - ActivePos_read Snapshot' AND enabled = 1 IF @CheckJob <> 1 SET @message = @message + '- D00480 - ActivePos_read Snapshot'+CHAR(13)+CHAR(10) SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'DR00470 - ActivePos_read Log reader' AND enabled = 1 IF @CheckJob <> 1 SET @message = @message + '- DR00470 - ActivePos_read Log reader'+CHAR(13)+CHAR(10) IF @message <> '' BEGIN SET @errfound = 1 SET @message = 'Jobs de maintenance de la réplication manquants ou désactivés : '+CHAR(13)+CHAR(10)+@message SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'MaintJobs' IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 1, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END END END TRY BEGIN CATCH /* Traitement des erreurs (avec RaiseError) */ EXEC dbo.get_Error_Info @in_RaiseError = 1 END CATCH /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ /* 2: Check des articles présents dans la publication */ /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ BEGIN TRY IF (@in_CheckType = 2 or @in_CheckType = 0) BEGIN SET @message = '' DECLARE c_Articles CURSOR LOCAL FORWARD_ONLY STATIC FOR SELECT NAME FROM ActivePos_read.sys.objects AROT WITH (NOLOCK) WHERE type = 'U' AND is_ms_shipped = 0 AND NOT EXISTS (SELECT name FROM ActivePos_read.sys.objects AROA WITH (NOLOCK) WHERE type = 'U' AND is_published = 1 AND is_ms_shipped = 0 AND AROT.name = AROA.name) OPEN c_Articles FETCH NEXT FROM c_Articles INTO @Article WHILE @@fetch_status <> -1 BEGIN SET @message = @message + '- '+@Article+CHAR(13)+CHAR(10) FETCH NEXT FROM c_Articles INTO @Article END IF @message <> '' BEGIN SET @errfound = 1 SET @message = 'Liste des articles manquants dans la publication :'+CHAR(13)+CHAR(10)+@message SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'MissArt' IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 2, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END CLOSE c_Articles DEALLOCATE c_Articles END END TRY BEGIN CATCH /* Traitement des erreurs (avec RaiseError) */ EXEC dbo.get_Error_Info @in_RaiseError = 1 END CATCH /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ /* 3: Check des linked servers */ /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ BEGIN TRY IF (@in_CheckType = 3 or @in_CheckType = 0) BEGIN SET @message = '' SET @countok = 0 SET @countko = 0 SET @percent = 0 IF EXISTS ( SELECT * FROM distribution.sys.tables WHERE name = 'msreplservers') BEGIN EXEC ('SELECT POS.[POS_hostname] INTO ##POS FROM arizona.dbo.bmc_application_key WITH (NOLOCK) JOIN arizona.dbo.bmc_application_default WITH (NOLOCK) ON BAPD_bmc_application_key = Bmc_application_key_ID JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK) ON [OUA].[Organizational_unit_ID] = bapd_value JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK) ON [ou].[OU_code] = [OUA].[OU_code] JOIN [Arizona].[dbo].[Point_of_sale] [pos] ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit] WHERE BAPK_key = ''cvCurrentOrganizationalUnit'' AND [pos].[POS_active] = 1 AND [pos].[POS_number] <= 39 AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */ --AND OU.CROU_locally_used = 1 AND EXISTS (select distinct [ss].[srvname] FROM distribution.dbo.MSreplservers ss WITH (NOLOCK) INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK) ON [ss].[srvid] = [ms].[subscriber_id] WHERE [ss].[srvname] = POS.[POS_hostname])'); END; ELSE BEGIN EXEC ('SELECT POS.[POS_hostname] INTO ##POS FROM arizona.dbo.bmc_application_key WITH (NOLOCK) JOIN arizona.dbo.bmc_application_default WITH (NOLOCK) ON BAPD_bmc_application_key = Bmc_application_key_ID JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK) ON [OUA].[Organizational_unit_ID] = bapd_value JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK) ON [ou].[OU_code] = [OUA].[OU_code] JOIN [Arizona].[dbo].[Point_of_sale] [pos] ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit] WHERE BAPK_key = ''cvCurrentOrganizationalUnit'' AND [pos].[POS_active] = 1 AND [pos].[POS_number] <= 39 AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */ --AND OU.CROU_locally_used = 1 AND EXISTS (select distinct [ss].[name] FROM sys.servers ss WITH (NOLOCK) INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK) ON [ss].[server_id] = [ms].[subscriber_id] WHERE [ss].[name] = POS.[POS_hostname])'); END; DECLARE c_Subscriptions CURSOR LOCAL FORWARD_ONLY STATIC FOR SELECT * FROM ##POS SELECT TOP 1 @versionNumberSrv = [VersionNumber] FROM [ActivePos_read].[upd].[DatabaseHistory] ORDER BY [DatabaseHistoryId] DESC OPEN c_Subscriptions FETCH NEXT FROM c_Subscriptions INTO @POS WHILE @@fetch_status <> -1 BEGIN SET @LksrvPos = @POS; BEGIN TRY EXEC @result_sp = sys.sp_testlinkedserver @LksrvPos END TRY BEGIN CATCH SET @result_sp = sign(@@error); END CATCH; IF @result_sp <> 0 BEGIN SET @countko = @countko + 1 SET @message = @message + '- '+@POS+ ' - Timeout' +CHAR(13)+CHAR(10) INSERT INTO #LinkedServerPb(posname) VALUES(@POS) END ELSE BEGIN SET @countok = @countok + 1 END FETCH NEXT FROM c_Subscriptions INTO @POS END IF @message <> '' BEGIN SET @errfound = 1 SET @message = 'Liste des linked servers avec des problèmes de connexion:'+CHAR(13)+CHAR(10)+@message SET @countok = @counttot - @countko SELECT @percent = (@countko * 100 / (@counttot)) SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'POSLinked' IF @percent <= 29 BEGIN IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 1, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END END IF @percent >= 30 BEGIN IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 2, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END END /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN IF @percent >= 30 /* Envoi de mail seulement si erreur critique */ BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END END CLOSE c_Subscriptions DEALLOCATE c_Subscriptions DROP TABLE ##POS; END END TRY BEGIN CATCH /* Traitement des erreurs (avec RaiseError) */ EXEC dbo.get_Error_Info @in_RaiseError = 1 END CATCH /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ /* 4: Liste des postes non abonnées (manquants) */ /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ BEGIN TRY IF (@in_CheckType = 4 or @in_CheckType = 0) BEGIN SET @message = '' SET @countok = 0 SET @countko = 0 IF EXISTS ( SELECT * FROM distribution.sys.tables WHERE name = 'msreplservers') BEGIN EXEC ('SELECT POS.[POS_hostname] INTO ##POS FROM arizona.dbo.bmc_application_key WITH (NOLOCK) JOIN arizona.dbo.bmc_application_default WITH (NOLOCK) ON BAPD_bmc_application_key = Bmc_application_key_ID JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK) ON [OUA].[Organizational_unit_ID] = bapd_value JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK) ON [ou].[OU_code] = [OUA].[OU_code] JOIN [Arizona].[dbo].[Point_of_sale] [pos] ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit] WHERE BAPK_key = ''cvCurrentOrganizationalUnit'' AND [pos].[POS_active] = 1 AND [pos].[POS_number] <= 39 AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */ --AND OU.CROU_locally_used = 1 AND NOT EXISTS (select distinct [ss].[srvname] FROM distribution.dbo.MSreplservers ss WITH (NOLOCK) INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK) ON [ss].[srvid] = [ms].[subscriber_id] WHERE [ss].[srvname] = POS.[POS_hostname])'); END; ELSE BEGIN EXEC ('SELECT POS.[POS_hostname] INTO ##POS FROM arizona.dbo.bmc_application_key WITH (NOLOCK) JOIN arizona.dbo.bmc_application_default WITH (NOLOCK) ON BAPD_bmc_application_key = Bmc_application_key_ID JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK) ON [OUA].[Organizational_unit_ID] = bapd_value JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK) ON [ou].[OU_code] = [OUA].[OU_code] JOIN [Arizona].[dbo].[Point_of_sale] [pos] ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit] WHERE BAPK_key = ''cvCurrentOrganizationalUnit'' AND [pos].[POS_active] = 1 AND [pos].[POS_number] <= 39 AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */ --AND OU.CROU_locally_used = 1 AND NOT EXISTS (select distinct [ss].[name] FROM sys.servers ss WITH (NOLOCK) INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK) ON [ss].[server_id] = [ms].[subscriber_id] WHERE [ss].[name] = POS.[POS_hostname])'); END; DECLARE c_Subscriptions CURSOR LOCAL FORWARD_ONLY STATIC FOR SELECT * FROM ##POS SELECT TOP 1 @versionNumberSrv = [VersionNumber] FROM [ActivePos_write].[upd].[DatabaseHistory] ORDER BY [DatabaseHistoryId] DESC OPEN c_Subscriptions FETCH NEXT FROM c_Subscriptions INTO @POS WHILE @@fetch_status <> -1 BEGIN SET @LksrvPos = @POS; BEGIN TRY IF EXISTS(SELECT 1 FROM #LinkedServerPb WHERE posname = @POS) BEGIN SET @result_sp = 1 END ELSE BEGIN EXEC @result_sp = sys.sp_testlinkedserver @LksrvPos END END TRY BEGIN CATCH SET @result_sp = sign(@@error); END CATCH; IF @result_sp = 0 BEGIN SET @tsql = N'SELECT TOP 1 @versionNumberPos = [VersionNumber] FROM [' + @POS + '].[ActivePos_write].[upd].[DatabaseHistory] order by [DatabaseHistoryId] desc' EXEC [sys].[sp_executesql] @tsql, N'@versionNumberPos varchar(17) OUTPUT', @versionNumberPos OUTPUT IF @versionNumberSrv != @versionNumberPos BEGIN SET @message = @message + '- '+@POS+ ' Version missmatch: Pos ' +@versionNumberPos + '<> Server ' + @versionNumberSrv +CHAR(13)+CHAR(10) SET @countko = @countko + 1 END ELSE BEGIN SET @message = @message + '- '+@POS+ ' Version OK' +CHAR(13)+CHAR(10) SET @countok = @countok + 1 END END FETCH NEXT FROM c_Subscriptions INTO @POS END IF @message <> '' BEGIN SET @errfound = 1 SET @message = 'Liste des postes non abonnés à la publication :'+CHAR(13)+CHAR(10)+@message SELECT @percent = (@countko * 100 / (@counttot)) SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'SubMiss' IF @percent <= 29 BEGIN IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 1, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END END IF @percent >= 30 BEGIN IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 2, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END END /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END CLOSE c_Subscriptions DEALLOCATE c_Subscriptions DROP TABLE ##POS; END END TRY BEGIN CATCH /* Traitement des erreurs (avec RaiseError) */ EXEC dbo.get_Error_Info @in_RaiseError = 1 END CATCH /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ /* 5: Check de la disponibilité du share */ /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ BEGIN TRY IF (@in_CheckType = 5 or @in_CheckType = 0) BEGIN SET @message = '' SELECT @dbVersion = ActivePos_write.upd.DatabaseVersion() SELECT TOP 1 @ouCode = ou_code FROM ActivePos_server.dbo.GetCurrentOU() SELECT @fileName = CONVERT(VARCHAR(400),SettingValue) + '\ActivePos_read.' + @dbVersion + '.bak' FROM ActivePos_server.dbo.Settings WITH (NOLOCK) WHERE SettingId = 'Values.Pharmacy.DbInitializationBackupPath' IF @fileName is null BEGIN SELECT @fileName = CONVERT(VARCHAR(400), SettingValue) + '\ActivePos_read.' + @dbVersion + '.bak' FROM ActiveSystemServer.cfg.Settings WITH (NOLOCK) WHERE SettingId = 'Values.Modules.Replication.DbInitializationBackupPath' END SELECT @result_sp = HCITools.dbo.aps_File_Exists(@filename) IF @result_sp <> 1 BEGIN SET @errfound = 1 SET @message = 'Share Replinibackup injoignable pour la réplication SQL' SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'Replini' IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 1, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END END END TRY BEGIN CATCH /* Traitement des erreurs (avec RaiseError) */ EXEC dbo.get_Error_Info @in_RaiseError = 1 END CATCH /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ /* 7: Check de l'etat des souscriptions pour chaque poste */ /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ BEGIN TRY IF (@in_CheckType = 7 or @in_CheckType = 0) BEGIN SET @message = '' SET @countok = 0 SET @countko = 0 SET @percent = 0 IF EXISTS ( SELECT * FROM distribution.sys.tables WHERE name = 'msreplservers') BEGIN EXEC ('SELECT DISTINCT [Sub].[srvname] INTO ##POS FROM distribution.dbo.MSSubscriptions s JOIN distribution.dbo.MSreplservers Sub ON [Sub].[srvid] = [s].[subscriber_id] WHERE [s].[status] <> 2 ORDER BY [Sub].[srvname]'); END; ELSE BEGIN EXEC ('SELECT DISTINCT [Sub].[name] INTO ##POS FROM distribution.dbo.MSSubscriptions s JOIN master.sys.servers Sub ON [Sub].[server_id] = [s].[subscriber_id] WHERE [s].[status] <> 2 ORDER BY [Sub].[name]'); END; DECLARE c_Subscriptions3 CURSOR LOCAL FORWARD_ONLY STATIC FOR SELECT * FROM ##POS SELECT TOP 1 @versionNumberSrv = [VersionNumber] FROM [ActivePos_read].[upd].[DatabaseHistory] ORDER BY [DatabaseHistoryId] DESC OPEN c_Subscriptions3 FETCH NEXT FROM c_Subscriptions3 INTO @POS WHILE @@fetch_status <> -1 BEGIN SET @LksrvPos = @POS; BEGIN TRY IF EXISTS(SELECT 1 FROM #LinkedServerPb WHERE posname = @POS) BEGIN SET @result_sp = 1 END ELSE BEGIN EXEC @result_sp = sys.sp_testlinkedserver @LksrvPos END END TRY BEGIN CATCH SET @result_sp = sign(@@error); END CATCH; IF @result_sp = 0 BEGIN SET @tsql = N'SELECT TOP 1 @versionNumberPos = [VersionNumber] FROM [' + @POS + '].[ActivePos_read].[upd].[DatabaseHistory] order by [DatabaseHistoryId] desc' EXEC [sys].[sp_executesql] @tsql, N'@versionNumberPos varchar(17) OUTPUT', @versionNumberPos OUTPUT IF @versionNumberSrv != @versionNumberPos BEGIN SET @message = @message + '- '+@POS+ ' Version missmatch' +CHAR(13)+CHAR(10) SET @countko = @countko + 1 END ELSE BEGIN SET @message = @message + '- '+@POS+ ' Version OK' +CHAR(13)+CHAR(10) SET @countok = @countok + 1 END END FETCH NEXT FROM c_Subscriptions3 INTO @POS END IF @message <> '' BEGIN SET @errfound = 1 SET @message = 'Liste des postes avec un problème de souscription :'+CHAR(13)+CHAR(10)+@message SELECT @percent = (@countko * 100 / (@counttot)) SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'SubCheck' IF @percent <= 29 BEGIN IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 1, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END END IF @percent >= 30 BEGIN IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 2, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END END /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END CLOSE c_Subscriptions3 DEALLOCATE c_Subscriptions3 DROP TABLE ##POS END END TRY BEGIN CATCH /* Traitement des erreurs (avec RaiseError) */ EXEC dbo.get_Error_Info @in_RaiseError = 1 END CATCH /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ /* 8: Check All Agents Status */ /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ BEGIN TRY IF (@in_CheckType = 8 or @in_CheckType = 0) BEGIN SET @message = '' CREATE TABLE #ReplMonitorAgents ( agent sysname, dbname sysname NULL, name nvarchar(100) NULL, status int NULL, message nvarchar(255) NULL, publisher sysname NULL, publisher_db sysname NULL, publication sysname NULL, subscriber sysname NULL, subscriber_db sysname NULL, subscription_type int NULL, start_time nvarchar(24) NULL, time nvarchar(24) NULL, duration int NULL, comments nvarchar(255) NULL, delivery_time int NULL, delivered_transactions int NULL, delivered_commands int NULL, average_commands int NULL, transactions_processed int NULL, commands_processed int NULL, download_inserts int NULL, download_updates int NULL, download_deletes int NULL, publisher_conficts int NULL, upload_inserts int NULL, upload_updates int NULL, upload_deletes int NULL, subscriber_conficts int NULL, delivery_rate float NULL, delivery_latency int NULL, error_id INT NULL, job_id binary(16) NULL, local_job bit NULL, profile_id int NULL, agent_id int NULL, local_timestamp binary(8) NULL, offload_enabled bit NULL, offload_server sysname NULL, subscriber_type tinyint NULL, dynamic_filter_login sysname NULL, dynamic_filter_hostname sysname NULL ) INSERT INTO #ReplMonitorAgents (agent, dbname, name, status, publisher , publisher_db, publication, start_time, time, duration, comments, delivered_transactions, delivered_commands, delivery_rate, error_id, job_id, local_job, profile_id, agent_id, local_timestamp, dynamic_filter_login, dynamic_filter_hostname) SELECT 'Snapshot', dbname, name, status, publisher , publisher_db, publication, start_time, time, duration, comments, delivered_transactions, delivered_commands, delivery_rate, error_id, job_id, local_job, profile_id, agent_id, local_timestamp, dynamic_filter_login, dynamic_filter_hostname FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC sp_MSenum_replication_agents @type = 1 with result sets (( dbname sysname NULL, name nvarchar(100) NULL, status int NULL, publisher sysname NULL, publisher_db sysname NULL, publication sysname NULL, start_time nvarchar(24) NULL, time nvarchar(24) NULL, duration int NULL, comments nvarchar(255) NULL, delivered_transactions int NULL, delivered_commands int NULL, delivery_rate float NULL, error_id INT NULL, job_id binary(16) NULL, local_job bit NULL, profile_id int NULL, agent_id int NULL, local_timestamp binary(8) NULL, dynamic_filter_login sysname NULL, dynamic_filter_hostname sysname NULL ))') INSERT INTO #ReplMonitorAgents (agent, dbname, name, status, publisher , publisher_db, start_time, time, duration, comments, delivery_time, delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency, error_id, job_id, local_job, profile_id, agent_id, local_timestamp) SELECT 'Logreader', dbname, name, status, publisher , publisher_db, start_time, time, duration, comments, delivery_time, delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency, error_id, job_id, local_job, profile_id, agent_id, local_timestamp FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC sp_MSenum_replication_agents @type = 2 with result sets (( dbname sysname NULL, name nvarchar(100) NULL, status int NULL, publisher sysname NULL, publisher_db sysname NULL, start_time nvarchar(24) NULL, time nvarchar(24) NULL, duration int NULL, comments nvarchar(255) NULL, delivery_time int NULL, delivered_transactions int NULL, delivered_commands int NULL, average_commands int NULL, delivery_rate float NULL, delivery_latency int NULL, error_id INT NULL, job_id binary(16) NULL, local_job bit NULL, profile_id int NULL, agent_id int NULL, local_timestamp binary(8) NULL ))') INSERT INTO #ReplMonitorAgents (agent, dbname, name, status, publisher , publisher_db, publication, subscriber, subscriber_db, subscription_type, start_time, time, duration, comments, delivery_time, delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency, error_id, job_id, local_job, profile_id, agent_id, local_timestamp, offload_enabled, offload_server, subscriber_type) SELECT 'Distribution', dbname, name, status, publisher , publisher_db, publication, subscriber, subscriber_db, subscription_type, start_time, time, duration, comments, delivery_time, delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency, error_id, job_id, local_job, profile_id, agent_id, local_timestamp, offload_enabled, offload_server, subscriber_type FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC sp_MSenum_replication_agents @type = 3 with result sets (( dbname sysname NULL, name nvarchar(100) NULL, status int NULL, publisher sysname NULL, publisher_db sysname NULL, publication sysname NULL, subscriber sysname NULL, subscriber_db sysname NULL, subscription_type int NULL, start_time nvarchar(24) NULL, time nvarchar(24) NULL, duration int NULL, comments nvarchar(255) NULL, delivery_time int NULL, delivered_transactions int NULL, delivered_commands int NULL, average_commands int NULL, delivery_rate float NULL, delivery_latency int NULL, error_id INT NULL, job_id binary(16) NULL, local_job bit NULL, profile_id int NULL, agent_id int NULL, local_timestamp binary(8) NULL, offload_enabled bit NULL, offload_server sysname NULL, subscriber_type tinyint NULL ))') INSERT INTO #ReplMonitorAgents (agent, dbname, name, status, publisher , publisher_db, publication, subscriber, subscriber_db, subscription_type, start_time, time, duration, comments, delivery_rate, download_inserts, download_updates, download_deletes, publisher_conficts, upload_inserts, upload_updates, upload_deletes, subscriber_conficts, error_id, job_id, local_job, profile_id, agent_id, local_timestamp, offload_enabled, offload_server, subscriber_type) SELECT 'Merge', dbname, name, status, publisher , publisher_db, publication, subscriber, subscriber_db, subscription_type, start_time, time, duration, comments, delivery_rate, download_inserts, download_updates, download_deletes, publisher_conficts, upload_inserts, upload_updates, upload_deletes, subscriber_conficts, error_id, job_id, local_job, profile_id, agent_id, local_timestamp, offload_enabled, offload_server, subscriber_type FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC sp_MSenum_replication_agents @type = 4 with result sets (( dbname sysname NULL, name nvarchar(100) NULL, status int NULL, publisher sysname NULL, publisher_db sysname NULL, publication sysname NULL, subscriber sysname NULL, subscriber_db sysname NULL, subscription_type int NULL, start_time nvarchar(24) NULL, time nvarchar(24) NULL, duration int NULL, comments nvarchar(255) NULL, delivery_rate float NULL, download_inserts int NULL, download_updates int NULL, download_deletes int NULL, publisher_conficts int NULL, upload_inserts int NULL, upload_updates int NULL, upload_deletes int NULL, subscriber_conficts int NULL, error_id INT NULL, job_id binary(16) NULL, local_job bit NULL, profile_id int NULL, agent_id int NULL, local_timestamp binary(8) NULL, offload_enabled bit NULL, offload_server sysname NULL, subscriber_type tinyint NULL ))') INSERT INTO #ReplMonitorAgents (agent, dbname, name, status, start_time, time, duration, comments, transactions_processed, commands_processed, average_commands, delivery_rate, delivery_latency, error_id, job_id, profile_id, agent_id, local_timestamp) SELECT 'Queue reader', dbname, name, status, start_time, time, duration, comments, transactions_processed, commands_processed, average_commands, delivery_rate, delivery_latency, error_id, job_id, profile_id, agent_id, local_timestamp FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC sp_MSenum_replication_agents @type = 9 with result sets (( dbname sysname NULL, name nvarchar(100) NULL, status int NULL, start_time nvarchar(24) NULL, time nvarchar(24) NULL, duration int NULL, comments nvarchar(255) NULL, transactions_processed int NULL, commands_processed int NULL, average_commands int NULL, delivery_rate float NULL, delivery_latency int NULL, error_id INT NULL, job_id binary(16) NULL, profile_id int NULL, agent_id int NULL, local_timestamp binary(8) NULL ))') DECLARE CursorLastHistory CURSOR FOR SELECT agent,dbname,name,status,time,comments,agent_id,publication,publisher_db,subscriber FROM #ReplMonitorAgents ORDER BY publisher_db,publication desc,subscriber desc OPEN CursorLastHistory FETCH NEXT FROM CursorLastHistory INTO @Agent_type,@distribution_db,@Jobname,@status,@EndTime,@Comments,@Agent_id,@publication,@publisher_db,@subscriber WHILE @@FETCH_STATUS = 0 BEGIN SET @message = '' SELECT @AlertKey = CASE @Agent_type WHEN 'Snapshot' THEN 'SnapAgt' WHEN 'Logreader' THEN 'LogReadAgt' WHEN 'Distribution' THEN 'DistAgt' WHEN 'Merge' THEN 'MergeAgt' WHEN 'Queue reader' THEN 'QreaderAgt' END IF @status > 4 BEGIN IF @subscriber IS NOT NULL AND @publication <> 'publ_Ceres_Arizona_Gaia_Tran' BEGIN BEGIN TRY IF EXISTS(SELECT 1 FROM #LinkedServerPb WHERE posname = @subscriber) BEGIN SET @result_sp = 1 END ELSE BEGIN EXEC @result_sp = sys.sp_testlinkedserver @subscriber END END TRY BEGIN CATCH SET @result_sp = @@error; END CATCH; END ELSE SET @result_sp = 0 IF @result_sp = 0 SELECT @message = 'L''agent '+ @Agent_type +' '+ @Jobname +' de la ' + CASE WHEN @publication IS NOT NULL THEN 'publication '+@publication WHEN @publication IS NULL THEN 'base '+@publisher_db END + CASE WHEN @subscriber IS NOT NULL THEN ' de l''abonné '+@subscriber ELSE '' END + ' est en erreur : ' + @Comments IF @message <> '' BEGIN SET @errfound = 1 SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = @AlertKey IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 1, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END END ELSE IF @status = 2 AND DATEDIFF(SECOND,@EndTime,GETDATE()) > 120 BEGIN IF @subscriber IS NOT NULL AND @publication <> 'publ_Ceres_Arizona_Gaia_Tran' BEGIN BEGIN TRY IF EXISTS(SELECT 1 FROM #LinkedServerPb WHERE posname = @subscriber) BEGIN SET @result_sp = 1 END ELSE BEGIN EXEC @result_sp = sys.sp_testlinkedserver @subscriber END END TRY BEGIN CATCH SET @result_sp = @@error; END CATCH; END ELSE IF @Agent_type = 'Snapshot' SET @result_sp = 1 ELSE SET @result_sp = 0 IF @result_sp = 0 SELECT @message = 'L''agent '+ @Agent_type +' '+ @Jobname +' de la ' + CASE WHEN @publication IS NOT NULL THEN 'publication '+@publication WHEN @publication IS NULL THEN 'base '+@publisher_db END + CASE WHEN @subscriber IS NOT NULL THEN ' de l''abonné '+@subscriber ELSE '' END + ' est stoppé depuis plus de 2 minutes' IF @message <> '' BEGIN SET @errfound = 1 SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'StopJobs' IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 2, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END END ELSE BEGIN SELECT @TableSys = CASE @Agent_type WHEN 'Snapshot' THEN 'MSsnapshot_history' WHEN 'Logreader' THEN 'MSlogreader_history' WHEN 'Distribution' THEN 'MSdistribution_history' WHEN 'Merge' THEN 'MSmerge_history' WHEN 'Queue reader' THEN 'MSqreader_history' END SET @tsql = 'IF EXISTS (SELECT 1 FROM (SELECT TOP 3 MH.agent_id, MH.error_id FROM '+ @distribution_db +'.dbo.'+ @TableSys +' as MH WHERE MH.agent_id = @Agent_id ORDER BY MH.start_time DESC, MH.time desc) as T1 WHERE T1.error_id > 0) BEGIN SELECT @message = ''A ''+CONVERT(nvarchar(30), T1.start_time, 120)+'' Il y a eu une erreur ''+CAST(T1.error_id as varchar(10))+'' sur l''''agent ''+ @Agent_type +'' ''+ @Jobname +'' de la '' + CASE WHEN @publication IS NOT NULL THEN ''publication ''+@publication WHEN @publication IS NULL THEN ''base ''+@publisher_db END + CASE WHEN @subscriber IS NOT NULL THEN '' de l''''abonné ''+@subscriber ELSE '''' END + '' : '' + @Comments FROM (SELECT TOP 3 start_time, error_id FROM '+ @distribution_db +'.dbo.'+ @TableSys +' WHERE agent_id = @Agent_id ORDER BY start_time DESC, time desc) as T1 WHERE T1.error_id > 0 END' EXEC [sys].[sp_executesql] @tsql, N'@Agent_id int,@Agent_type sysname,@JobName varchar(100),@publication sysname,@publisher_db sysname,@subscriber sysname,@comments varchar(250),@Message varchar(max) OUTPUT',@Agent_id=@Agent_id,@Agent_type=@Agent_type,@JobName=@JobName,@publication=@publication,@publisher_db=@publisher_db,@subscriber=@subscriber,@comments=@comments, @message = @message OUTPUT IF @Message like '%No replicated transactions are available%' or @Message like '%transaction(s) with%command(s) were delivered%' BEGIN SET @Message = '' END IF @Message <> '' BEGIN SET @errfound = 1 SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = @AlertKey IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 1, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END END FETCH NEXT FROM CursorLastHistory INTO @Agent_type,@distribution_db,@Jobname,@status,@EndTime,@Comments,@Agent_id,@publication,@publisher_db,@subscriber END CLOSE CursorLastHistory DEALLOCATE CursorLastHistory IF OBJECT_ID('tempdb..#ReplMonitorAgents') IS NOT NULL DROP TABLE #ReplMonitorAgents END END TRY BEGIN CATCH /* Traitement des erreurs (avec RaiseError) */ EXEC dbo.get_Error_Info @in_RaiseError = 1 END CATCH /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ /* 9: Check jobs de réplication à double */ /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ BEGIN TRY IF (@in_CheckType = 9 or @in_CheckType = 0) BEGIN SET @message = '' DECLARE c_pos CURSOR LOCAL FORWARD_ONLY STATIC FOR SELECT SUBSTRING(name,CHARINDEX('ActivePosTran',name,0)+14,CHARINDEX('-',name,CHARINDEX('ActivePosTran',name,0)+14)-(CHARINDEX('ActivePosTran',name,0)+14)) FROM msdb.dbo.sysjobs WHERE name like '%ActivePos_read-ActivePosTran%' AND SUBSTRING(name,CHARINDEX('ActivePosTran',name,0)+14,8) like 'W%' GROUP BY SUBSTRING(name,CHARINDEX('ActivePosTran',name,0)+14,CHARINDEX('-',name,CHARINDEX('ActivePosTran',name,0)+14)-(CHARINDEX('ActivePosTran',name,0)+14)) HAVING COUNT(*) > 1 OPEN c_pos FETCH NEXT FROM c_pos INTO @POS WHILE @@fetch_status = 0 BEGIN SET @message = @Message + 'Il y a trop de jobs pour le poste : '+@POS+CHAR(13)+CHAR(10) FETCH NEXT FROM c_pos INTO @POS END CLOSE c_pos DEALLOCATE c_pos IF @message <> '' BEGIN SET @errfound = 1 SET @message = 'Liste des jobs en double :'+CHAR(13)+CHAR(10)+@message SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'DuplicJobs' IF @isProd = 1 BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 1, @CheckDate, @message) END ELSE BEGIN INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, @message) END /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END END END TRY BEGIN CATCH /* Traitement des erreurs (avec RaiseError) */ EXEC dbo.get_Error_Info @in_RaiseError = 1 END CATCH /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ /* 10: Verifications performances et des commandes non distribuées */ /*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/ BEGIN TRY IF (@in_CheckType = 10 or @in_CheckType = 0) BEGIN SET @message = '' SET @countko = 0 SET @percent = 0 SELECT RQH_subscriber_db, RQH_records_in_queue, RQH_catch_up_time, RQH_log_date INTO #LastHourStatus FROM HciTools.[mon].[Replication_queue_history] WHERE RQH_records_in_queue > 1000 and RQH_catch_up_time > 60 AND RQH_log_date > DATEADD(hh, -1, getdate()) ORDER BY RQH_log_date DESC /* Separate check for TriaFin replication, only ont subscription registered */ IF EXISTS (SELECT RQH_subscriber_db, count(*) FROM #LastHourStatus GROUP BY RQH_subscriber_db HAVING RQH_subscriber_db like '%GALCTP' and count(*) >= 3) BEGIN SELECT @Message = @Message + CHAR(13) + CHAR(10) + '-' + RQH_subscriber_db + ' : ' + cast(RQH_records_in_queue as varchar(20)) +': ' + cast(RQH_catch_up_time as varchar (10)) + '(s) : ' + convert(varchar(20),RQH_log_date, 120) + CHAR(13) + CHAR(10) FROM #LastHourStatus WHERE RQH_subscriber_db like '%GALCTP' END /* Suiz percentage check for perf, need to see caisse mall, pos covid and out f site pos (ex chantepoulet) single perf issue SELECT @countko = count(distinct RQH_subscriber_db) FROM #LastHourStatus WHERE RQH_subscriber_db not like '%GALCTP' SELECT @percent = (@countko * 100 / (@counttot)) IF @percent < 50 and @message = '' Return */ IF EXISTS ( SELECT * FROM distribution.sys.tables WHERE name = 'msreplservers') BEGIN EXEC ('SELECT REPLACE([POS].[POS_hostname],''-'','''') as POS_hostname INTO ##POS FROM arizona.dbo.bmc_application_key WITH (NOLOCK) JOIN arizona.dbo.bmc_application_default WITH (NOLOCK) ON BAPD_bmc_application_key = Bmc_application_key_ID JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK) ON [OUA].[Organizational_unit_ID] = bapd_value JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK) ON [ou].[OU_code] = [OUA].[OU_code] JOIN [Arizona].[dbo].[Point_of_sale] [pos] ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit] WHERE BAPK_key = ''cvCurrentOrganizationalUnit'' AND [pos].[POS_active] = 1 AND [pos].[POS_number] <= 39 AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */ --AND OU.CROU_locally_used = 1 AND EXISTS (SELECT DISTINCT [ss].[srvname] FROM distribution.dbo.MSreplservers ss WITH (NOLOCK) INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK) ON [ss].[srvid] = [ms].[subscriber_id] WHERE [ss].[srvname] = POS.[POS_hostname])'); END; ELSE BEGIN EXEC ('SELECT REPLACE([POS].[POS_hostname],''-'','''') as POS_hostname INTO ##POS FROM arizona.dbo.bmc_application_key WITH (NOLOCK) JOIN arizona.dbo.bmc_application_default WITH (NOLOCK) ON BAPD_bmc_application_key = Bmc_application_key_ID JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK) ON [OUA].[Organizational_unit_ID] = bapd_value JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK) ON [ou].[OU_code] = [OUA].[OU_code] JOIN [Arizona].[dbo].[Point_of_sale] [pos] ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit] WHERE BAPK_key = ''cvCurrentOrganizationalUnit'' AND [pos].[POS_active] = 1 AND [pos].[POS_number] <= 39 AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */ --AND OU.CROU_locally_used = 1 AND EXISTS (SELECT DISTINCT [ss].[name] FROM sys.servers ss WITH (NOLOCK) INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK) ON [ss].[server_id] = [ms].[subscriber_id] WHERE [ss].[name] = POS.[POS_hostname])'); END; /* Online subscription having undistributed transaction with latency in last hour */ DECLARE c_Subscriptions4 CURSOR LOCAL FORWARD_ONLY STATIC FOR SELECT * FROM ##POS OPEN c_Subscriptions4 FETCH NEXT FROM c_Subscriptions4 INTO @POS WHILE @@fetch_status <> -1 BEGIN /* Select only POS having more or equal than 4 entries ko in last hour */ IF NOT EXISTS(SELECT 1 FROM #LinkedServerPb WHERE posname = @POS) AND EXISTS (SELECT RQH_subscriber_db, count(*) FROM #LastHourStatus GROUP BY RQH_subscriber_db HAVING RQH_subscriber_db = @pos and count(*) >= 4) BEGIN SELECT @Message = @Message + '-' + RQH_subscriber_db + ' : ' + cast(RQH_records_in_queue as varchar(20)) + ': '+ cast(RQH_catch_up_time as varchar (10)) + '(s) : ' + convert(varchar(20),RQH_log_date, 120) + CHAR(13) + CHAR(10) FROM #LastHourStatus WHERE RQH_subscriber_db = @pos SELECT @Message = @Message + CHAR(13) + CHAR(10) END FETCH NEXT FROM c_Subscriptions4 INTO @POS END DROP TABLE #LastHourStatus IF @message <> '' BEGIN SET @errfound = 1 SET @message = 'Subscription(s) with undistributed commands and catch up time :' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + @message /* S'il y a une erreur, on envoie un mail aux DBA */ IF @in_Email = 1 BEGIN EXEC [dbo].[aps_Send_Mail_with_template] @in_param_varchar_2 = 'DBA_operator;', @in_job_type = 3, /* 3 = warning */ @in_param_varchar_3 = @Message END END CLOSE c_Subscriptions4 DEALLOCATE c_Subscriptions4 DROP TABLE ##POS END END TRY BEGIN CATCH /* Traitement des erreurs (avec RaiseError) */ EXEC dbo.get_Error_Info @in_RaiseError = 1 END CATCH /* Status OK si pas d'erreur */ IF @errfound = 0 BEGIN SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'OK' INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, '') END DROP TABLE #LinkedServerPb END ELSE BEGIN /* N+2 Sites = OK */ SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'OK' INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message) VALUES (@AlertID, 0, @CheckDate, '') END END