353 lines
12 KiB
PL/PgSQL
353 lines
12 KiB
PL/PgSQL
USE master;
|
|
SET NOCOUNT ON;
|
|
|
|
IF OBJECT_ID('tempdb..#tempsub2') IS NOT NULL
|
|
DROP TABLE #tempsub2;
|
|
|
|
IF OBJECT_ID('tempdb..#tempsub1') IS NOT NULL
|
|
DROP TABLE #tempsub1;
|
|
|
|
CREATE TABLE #tempsub2
|
|
(
|
|
publisher sysname,
|
|
publisher_db sysname,
|
|
publication sysname,
|
|
subscriber sysname,
|
|
subscriber_db sysname,
|
|
Pending_Commands INT,
|
|
time_to_deliver_pending_Commands INT
|
|
);
|
|
|
|
DECLARE @publisher sysname,
|
|
@publisher_db sysname,
|
|
@publication sysname,
|
|
@subscriber sysname,
|
|
@subscriber_db sysname,
|
|
@GAIASrvName VARCHAR(100);
|
|
|
|
SELECT @GAIASrvName = CASE WHEN [Type] = 'DEVE' THEN 'SWGCMDEV01.CENTRALINFRA.NET\DGALCTP'
|
|
WHEN [Type] = 'VALI' THEN 'SWGCMQMS01.CENTRALINFRA.NET\TGALCTP'
|
|
WHEN [Type] = 'PROD' THEN 'SWGCMDB01.CENTRALINFRA.NET\PGALCTP'
|
|
ELSE ''
|
|
END FROM [master].[cfg].[InstanceContext];
|
|
|
|
IF OBJECT_ID('tempdb..#tempsub1')IS NOT NULL BEGIN;
|
|
DROP TABLE #tempsub1;
|
|
END;
|
|
|
|
SELECT sub3.[publisher],
|
|
sub1.[publisher_db],
|
|
sub1.[publication],
|
|
--CASE
|
|
-- WHEN [sub1].[anonymous_subid] IS NOT NULL THEN
|
|
-- UPPER([sub1].[subscriber_name])
|
|
-- ELSE
|
|
-- UPPER(srv.name)
|
|
--END 'Subscriber',
|
|
CAST(NULL AS sysname)AS Subscriber, --will be computed later
|
|
[sub1].[subscriber_db],
|
|
[sub1].[job_id],
|
|
[sub1].[id],
|
|
[sub1].[subscription_type],
|
|
[sub1].[name]
|
|
INTO [#tempsub1]
|
|
FROM
|
|
(
|
|
SELECT
|
|
[agents].[publisher_db]
|
|
,[agents].[publisher_id]
|
|
,[agents].[publication]
|
|
,[agents].[anonymous_subid]
|
|
,[agents].[subscriber_name]
|
|
,[agents].[subscriber_db]
|
|
,[agents].[job_id]
|
|
,[agents].[id]
|
|
,[agents].[subscription_type]
|
|
,[agents].[name]
|
|
FROM distribution..MSdistribution_agents agents
|
|
WHERE subscriber_db IN ( 'Gaia' )
|
|
AND anonymous_subid IS NULL
|
|
) sub1
|
|
INNER JOIN
|
|
(
|
|
SELECT publisher,
|
|
publisher_db,
|
|
publication,
|
|
publication_type,
|
|
agent_name,
|
|
publisher_srvid,
|
|
job_id
|
|
FROM distribution..MSreplication_monitordata
|
|
WHERE publication_id IS NOT NULL
|
|
AND agent_type = 3
|
|
) sub3
|
|
ON sub1.publisher_id = sub3.publisher_srvid
|
|
--AND CAST(sub1.job_id AS UNIQUEIDENTIFIER) = sub3.job_id
|
|
AND sub1.publisher_db = sub3.publisher_db
|
|
AND sub1.publication = sub3.publication
|
|
AND sub1.subscription_type = sub3.publication_type
|
|
AND sub1.name = sub3.agent_name
|
|
--CROSS JOIN master.sys.servers AS srv
|
|
--WHERE srv.name = @GAIASrvName
|
|
;
|
|
|
|
DECLARE subscribers CURSOR FOR
|
|
SELECT publisher,
|
|
publisher_db,
|
|
publication,
|
|
Subscriber,
|
|
subscriber_db
|
|
FROM #tempsub1;
|
|
|
|
OPEN subscribers;
|
|
FETCH NEXT FROM subscribers
|
|
INTO @publisher,
|
|
@publisher_db,
|
|
@publication,
|
|
@subscriber,
|
|
@subscriber_db;
|
|
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
/* Those CTE are extracting the publication, publisher and subscriber from MS helper procs */
|
|
WITH subscriber( [publisher] , [publication]) AS (
|
|
SELECT publisher, subscriber
|
|
FROM OPENROWSET('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF; EXEC arizona.dbo.sp_helpsubscriberinfo
|
|
with result sets ((
|
|
[publisher] nvarchar(128),
|
|
[subscriber] nvarchar(128),
|
|
[type] tinyint,
|
|
[login] nvarchar(128),
|
|
[password] nvarchar(524),
|
|
[commit_batch_size] int,
|
|
[status_batch_size] int,
|
|
[flush_frequency] int,
|
|
[frequency_type] int,
|
|
[frequency_interval] int,
|
|
[frequency_relative_interval] int,
|
|
[frequency_recurrence_factor] int,
|
|
[frequency_subday] int,
|
|
[frequency_subday_interval] int,
|
|
[active_start_time_of_day] int,
|
|
[active_end_time_of_day] int,
|
|
[active_start_date] int,
|
|
[active_end_date] int,
|
|
[retryattempt] int,
|
|
[retrydelay] int,
|
|
[description] nvarchar(255),
|
|
[security_mode] int,
|
|
[frequency_type2] int,
|
|
[frequency_interval2] int,
|
|
[frequency_relative_interval2] int,
|
|
[frequency_recurrence_factor2] int,
|
|
[frequency_subday2] int,
|
|
[frequency_subday_interval2] int,
|
|
[active_start_time_of_day2] int,
|
|
[active_end_time_of_day2] int,
|
|
[active_start_date2] int,
|
|
[active_end_date2] int
|
|
|
|
));
|
|
') [or]
|
|
)
|
|
, subscription(subscriber, publication, [destination database]) AS (
|
|
SELECT subscriber, publication, [destination database]
|
|
FROM OPENROWSET('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF; EXEC arizona.dbo.sp_helpsubscription
|
|
with result sets ((
|
|
[subscriber] nvarchar(128),
|
|
[publication] nvarchar(128),
|
|
[article] nvarchar(128),
|
|
[destination database] nvarchar(128),
|
|
[subscription status] int,
|
|
[synchronization type] tinyint,
|
|
[subscription type] int,
|
|
[full subscription] bit,
|
|
[subscription name] nvarchar(386),
|
|
[update mode] int,
|
|
[distribution job id] varbinary(16),
|
|
[loopback_detection] bit,
|
|
[offload_enabled] bit,
|
|
[offload_server] nvarchar(128),
|
|
[dts_package_name] nvarchar(128),
|
|
[dts_package_location] int,
|
|
[subscriber_security_mode] smallint,
|
|
[subscriber_login] nvarchar(128),
|
|
[subscriber_password] varchar(10),
|
|
[job_login] nvarchar(128),
|
|
[job_password] varchar(10),
|
|
[distrib_agent_name] nvarchar(100),
|
|
[subscriber_type] tinyint,
|
|
[subscriber_provider] nvarchar(128),
|
|
[subscriber_datasource] nvarchar(4000),
|
|
[subscriber_providerstring] nvarchar(4000),
|
|
[subscriber_location] nvarchar(4000),
|
|
[subscriber_catalog] nvarchar(128)
|
|
));
|
|
') [or]
|
|
)
|
|
|
|
SELECT DISTINCT
|
|
@subscriber = [pub].[subscriber]
|
|
FROM [subscription] pub
|
|
JOIN [subscriber] sub ON sub.publication = pub.subscriber
|
|
JOIN sys.servers s ON s.[data_source] = sub.publication
|
|
LEFT JOIN [distribution].dbo.[MSpublications] [ms] ON ms.publication = pub.publication
|
|
WHERE pub.[destination database] = 'gaia'
|
|
AND sub.[publisher] = @publisher
|
|
AND [ms].[publisher_db] = @publisher_db
|
|
;
|
|
|
|
/* push back the subscriber name into the [#tempsub1] table, for compatibility with sql 2017 and below */
|
|
UPDATE t SET [t].[Subscriber] = @subscriber
|
|
FROM [#tempsub1] [t]
|
|
WHERE t.[Subscriber] IS NULL
|
|
AND t.[publisher_db] = @publisher_db
|
|
AND t.[publication] = @publication
|
|
;
|
|
|
|
PRINT '
|
|
@publisher: '+ISNULL(CONVERT(VARCHAR(200),@publisher),'NULL')+'
|
|
@publisher_db: '+ISNULL(CONVERT(VARCHAR(200),@publisher_db),'NULL')+'
|
|
@publication: '+ISNULL(CONVERT(VARCHAR(200),@publication),'NULL')+'
|
|
@subscriber: '+ISNULL(CONVERT(VARCHAR(200),@subscriber),'NULL')+'
|
|
@subscriber_db: '+ISNULL(CONVERT(VARCHAR(200),@subscriber_db),'NULL')+'
|
|
|
|
EXEC distribution..sp_replmonitorsubscriptionpENDingcmds
|
|
@publisher= '''+ISNULL(CONVERT(VARCHAR(200),@publisher),'NULL')+'''
|
|
,@subscription_type=0
|
|
,@publisher_db= '''+ISNULL(CONVERT(VARCHAR(200),@publisher_db),'NULL')+'''
|
|
,@publication = '''+ISNULL(CONVERT(VARCHAR(200),@publication),'NULL')+'''
|
|
,@subscriber= '''+ISNULL(CONVERT(VARCHAR(200),@subscriber),'NULL')+'''
|
|
,@subscriber_db= '''+ISNULL(CONVERT(VARCHAR(200),@subscriber_db),'NULL')+'''
|
|
;
|
|
|
|
';
|
|
|
|
INSERT INTO [#tempsub2] (
|
|
[publisher]
|
|
,[publisher_db]
|
|
,[publication]
|
|
,[subscriber]
|
|
,[subscriber_db]
|
|
,[Pending_Commands]
|
|
,[time_to_deliver_pending_Commands]
|
|
)
|
|
|
|
EXEC ('
|
|
SELECT ''' + @publisher + ''' , ''' + @publisher_db + ''' ,''' + @publication + ''' , ''' + @subscriber + ''' , ''' + @subscriber_db + ''' ,*
|
|
FROM OPENROWSET (''SQLOLEDB'',''Server=(local);TRUSTED_CONNECTION=YES;'',''set fmtonly off EXEC distribution..sp_replmonitorsubscriptionpENDingcmds @publisher= ''''' + @publisher + ''''' ,@subscription_type=0, @publisher_db= ''''' + @publisher_db + ''''',@publication = ''''' + @publication + ''''',@subscriber= ''''' + @subscriber + ''''' ,@subscriber_db=''''' + @subscriber_db + '''''
|
|
with result sets
|
|
((
|
|
pendingcmdcount INT,
|
|
estimatedprocesstime INT
|
|
))
|
|
'')
|
|
' );
|
|
|
|
PRINT ('
|
|
SELECT ''' + @publisher + ''' , ''' + @publisher_db + ''' ,''' + @publication + ''' , ''' + @subscriber + ''' , ''' + @subscriber_db + ''' ,*
|
|
FROM OPENROWSET (''SQLOLEDB'',''Server=(local);TRUSTED_CONNECTION=YES;'',''set fmtonly off EXEC distribution..sp_replmonitorsubscriptionpENDingcmds @publisher= ''''' + @publisher + ''''' ,@subscription_type=0, @publisher_db= ''''' + @publisher_db + ''''',@publication = ''''' + @publication + ''''',@subscriber= ''''' + @subscriber + ''''' ,@subscriber_db=''''' + @subscriber_db + ''''''')
|
|
' );
|
|
|
|
FETCH NEXT FROM subscribers
|
|
INTO @publisher,
|
|
@publisher_db,
|
|
@publication,
|
|
@subscriber,
|
|
@subscriber_db;
|
|
|
|
END;
|
|
|
|
CLOSE subscribers;
|
|
DEALLOCATE subscribers;
|
|
|
|
|
|
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM #tempsub1 Info
|
|
INNER JOIN #tempsub2 Pending_commands
|
|
ON Info.publisher_db = Pending_commands.publisher_db
|
|
AND Info.publication = Pending_commands.publication
|
|
AND Info.Subscriber = Pending_commands.subscriber
|
|
AND Info.subscriber_db = Pending_commands.subscriber_db
|
|
LEFT OUTER JOIN msdb..sysjobs jobs
|
|
ON Info.job_id = jobs.job_id
|
|
INNER JOIN
|
|
(
|
|
SELECT time,
|
|
agent_id,
|
|
runstatus,
|
|
delivery_latency,
|
|
comments,
|
|
ROW_NUMBER() OVER (PARTITION BY agent_id ORDER BY time DESC) AS pos
|
|
FROM distribution..MSdistribution_history
|
|
) comment
|
|
ON comment.agent_id = Info.id
|
|
WHERE comment.pos = 1
|
|
)
|
|
BEGIN
|
|
SELECT 'Not Started' AS status,@@SERVERNAME AS publisher, publisher_db,publication,'' AS subscriber,subscriber_db,NULL AS Pending, 'Push' AS [Type],'' AS Distribution_agent_name, 'Not Started' AS comments
|
|
FROM distribution..MSdistribution_agents agents
|
|
WHERE subscriber_db IN ( 'Gaia' )
|
|
AND anonymous_subid IS NULL;
|
|
END;
|
|
ELSE
|
|
BEGIN
|
|
|
|
SELECT CASE comment.runstatus
|
|
WHEN 1 THEN
|
|
'Started'
|
|
WHEN 2 THEN
|
|
'Succeeded'
|
|
WHEN 3 THEN
|
|
'In progress'
|
|
WHEN 4 THEN
|
|
'Idle'
|
|
WHEN 5 THEN
|
|
'Retrying'
|
|
WHEN 6 THEN
|
|
'Failed'
|
|
END status,
|
|
Pending_commands.publisher,
|
|
Pending_commands.publisher_db,
|
|
Pending_commands.publication,
|
|
Pending_commands.subscriber,
|
|
Pending_commands.subscriber_db,
|
|
Pending_commands.Pending_Commands AS Pending,
|
|
CASE Info.subscription_type
|
|
WHEN 0 THEN
|
|
'Push'
|
|
WHEN 1 THEN
|
|
'Pull'
|
|
WHEN 2 THEN
|
|
'Anonymous'
|
|
END 'Type',
|
|
Info.name 'Distribution_agent_name',
|
|
comment.comments
|
|
FROM #tempsub1 Info
|
|
INNER JOIN #tempsub2 Pending_commands
|
|
ON Info.publisher_db = Pending_commands.publisher_db
|
|
AND Info.publication = Pending_commands.publication
|
|
AND Info.Subscriber = Pending_commands.subscriber
|
|
AND Info.subscriber_db = Pending_commands.subscriber_db
|
|
LEFT OUTER JOIN msdb..sysjobs jobs
|
|
ON Info.job_id = jobs.job_id
|
|
INNER JOIN
|
|
(
|
|
SELECT time,
|
|
agent_id,
|
|
runstatus,
|
|
delivery_latency,
|
|
comments,
|
|
ROW_NUMBER() OVER (PARTITION BY agent_id ORDER BY time DESC) AS pos
|
|
FROM distribution..MSdistribution_history
|
|
) comment
|
|
ON comment.agent_id = Info.id
|
|
WHERE comment.pos = 1;
|
|
END;
|
|
|
|
DROP TABLE #tempsub1; |