Now that the SQL management packs rely heavily on DLLs it’s not easy to determine what the workflows are doing or how they do it. These are the queries used for many of the workflows within this management pack.
-- Microsoft.SQLServer.Replication.Windows.Monitoring (7.0.28.0)
--DistributorPercentOfExpiredSubscriptionsQuery
declare @ParmDefinition nVARCHAR(40);
declare @CountOfSubscription int;
declare @CountOfWrongSubscription int;
SET @ParmDefinition = N'@CountOfSubscriptionOut int OUTPUT';
declare @query nvarchar(max)
SET @query = N'';
SELECT @query = @query + N'SELECT COUNT(*) [COUNTER] from (select [publication_id],[subscriber_id],[subscriber_db] FROM ' + QUOTENAME(name, '"') + '.[dbo].[MSsubscriptions] where subscriber_id > -1 group by [publication_id],[subscriber_id],[subscriber_db]) t UNION ALL '
FROM sys.databases where is_distributor = 1;
declare @query_tail int
SET @query_tail = LEN('UNION ALL')
IF (LEN(@query) > @query_tail)
BEGIN
SET @query = SUBSTRING ( @query ,0 , LEN(@query) - @query_tail )
SET @query = N'SELECT @CountOfSubscriptionOut = SUM([COUNTER]) FROM ('+@query+N') T0';
END
EXECUTE sp_executesql @query, @ParmDefinition, @CountOfSubscriptionOut = @CountOfSubscription OUTPUT;
SELECT @CountOfSubscription AS TotalCount
SET @query = N'';
SELECT @query = @query + N'EXEC ' + QUOTENAME(NAME, '"') + '.sys.sp_replmonitorhelpsubscription @mode = 3, @exclude_anonymous = 0, @refreshpolicy = N''0'', @publication_type = 2;
EXEC ' + QUOTENAME(NAME, '"') + '.sys.sp_replmonitorhelpsubscription @mode = 3, @exclude_anonymous = 0, @refreshpolicy = N''0'', @publication_type = 1;
EXEC ' + QUOTENAME(NAME, '"') + '.sys.sp_replmonitorhelpsubscription @mode = 3, @exclude_anonymous = 0, @refreshpolicy = N''0'', @publication_type = 0;'
FROM sys.databases where is_distributor = 1;
EXECUTE (@query)
--DistributionDbAvailabilityQuery
DECLARE @ResultList TABLE (DatabaseName sysname, [State] bit, ErrorCode int, ErrorDescription nvarchar(max));
DECLARE @dbname sysname, @sql nvarchar(max)
DECLARE temp CURSOR LOCAL FOR
SELECT name from sys.databases where is_distributor = 1;
OPEN temp;
FETCH NEXT FROM temp INTO @dbname;
WHILE @@Fetch_Status=0 BEGIN
SET @sql = N'USE '+ QUOTENAME(@dbname ,'""');
BEGIN TRY
EXEC (@sql);
INSERT INTO @ResultList VALUES (@dbname, 1, 0, '');
END TRY
BEGIN CATCH
INSERT INTO @ResultList VALUES (@dbname, 0, @@ERROR, ERROR_MESSAGE());
END CATCH;
FETCH NEXT FROM temp INTO @dbname;
End
CLOSE temp;
DEALLOCATE temp;
SELECT * FROM @ResultList
--SubscriberSubscriptionsQuery
DECLARE @Databases TABLE (
DatabaseName sysname
)
DECLARE @UnfilteredDatabases TABLE (
DatabaseName sysname
)
INSERT INTO @UnfilteredDatabases SELECT name FROM sys.databases where name not in ('master','model','msdb','tempdb') and is_distributor <> 1
Declare temp Cursor LOCAL For
Select db.DatabaseName
From @UnfilteredDatabases db;
declare @sql nvarchar(max), @dbname sysname, @quotedName nvarchar(max);
Open temp;
Fetch next From temp into @dbname;
While @@Fetch_Status=0 Begin
set @quotedName = QUOTENAME(@dbname ,'"')
set @sql = N'USE '+ @quotedName;
BEGIN TRY
EXEC (@sql);
insert into @Databases values (@dbname);
END TRY
BEGIN CATCH
END CATCH;
Fetch next From temp into @dbname;
End
Close temp;
Deallocate temp;
declare @query nVARCHAR(MAX);
SET @query = N'DECLARE @SUBSCRIPTION_INFO TABLE (
publisher sysname not null,
publisher_db sysname not null,
publication sysname null,
replication_type int not NULL,
subscription_type int not NULL,
last_updated datetime null,
subscriber_db sysname not null,
update_mode smallint null,
last_sync_status int null,
last_sync_summary sysname null,
last_sync_time datetime null
) ;
DECLARE @COUNTER INT;
SET @COUNTER = 0;
';
select @query = @query + 'BEGIN TRY
USE ' + QUOTENAME(DatabaseName, '"') + '; ' + N'
INSERT INTO @SUBSCRIPTION_INFO exec sp_MSenumsubscriptions @subscription_type = ''both''
SELECT @COUNTER = @COUNTER + COUNT(*) FROM @SUBSCRIPTION_INFO
DELETE FROM @SUBSCRIPTION_INFO;
END TRY
BEGIN CATCH
END CATCH
'
from @Databases
set @query = @query + 'select @COUNTER SubscriberSubscriptionsCount'
EXEC ( @query)
--DistributorPuplicationsQuery
declare @query nvarchar(max)
SET @query = N'';
SELECT @query = @query + N'SELECT COUNT(*) [COUNTER] FROM ' + QUOTENAME(NAME, '"') + '.[dbo].[MSpublications] UNION ALL '
FROM sys.databases where is_distributor = 1;
declare @query_tail int
SET @query_tail = LEN('UNION ALL')
IF (LEN(@query) > @query_tail)
BEGIN
SET @query = SUBSTRING ( @query ,0 , LEN(@query) - @query_tail )
SET @query = N'SELECT SUM([COUNTER]) DistributorPublicationsCount FROM ('+@query+N') T0';
EXEC (@query)
END
--SubscriptionAgentState
DECLARE @Results TABLE (
[State] int,
Description int,
JobId uniqueidentifier,
JobName nvarchar(max),
JobLocation nvarchar(max),
Guid uniqueidentifier
);
IF (SERVERPROPERTY('EngineEdition') = 4)
BEGIN
SELECT * FROM @Results
RETURN
END
declare @job_id uniqueidentifier;
declare @ParmDefinition nVARCHAR(40);
SET @ParmDefinition = N'@job_id_out uniqueidentifier OUTPUT';
declare @query nvarchar(max)
SET @query = N'use '+QUOTENAME(@SUBSCRIPTION_DB, '"')+';
if object_id(''MSmerge_replinfo'', ''U'') is not null
select top 1 @job_id_out = merge_jobid from MSmerge_replinfo where merge_jobid is not null
if object_id(''MSreplication_subscriptions'', ''U'') is not null
select top 1 @job_id_out = cast([agent_id] as uniqueidentifier) from MSreplication_subscriptions where agent_id is not null';
EXECUTE sp_executesql @query, @ParmDefinition, @job_id_out = @job_id OUTPUT;
DECLARE @CALCULATED_DATE_LIST TABLE(schedule_id int,
schedule_uid UNIQUEIDENTIFIER,
enabled INT,
CALCULATED_DATE DATETIME);
declare @schedule_id int;
declare @schedule_uid UNIQUEIDENTIFIER;
declare @enabled int;
declare @freq_type int;
declare @freq_interval int;
declare @freq_subday_type int;
declare @freq_subday_interval int;
declare @freq_relative_interval int;
declare @freq_recurrence_factor int;
declare @active_start_date int;
declare @active_end_date int;
declare @active_start_time int;
declare @active_end_time int;
DECLARE CURRENT_SCHEDULE_CURSOR CURSOR FOR
SELECT schedule.schedule_id
,schedule.schedule_uid
,schedule.enabled
,schedule.freq_type
,schedule.freq_interval
,schedule.freq_subday_type
,schedule.freq_subday_interval
,schedule.freq_relative_interval
,schedule.freq_recurrence_factor
,schedule.active_start_date
,schedule.active_end_date
,schedule.active_start_time
,schedule.active_end_time
FROM msdb.dbo.sysschedules schedule
JOIN msdb.[dbo].[sysjobschedules] jobschedule ON schedule.schedule_id = jobschedule.schedule_id
where jobschedule.job_id = @job_id
OPEN CURRENT_SCHEDULE_CURSOR
FETCH NEXT FROM CURRENT_SCHEDULE_CURSOR
INTO @schedule_id ,
@schedule_uid ,
@enabled ,
@freq_type ,
@freq_interval ,
@freq_subday_type ,
@freq_subday_interval ,
@freq_relative_interval,
@freq_recurrence_factor,
@active_start_date ,
@active_end_date ,
@active_start_time ,
@active_end_time ;
DECLARE @USED_DAYS TABLE ( DAY_NUMBER INT);
DECLARE @START_DATE DATETIME;
DECLARE @END_DATE DATETIME;
DECLARE @NOW DATETIME
SET @NOW = GETDATE();
DECLARE @CURRENT_DAY INT
SET @CURRENT_DAY = DATEPART(DW, @NOW);
declare @PREVIOUS_CALCULATED_DATE DATETIME
SET @PREVIOUS_CALCULATED_DATE = NULL;
declare @PREVIOUS_CALCULATED_TIME DATETIME
SET @PREVIOUS_CALCULATED_TIME = NULL;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @START_DATE = CAST(@active_start_date AS varchar(MAX));
DECLARE @START_SECOND INT
SET @START_SECOND = DATEDIFF(second,0,Stuff(Stuff(right('000000'+Cast(@active_start_time as Varchar),6),3,0,':'),6,0,':'));
SET @START_DATE = DATEADD(SECOND, @START_SECOND, @START_DATE);
SET @END_DATE = CAST(@active_end_date AS varchar(MAX));
DECLARE @END_SECOND INT
SET @END_SECOND = DATEDIFF(second,0,Stuff(Stuff(right('000000'+Cast(@active_end_time as Varchar),6),3,0,':'),6,0,':'));
SET @END_DATE = DATEADD(SECOND, @END_SECOND, @END_DATE);
SET @PREVIOUS_CALCULATED_DATE = NULL;
IF NOT (@enabled = 0 OR @START_DATE > @NOW OR @END_DATE < @NOW)
BEGIN
SET @PREVIOUS_CALCULATED_DATE = @START_DATE;
IF @freq_type = 4 OR --Daily
@freq_type = 8 OR -- Weekly
@freq_type = 16 OR -- Monthly
@freq_type = 32 -- Monthly, relative to freq_interval
BEGIN
-- GLOBAL VARS
DECLARE @ITER_DATE DATETIME
SET @ITER_DATE = @START_DATE;
DECLARE @ITERATOR INT
SET @ITERATOR = 0;
DECLARE @DAY_COUNTER INT
SET @DAY_COUNTER = 0;
DECLARE @WEEK_COUNTER INT
SET @WEEK_COUNTER = 0;
DECLARE @MONTH_COUNTER INT
SET @MONTH_COUNTER = 0;
IF @freq_type = 4 --Daily
BEGIN
SET @DAY_COUNTER = 0;
WHILE @ITER_DATE < @NOW
BEGIN
IF (@DAY_COUNTER % @freq_interval) = 0
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
SET @DAY_COUNTER = @DAY_COUNTER + 1;
SET @ITER_DATE = DATEADD(DAY, 1, @ITER_DATE);
END
END
IF @freq_type = 8 -- Weekly
BEGIN
SET @ITERATOR = 1;
-- FIND USED DAY OF WEEK
WHILE @ITERATOR <= 7
BEGIN
DECLARE @DEGREE_OF_TWO INT
SET @DEGREE_OF_TWO = CAST(POWER(2, @ITERATOR-1) AS INT);
IF @freq_interval & @DEGREE_OF_TWO = @DEGREE_OF_TWO
INSERT INTO @USED_DAYS VALUES(@ITERATOR);
SET @ITERATOR= @ITERATOR + 1;
END
-- LOOP ON ALL DAYS FROM START TO NOW AND CHECK IF CONDITION PASSED
DECLARE @IS_PREVIOUS_RUNS_EXIST BIT
SET @IS_PREVIOUS_RUNS_EXIST = 0;
SET @WEEK_COUNTER = 0;
WHILE @ITER_DATE < @NOW
BEGIN
IF EXISTS (SELECT * FROM @USED_DAYS WHERE DAY_NUMBER = DATEPART(DW, @ITER_DATE))
BEGIN
SET @IS_PREVIOUS_RUNS_EXIST = 1;
IF (@WEEK_COUNTER % @freq_recurrence_factor) = 0
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_PREVIOUS_RUNS_EXIST = 1 AND DATEPART(DW, @ITER_DATE) = 7
SET @WEEK_COUNTER = @WEEK_COUNTER + 1;
SET @ITER_DATE = DATEADD(DAY, 1, @ITER_DATE);
END
-- CLEAR TABLE @USED_DAYS
DELETE FROM @USED_DAYS
END
IF @freq_type = 16 -- Monthly
BEGIN
SET @PREVIOUS_CALCULATED_DATE = NULL
DECLARE @DAYS_IN_MONTH INT;
IF DATEPART(DAY, @START_DATE) > @freq_interval
BEGIN
SET @ITERATOR = 1;
-- FIND FIRST RUN
WHILE @ITERATOR < 3
BEGIN
SET @START_DATE = DATEADD(MONTH, 1, @START_DATE);
SET @DAYS_IN_MONTH = DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@START_DATE),0)))
IF @DAYS_IN_MONTH > @freq_interval
SET @START_DATE = CAST(DATEPART(YEAR, @START_DATE) AS VARCHAR)+ '-' +CAST(DATEPART(MONTH, @START_DATE) AS VARCHAR)+ '-' +CAST(@freq_interval AS VARCHAR) + ' ' + Stuff(Stuff(right('000000'+Cast(@active_start_time as Varchar),6),3,0,':'),6,0,':');
SET @ITERATOR = @ITERATOR + 1;
END
END
ELSE
SET @START_DATE = CAST(DATEPART(YEAR, @START_DATE) AS VARCHAR)+ '-' +CAST(DATEPART(MONTH, @START_DATE) AS VARCHAR)+ '-' +CAST(@freq_interval AS VARCHAR) + ' ' + Stuff(Stuff(right('000000'+Cast(@active_start_time as Varchar),6),3,0,':'),6,0,':');
SET @ITER_DATE = @START_DATE
IF @START_DATE > @NOW
SET @START_DATE = NULL;
SET @PREVIOUS_CALCULATED_DATE = @START_DATE;
SET @MONTH_COUNTER = 0;
WHILE @ITER_DATE < @NOW
BEGIN
SET @DAYS_IN_MONTH = DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0)))
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @DAYS_IN_MONTH > @freq_interval
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
SET @MONTH_COUNTER = @MONTH_COUNTER + 1;
SET @ITER_DATE = DATEADD(MONTH, 1, @ITER_DATE);
END
END
IF @freq_type = 32 -- Monthly, relative to freq_interval
BEGIN
SET @PREVIOUS_CALCULATED_DATE = NULL
--1 = Sunday
--2 = Monday
--3 = Tuesday
--4 = Wednesday
--5 = Thursday
--6 = Friday
--7 = Saturday
--8 = Day
--9 = Weekday
--10 = Weekend day
DECLARE @IS_SUNDAY BIT
SET @IS_SUNDAY = 0;
DECLARE @IS_MONDAY BIT
SET @IS_MONDAY = 0;
DECLARE @IS_TUESDAY BIT
SET @IS_TUESDAY = 0;
DECLARE @IS_WEDNESDAY BIT
SET @IS_WEDNESDAY = 0;
DECLARE @IS_THURSDAY BIT
SET @IS_THURSDAY = 0;
DECLARE @IS_FRIDAY BIT
SET @IS_FRIDAY = 0;
DECLARE @IS_SATURDAY BIT
SET @IS_SATURDAY = 0;
DECLARE @IS_DAY BIT
SET @IS_DAY = 0;
DECLARE @IS_WEEKDAY BIT
SET @IS_WEEKDAY = 0;
DECLARE @IS_WEEKEND_DAY BIT
SET @IS_WEEKEND_DAY = 0;
IF @freq_interval = 1
SET @IS_SUNDAY = 1;
IF @freq_interval = 2
SET @IS_MONDAY = 1;
IF @freq_interval = 3
SET @IS_TUESDAY = 1;
IF @freq_interval = 4
SET @IS_WEDNESDAY = 1;
IF @freq_interval = 5
SET @IS_THURSDAY = 1;
IF @freq_interval = 6
SET @IS_FRIDAY = 1;
IF @freq_interval = 7
SET @IS_SATURDAY = 1;
IF @freq_interval = 8
SET @IS_DAY = 1;
IF @freq_interval = 9
SET @IS_WEEKDAY = 1;
IF @freq_interval = 10
SET @IS_WEEKEND_DAY = 1;
--1 = First
--2 = Second
--4 = Third
--8 = Fourth
--16 = Last
DECLARE @EVERY INT;
DECLARE @IS_LAST BIT
SET @IS_LAST = 0;
IF @freq_relative_interval = 16
SET @IS_LAST = 1
ELSE
SET @EVERY = CAST(LOG(@freq_relative_interval)/LOG(2) AS INT) + 1;
DECLARE @CURRENT_DAY_COUNTER INT
SET @CURRENT_DAY_COUNTER = 0;
SET @MONTH_COUNTER = 0;
DECLARE @PREVIOUS_MONTH INT
SET @PREVIOUS_MONTH = DATEPART(MONTH, @START_DATE);
WHILE @ITER_DATE < @NOW
BEGIN
IF @IS_SUNDAY = 1 AND DATEPART(DW, @ITER_DATE) = 1
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_MONDAY = 1 AND DATEPART(DW, @ITER_DATE) = 2
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_TUESDAY = 1 AND DATEPART(DW, @ITER_DATE) = 3
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_WEDNESDAY = 1 AND DATEPART(DW, @ITER_DATE) = 4
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_THURSDAY = 1 AND DATEPART(DW, @ITER_DATE) = 5
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_FRIDAY = 1 AND DATEPART(DW, @ITER_DATE) = 6
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_SATURDAY = 1 AND DATEPART(DW, @ITER_DATE) = 7
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_DAY = 1
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) = 0 --IF LAST DAY OF MONTH
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @EVERY = DATEPART(DAY, @ITER_DATE)
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
IF @IS_WEEKDAY = 1 AND
(DATEPART(DW, @ITER_DATE) = 2 OR
DATEPART(DW, @ITER_DATE) = 3 OR
DATEPART(DW, @ITER_DATE) = 4 OR
DATEPART(DW, @ITER_DATE) = 5 OR
DATEPART(DW, @ITER_DATE) = 6)
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 3 -- CHECK LAST 3 LFY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_WEEKEND_DAY = 1 AND
(DATEPART(DW, @ITER_DATE) = 1 OR
DATEPART(DW, @ITER_DATE) = 7)
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 6 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
SET @ITER_DATE = DATEADD(DAY, 1, @ITER_DATE);
IF @PREVIOUS_MONTH <> DATEPART(MONTH, @ITER_DATE)
SET @MONTH_COUNTER = @MONTH_COUNTER + 1;
END
END
IF @PREVIOUS_CALCULATED_DATE IS NOT NULL AND @freq_subday_type <> 1 -- ONCE AT DAY
BEGIN
DECLARE @MULTIPLIER INT
SET @MULTIPLIER = @freq_subday_interval;
IF CAST(FLOOR(CAST(@NOW AS FLOAT)) AS DATETIME) = CAST(FLOOR(CAST(@PREVIOUS_CALCULATED_DATE AS FLOAT)) AS DATETIME)
BEGIN
DECLARE @NOW_SECONDS INT
SET @NOW_SECONDS = DATEDIFF(SECOND, CAST(FLOOR(CAST(@NOW AS FLOAT)) AS DATETIME), @NOW)
IF @NOW_SECONDS < @END_SECOND
SET @END_SECOND = @NOW_SECONDS
END
IF @freq_subday_type = 4
SET @MULTIPLIER = @freq_subday_interval*60;
IF @freq_subday_type = 8
SET @MULTIPLIER = @freq_subday_interval*3600;
SET @PREVIOUS_CALCULATED_DATE = DATEADD(SECOND, (@END_SECOND - @START_SECOND)/@MULTIPLIER*@MULTIPLIER, @PREVIOUS_CALCULATED_DATE);
END
END
END
INSERT INTO @CALCULATED_DATE_LIST VALUES(@schedule_id, @schedule_uid, @enabled, @PREVIOUS_CALCULATED_DATE);
FETCH NEXT FROM CURRENT_SCHEDULE_CURSOR
INTO @schedule_id ,
@schedule_uid ,
@enabled ,
@freq_type ,
@freq_interval ,
@freq_subday_type ,
@freq_subday_interval ,
@freq_relative_interval,
@freq_recurrence_factor,
@active_start_date ,
@active_end_date ,
@active_start_time ,
@active_end_time ;
END
CLOSE CURRENT_SCHEDULE_CURSOR;
DEALLOCATE CURRENT_SCHEDULE_CURSOR;
DECLARE @job_is_continuous int
SET @job_is_continuous = CASE
WHEN EXISTS(
SELECT 1 FROM msdb.dbo.sysjobschedules sjs
JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = sjs.schedule_id
WHERE ss.enabled = 1 AND ss.freq_type = 64 AND sjs.job_id = @job_id
) THEN 1
ELSE 0
END
DECLARE @XpJobList TABLE (
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)
DECLARE @can_see_all_running_jobs INT
DECLARE @job_owner sysname
SELECT @can_see_all_running_jobs = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
IF (@can_see_all_running_jobs = 0)
BEGIN
declare @table_role table (rolecount int null);
declare @query_role nvarchar(max)
set @query_role = 'USE msdb; ' +
'SELECT ISNULL(IS_MEMBER(N''SQLAgentReaderRole''), 0)'
insert into @table_role exec (@query_role)
SELECT @can_see_all_running_jobs = rolecount from @table_role
END
SELECT @job_owner = SUSER_SNAME()
IF (SELECT value from sys.configurations where name = 'Agent XPs') = 1
BEGIN
IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
INSERT INTO @XpJobList
EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @job_id
ELSE
INSERT INTO @XpJobList
EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner
END
--Get Job history entries
DECLARE @run_date_outcome datetime
DECLARE @run_duration_outcome int
DECLARE @run_status_outcome int
DECLARE @run_date datetime
DECLARE @run_duration int
DECLARE @run_end_date datetime
DECLARE @run_status int
SELECT TOP 1
@run_date_outcome = sjso.run_date_outcome
,@run_duration_outcome = sjso.run_duration_outcome
,@run_status_outcome = sjso.run_status_outcome
,@run_date = sja.run_date
,@run_duration = sja.run_duration
,@run_status = sja.run_status
FROM
(
SELECT TOP 1
sjs.job_id
,CASE
WHEN (last_run_date IS NOT NULL AND last_run_time IS NOT NULL) AND (last_run_date <> 0) THEN msdb.dbo.agent_datetime(last_run_date,last_run_time)
ELSE NULL
END run_date_outcome
,(CASE
--hours, minutes and seconds
WHEN LEN(last_run_duration) > 4 THEN CONVERT(VARCHAR(4),LEFT(last_run_duration,LEN(last_run_duration)-4)) * 3600
+ LEFT(RIGHT(last_run_duration,4),2) * 60 + RIGHT(last_run_duration,2)
--minutes and seconds
WHEN LEN(last_run_duration) = 4 THEN LEFT(last_run_duration,2) * 60 + RIGHT(last_run_duration,2)
WHEN LEN(last_run_duration) = 3 THEN LEFT(last_run_duration,1) * 60 + RIGHT(last_run_duration,2)
ELSE --only seconds
RIGHT(last_run_duration,2)
END) run_duration_outcome
,sjs.last_run_outcome run_status_outcome
FROM msdb.dbo.sysjobservers sjs
WHERE last_run_duration is not null AND sjs.job_id = @job_id
) sjso
OUTER APPLY
(
SELECT TOP 1
msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) run_date
,(CASE
--hours, minutes and seconds
WHEN LEN(run_duration) > 4 THEN CONVERT(VARCHAR(4),LEFT(run_duration,LEN(run_duration)-4)) * 3600
+ LEFT(RIGHT(run_duration,4),2) * 60 + RIGHT(run_duration,2)
--minutes and seconds
WHEN LEN(run_duration) = 4 THEN LEFT(run_duration,2) * 60 + RIGHT(run_duration,2)
WHEN LEN(run_duration) = 3 THEN LEFT(run_duration,1) * 60 + RIGHT(run_duration,2)
ELSE --only seconds
RIGHT(run_duration,2)
END) run_duration
,sjh.run_status
FROM msdb.dbo.sysjobhistory sjh
WHERE sjh.job_id = sjso.job_id
AND msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) > dateadd(SECOND, sjso.run_duration_outcome, sjso.run_date_outcome)
ORDER BY sjh.run_date desc, run_time desc, run_duration desc
) sja
DECLARE @agent_is_running int
DECLARE @current_step int
DECLARE @retry_attempt int
DECLARE @xp_job_state int
SELECT TOP 1
@agent_is_running = xjl.running
,@current_step = CASE xjl.current_step
WHEN 0 THEN NULL
ELSE xjl.current_step
END
,@retry_attempt = xjl.current_retry_attempt
,@xp_job_state = xjl.job_state
FROM @XpJobList xjl
WHERE xjl.job_id = @job_id
DECLARE @JOB_NAME nvarchar(max)
DECLARE @SCHEDULE_ENABLED int
DECLARE @JOB_ENABLED int
DECLARE @JOB_LOCATION nVARCHAR(MAX)
DECLARE @CALCULATED_DATE datetime
DECLARE @current_job_state int
DECLARE @current_job_description int
DECLARE @current_date datetime
SET @current_date = GETDATE()
SELECT TOP 1
@JOB_NAME = job.name
,@SCHEDULE_ENABLED = JS.enabled
,@JOB_ENABLED = job.enabled
,@JOB_LOCATION = @@SERVERNAME
,@CALCULATED_DATE = JS.CALCULATED_DATE
FROM [msdb].[dbo].[sysjobs] job
LEFT JOIN (SELECT JS.job_id,MAX(enabled) enabled, MAX(CALCULATED_DATE) CALCULATED_DATE
FROM msdb.[dbo].[sysjobschedules] JS
JOIN @CALCULATED_DATE_LIST LIST ON LIST.schedule_id = JS.schedule_id
GROUP BY JS.job_id) JS ON job.job_id = JS.job_id
LEFT JOIN msdb.dbo.sysjobservers jobservers ON job.job_id = jobservers.job_id
where job.job_id = @job_id
SET @current_job_state = 4--'Unknown'
SET @current_job_description = 11--'Unknown'
--IF @job_id = '374B974A-1EFB-4D60-82E1-4136828F8FE8'
-- SET @current_job_description = 11
IF @JOB_ENABLED = 0
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 3--'Job is disabled'
END
ELSE IF @SCHEDULE_ENABLED = 0 AND @JOB_ENABLED = 1
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 12--'Job is enabled but schedule is disabled'
END
ELSE IF @job_is_continuous = 1
IF @agent_is_running = 1 --continuous job is running
BEGIN
SET @current_job_state = CASE @xp_job_state
WHEN 3 THEN 2--'Warning'
ELSE 3--'Healhy'
END
SET @current_job_description = CASE @xp_job_state
WHEN 3 THEN 10--'Job is retry'
ELSE 2--'Job is running'
END
END
ELSE
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 15--'Agent is not running'
END
--end of continuous jobs processing
ELSE
BEGIN
--DECLARE @curr_time_last_run_is_valid bit
--SET @curr_time_last_run_is_valid = 0
--IF @run_end_date IS NOT NULL
-- IF ABS(datediff(MINUTE,@run_end_date,@current_date)) <= @estimated_job_duration
-- SET @curr_time_last_run_is_valid = 1
IF @SCHEDULE_ENABLED IS NULL
BEGIN
IF (@run_status_outcome IS NULL) AND (@CALCULATED_DATE IS NULL) AND ((@run_date IS NULL) OR (@run_end_date IS NOT NULL /*AND @curr_time_last_run_is_valid = 0*/))
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 9--'Job exist but never run and has not shcedule'
END
END
ELSE IF @SCHEDULE_ENABLED = 1
BEGIN
IF (@run_date_outcome IS NULL) OR (@run_date IS NOT NULL /*AND @curr_time_last_run_is_valid = 0*/)
IF(@CALCULATED_DATE IS NOT NULL)
BEGIN
SET @current_job_state = 1--'Error'
SET @current_job_description = 4--'Job never run but job schedule order to run'
END
ELSE
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 5--'Job never run'
END
ELSE
BEGIN
IF (@CALCULATED_DATE IS NULL) AND ((@run_date_outcome IS NULL) OR (@run_date IS NOT NULL /*AND @curr_time_last_run_is_valid = 0*/))
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 6--'Possibly Job expired, please, check configuration of job scheduler'
END
ELSE
BEGIN
DECLARE @last_outcome_is_according_schedule bit
SET @last_outcome_is_according_schedule = 0;
IF (@run_date_outcome IS NOT NULL) AND (@CALCULATED_DATE IS NOT NULL)
IF ABS(datediff(MINUTE, @run_date_outcome,@CALCULATED_DATE)) <= @estimated_job_duration
SET @last_outcome_is_according_schedule = 1
IF @last_outcome_is_according_schedule = 1
BEGIN
SET @current_job_state = CASE @run_status_outcome
WHEN 1 THEN 3--'Healhy'
WHEN 3 THEN 3--'Healhy' (Job was cancelled)
WHEN 0 THEN 1--'Error'
ELSE 4--'Unknown'
END
SET @current_job_description = CASE @run_status_outcome
WHEN 1 THEN 7--'Job is successfully done according to the schedule'
WHEN 3 THEN 7--'Job is successfully done according to the schedule' (but was cancelled)
WHEN 0 THEN 1--'Job failed'
ELSE 11--'Unknown'
END
END
ELSE
BEGIN
DECLARE @first_step_begin_date datetime;
DECLARE @last_job_execution_end_date datetime
IF @run_date_outcome IS NOT NULL AND @run_duration_outcome IS NOT NULL
SET @last_job_execution_end_date = dateadd(second,@run_duration_outcome,@run_date_outcome)
SET @first_step_begin_date =(
SELECT TOP 1
msdb.dbo.agent_datetime(run_date, run_time)
FROM msdb.dbo.sysjobhistory
WHERE msdb.dbo.agent_datetime(run_date, run_time) > @last_job_execution_end_date
ORDER BY run_date, run_time
)
--process jobs in progress
IF (@run_date IS NOT NULL) AND (@run_date > @CALCULATED_DATE) /*AND (@curr_time_last_run_is_valid = 1)*/
BEGIN
DECLARE @can_use_last_outcome_status bit
SET @can_use_last_outcome_status = 0
IF @first_step_begin_date IS NOT NULL AND @run_end_date IS NOT NULL
IF abs(datediff(minute,@first_step_begin_date,@run_end_date)) <= @estimated_job_duration
SET @can_use_last_outcome_status = 1
IF @can_use_last_outcome_status = 1 AND @run_status_outcome IS NOT NULL
BEGIN
--state will be correct ONLY if previous outcome executed according to the schedule
SET @current_job_state = CASE @run_status_outcome
WHEN 1 THEN 3--'Healhy'
WHEN 3 THEN 2--'Warning' (Job was cancelled)
WHEN 0 THEN 1--'Error'
ELSE 4--'Unknown'
END
SET @current_job_description = CASE @run_status_outcome
WHEN 1 THEN 17--'Last job execution succeeded'
WHEN 3 THEN 18--'Last job execution was stopped'
WHEN 0 THEN 19--'Last job execution failed'
ELSE 11--'Unknown'
END
END
ELSE
BEGIN
SET @current_job_state = CASE @run_status
WHEN 2 THEN 2--'Warning'
ELSE 3--'Healhy'
END
SET @current_job_description = CASE @run_status
WHEN 2 THEN 10--'Job is retry'
ELSE 2--'Job is running'
END
END
END
ELSE
BEGIN
SET @current_job_state = CASE @run_status_outcome
WHEN 0 THEN 1--'Error'
WHEN 1 THEN 2--'Warning'
WHEN 3 THEN 2--'Warning'
ELSE 4--'Unknown'
END
SET @current_job_description = CASE @run_status_outcome
WHEN 0 THEN 13--'Job is failed and last run time less than presume the schedule'
WHEN 1 THEN 8--'Job is successfully done but last run time less than presume the schedule'
WHEN 3 THEN 14--'Job was cancelled and last run time less than presume the schedule'
ELSE 11--'Unknown'
END
END
END
END
END
END
END
INSERT INTO @Results
SELECT
@current_job_state AS [State]
,@current_job_description AS Description
,@job_id AS JobId
,@JOB_NAME AS JobName
,@JOB_LOCATION AS JobLocation
,@ID AS Guid;
SELECT * FROM @Results;
--GetSecurablesServerConfig
DECLARE @xmlDoc XML = CAST(@SecurableConfigXml AS XML);
DECLARE @TargetSecurables TABLE(
[Name] NVARCHAR(128)
,[PermissionType] NVARCHAR(30)
,[SecurableType] INT
,[SqlCondition] INT
);
INSERT INTO @TargetSecurables
SELECT
xc.value('./name[1]', 'NVARCHAR(128)') AS [Name]
,xc.value('./pt[1]', 'NVARCHAR(30)') AS [PermissionType]
,xc.value('./st[1]', 'INT') AS [SecurableType]
,xc.value('./cn[1]', 'INT') AS [SqlCondition]
FROM @xmlDoc.nodes('/*/dmv') AS xt(xc);
DECLARE @IsHadrEnabled INT = CAST(SERVERPROPERTY('IsHadrEnabled') AS INT);
WITH src AS(
SELECT
tObj.[Name]
,tObj.SecurableType
,CASE tObj.SecurableType
WHEN 0 THEN HAS_PERMS_BY_NAME(NULL, NULL, tObj.[Name])
ELSE HAS_PERMS_BY_NAME(tObj.[Name], 'OBJECT', tObj.PermissionType)
END AS HasPermission
FROM @TargetSecurables tObj
WHERE tObj.SqlCondition = 0 OR (tObj.SqlCondition = 1 AND @IsHadrEnabled = 1)
)
SELECT
src.[Name] AS [Name]
,SecurableType AS [SecurableType]
FROM src
WHERE src.HasPermission = 0;
--SubscriptionPendingCmdQuery
DECLARE @query NVARCHAR(max);
SET @query = N'
DECLARE @replication_servers TABLE (
server_id INT NOT NULL
,[name] SYSNAME NOT NULL
,[data_source] NVARCHAR(4000)
);
DECLARE @data TABLE (
Publisher NVARCHAR(4000)
,Subscriber NVARCHAR(4000)
,PublisherDb SYSNAME
,SubscriberDb SYSNAME
,Publication SYSNAME
,PendingCmdCount INT NULL
);
DECLARE @temp TABLE (pendingcmdcount INT NULL);
DECLARE @agent_id INT
,@xact_seqno VARBINARY(16)
,@subscriber_db SYSNAME
,@publisher_db SYSNAME
,@publication SYSNAME
,@publisher NVARCHAR(4000)
,@subscriber NVARCHAR(4000)
,@pendingcmdcount INT;
DECLARE @in_subscriber NVARCHAR(4000) = ' + QUOTENAME(@subscriber, '''') + ';';
SELECT @query = @query + N'
USE ' + QUOTENAME([name]) + N';
DELETE
FROM @replication_servers;
IF OBJECT_ID(N''dbo.MSreplservers'') IS NOT NULL
INSERT INTO @replication_servers
SELECT srvid
,srvname
,[data_source]
FROM dbo.MSreplservers AS r
INNER JOIN [master].sys.servers AS s ON s.[name] = r.srvname COLLATE SQL_Latin1_General_CP1_CI_AS;
ELSE
INSERT INTO @replication_servers
SELECT server_id
,[name]
,[data_source]
FROM [master].sys.servers;
DECLARE INFO_CURSOR CURSOR
FOR
SELECT a.id
,ISNULL(h.xact_seqno, 0x0)
,p.[data_source]
,s.[data_source]
,a.publication
,a.publisher_db
,a.subscriber_db
FROM dbo.MSdistribution_agents a
JOIN dbo.MSdistribution_history h ON a.id = h.agent_id
LEFT JOIN @replication_servers p ON p.server_id = a.publisher_id
LEFT JOIN @replication_servers s ON s.server_id = a.subscriber_id
WHERE s.[data_source] = @in_subscriber COLLATE SQL_Latin1_General_CP1_CI_AS
AND a.subscriber_id > - 1
AND h.[timestamp] = (
SELECT MAX(mts)
FROM (
SELECT MAX([timestamp]) mts
FROM dbo.MSdistribution_history
WHERE agent_id = a.id
AND runstatus IN (2, 3, 4)
GROUP BY start_time
) AS T
)
OPEN INFO_CURSOR;
FETCH NEXT
FROM INFO_CURSOR
INTO @agent_id
,@xact_seqno
,@publisher
,@subscriber
,@publication
,@publisher_db
,@subscriber_db;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @temp
EXEC dbo.sp_MSget_repl_commands @agent_id = @agent_id
,@last_xact_seqno = @xact_seqno
,@get_count = 2
,@compatibility_level = 9000000;
SELECT @pendingcmdcount = pendingcmdcount
FROM @temp;
DELETE
FROM @temp;
INSERT INTO @data
VALUES (
@publisher
,@in_subscriber
,@publisher_db
,@subscriber_db
,@publication
,@pendingcmdcount
);
FETCH NEXT
FROM INFO_CURSOR
INTO @agent_id
,@xact_seqno
,@publisher
,@subscriber
,@publication
,@publisher_db
,@subscriber_db;
END
CLOSE INFO_CURSOR;
DEALLOCATE INFO_CURSOR;'
FROM [master].sys.databases
WHERE is_distributor = 1;
SET @query = @query + N'
SELECT *
FROM @data;';
EXEC (@query);
--DistributorFindSubscriptionsWithAgents
DECLARE @query NVARCHAR(max);
SET @query = N'
DECLARE @agents TABLE (
Subscriber NVARCHAR(4000)
,SubscriberDb SYSNAME
,Publisher NVARCHAR(4000)
,PublisherDb SYSNAME
,PublicationName SYSNAME
,AgentCategory NVARCHAR(20)
,DistributionDb SYSNAME
,Guid UNIQUEIDENTIFIER
);
DECLARE @replication_servers TABLE (
server_id INT NOT NULL
,[name] SYSNAME NOT NULL
,[data_source] NVARCHAR(4000)
);';
SELECT @query = @query + N'
USE ' + QUOTENAME([name]) + N';
DELETE
FROM @replication_servers;
IF OBJECT_ID(N''dbo.MSreplservers'') IS NOT NULL
INSERT INTO @replication_servers
SELECT srvid
,srvname
,[data_source]
FROM dbo.MSreplservers AS r
INNER JOIN [master].sys.servers AS s ON s.[name] = r.srvname COLLATE SQL_Latin1_General_CP1_CI_AS;
ELSE
INSERT INTO @replication_servers
SELECT server_id
,[name]
,[data_source]
FROM [master].sys.servers;
INSERT INTO @agents
SELECT s.[data_source] AS [Subscriber]
,a.subscriber_db AS [SubscriberDb]
,p.[data_source] AS [Publisher]
,a.publisher_db AS [PublisherDb]
,a.[publication] AS [PublicationName]
,''Distribution'' AS [AgentCategory]
,DB_NAME() AS [DistributionDb]
,NEWID() AS [Guid]
FROM dbo.MSdistribution_agents a
INNER JOIN @replication_servers s ON a.subscriber_id = s.server_id
INNER JOIN @replication_servers p ON a.publisher_id = p.server_id
WHERE a.local_job = 0;
INSERT INTO @agents
SELECT s.[data_source] AS [Subscriber]
,a.subscriber_db AS [SubscriberDb]
,p.[data_source] AS [Publisher]
,a.publisher_db AS [PublisherDb]
,a.[publication] AS [PublicationName]
,''Merge'' AS [AgentCategory]
,DB_NAME() AS [DistributionDb]
,NEWID() AS [Guid]
FROM dbo.MSmerge_agents a
INNER JOIN @replication_servers s ON a.subscriber_id = s.server_id
INNER JOIN @replication_servers p ON a.publisher_id = p.server_id
WHERE a.local_job = 0;'
FROM [master].sys.databases
WHERE is_distributor = 1;
SET @query = @query + N'
SELECT *
FROM @agents;';
EXEC (@query);
--GetAgentServiceStatus
SELECT
startup_type AS [StartupType]
,[status] AS [Status]
FROM sys.dm_server_services
WHERE [filename] LIKE '"%\Binn\SQLAGENT.EXE" -i%'
--ReplicationAgentsStateQuery
DECLARE @query NVARCHAR(max);
SET @query = N'
DECLARE @replication_servers TABLE (
server_id INT NOT NULL
,[name] SYSNAME NOT NULL
,[data_source] NVARCHAR(4000)
);
DECLARE @data TABLE (
job_id UNIQUEIDENTIFIER
,job_name NVARCHAR(MAX)
,job_location NVARCHAR(MAX)
,distributor SYSNAME
,distribution_db SYSNAME
,publiher NVARCHAR(MAX)
,publication_db NVARCHAR(MAX)
,subscriber NVARCHAR(MAX)
,subscription_db NVARCHAR(MAX)
,publication_name NVARCHAR(MAX)
,agent_name NVARCHAR(MAX)
);';
SELECT @query = @query + N'
USE ' + QUOTENAME([name]) + N';
DELETE
FROM @replication_servers;
IF OBJECT_ID(N''dbo.MSreplservers'') IS NOT NULL
INSERT INTO @replication_servers
SELECT srvid
,srvname
,[data_source]
FROM dbo.MSreplservers AS r
INNER JOIN [master].sys.servers AS s ON s.[name] = r.srvname COLLATE SQL_Latin1_General_CP1_CI_AS;
ELSE
INSERT INTO @replication_servers
SELECT server_id
,[name]
,[data_source]
FROM [master].sys.servers;
INSERT INTO @data
SELECT a.job_id
,a.[name]
,@@SERVERNAME
,@@SERVERNAME
,DB_NAME()
,p.[data_source]
,ss.publisher_db
,s.[data_source]
,ss.subscriber_db
,pp.publication
,''Distribution''
FROM dbo.MSdistribution_agents a
LEFT JOIN (
SELECT publication_id
,subscriber_id
,subscriber_db
,MAX(agent_id) agent_id
,MAX(publisher_db) publisher_db
,MAX(publisher_id) publisher_id
FROM dbo.MSsubscriptions
GROUP BY publication_id
,subscriber_id
,subscriber_db
) ss ON a.id = ss.agent_id
INNER JOIN dbo.MSpublications pp ON pp.publication_id = ss.publication_id
INNER JOIN @replication_servers p ON p.server_id = ss.publisher_id
LEFT JOIN @replication_servers s ON s.server_id = ss.subscriber_id
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
UNION ALL
SELECT a.job_id
,a.[name]
,@@SERVERNAME
,@@SERVERNAME
,DB_NAME()
,p.[data_source]
,pp.publisher_db
,NULL
,NULL
,pp.publication
,''Log reader''
FROM dbo.MSpublications pp
INNER JOIN dbo.MSlogreader_agents a ON a.publisher_id = pp.publisher_id
AND a.publisher_db = pp.publisher_db
AND pp.publication_type = 0 --Transactional
INNER JOIN @replication_servers p ON p.server_id = pp.publisher_id
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
UNION ALL
SELECT a.job_id
,a.[name]
,@@SERVERNAME
,@@SERVERNAME
,DB_NAME()
,NULL
,NULL
,NULL
,NULL
,NULL
,''Queue reader''
FROM dbo.MSqreader_agents a
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
UNION ALL
SELECT a.job_id
,a.[name]
,@@SERVERNAME
,@@SERVERNAME
,DB_NAME()
,p.[data_source]
,pp.publisher_db
,NULL
,NULL
,pp.publication
,''Snapshot''
FROM dbo.MSsnapshot_agents a
INNER JOIN dbo.MSpublications pp ON pp.publisher_id = a.publisher_id
AND pp.publisher_db = a.publisher_db
AND pp.publication = a.publication
INNER JOIN @replication_servers p ON p.server_id = pp.publisher_id
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
UNION ALL
SELECT a.job_id
,a.[name]
,@@SERVERNAME
,@@SERVERNAME
,DB_NAME()
,p.[data_source]
,a.publisher_db
,s.[data_source]
,a.subscriber_db
,a.publication
,''Merge''
FROM dbo.MSmerge_agents a
INNER JOIN @replication_servers p ON p.server_id = a.publisher_id
LEFT JOIN @replication_servers s ON s.server_id = a.subscriber_id
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id;'
FROM sys.databases
WHERE is_distributor = 1;
SET @query = @query + N'
SELECT *
FROM @data;';
DECLARE @JOB_LIST TABLE (
job_id UNIQUEIDENTIFIER
,job_name NVARCHAR(MAX)
,job_location NVARCHAR(MAX)
,distributor SYSNAME
,distribution_db SYSNAME
,publiher NVARCHAR(MAX)
,publication_db NVARCHAR(MAX)
,subscriber NVARCHAR(MAX)
,subscription_db NVARCHAR(MAX)
,publication_name NVARCHAR(MAX)
,agent_name NVARCHAR(MAX)
);
INSERT INTO @JOB_LIST exec (@query)
DECLARE @CALCULATED_DATE_LIST TABLE(schedule_id int,
schedule_uid UNIQUEIDENTIFIER,
enabled INT,
CALCULATED_DATE DATETIME);
declare @schedule_id int;
declare @schedule_uid UNIQUEIDENTIFIER;
declare @enabled int;
declare @freq_type int;
declare @freq_interval int;
declare @freq_subday_type int;
declare @freq_subday_interval int;
declare @freq_relative_interval int;
declare @freq_recurrence_factor int;
declare @active_start_date int;
declare @active_end_date int;
declare @active_start_time int;
declare @active_end_time int;
DECLARE CURRENT_SCHEDULE_CURSOR CURSOR FOR
SELECT schedule.schedule_id
,schedule.schedule_uid
,schedule.enabled
,schedule.freq_type
,schedule.freq_interval
,schedule.freq_subday_type
,schedule.freq_subday_interval
,schedule.freq_relative_interval
,schedule.freq_recurrence_factor
,schedule.active_start_date
,schedule.active_end_date
,schedule.active_start_time
,schedule.active_end_time
FROM msdb.dbo.sysschedules schedule
JOIN msdb.[dbo].[sysjobschedules] jobschedule ON schedule.schedule_id = jobschedule.schedule_id
WHERE jobschedule.job_id IN (SELECT DISTINCT job_id FROM @JOB_LIST)
OPEN CURRENT_SCHEDULE_CURSOR
FETCH NEXT FROM CURRENT_SCHEDULE_CURSOR
INTO @schedule_id ,
@schedule_uid ,
@enabled ,
@freq_type ,
@freq_interval ,
@freq_subday_type ,
@freq_subday_interval ,
@freq_relative_interval,
@freq_recurrence_factor,
@active_start_date ,
@active_end_date ,
@active_start_time ,
@active_end_time ;
DECLARE @USED_DAYS TABLE ( DAY_NUMBER INT);
DECLARE @START_DATE DATETIME;
DECLARE @END_DATE DATETIME;
DECLARE @NOW DATETIME
SET @NOW = GETDATE();
DECLARE @CURRENT_DAY INT
SET @CURRENT_DAY = DATEPART(DW, @NOW);
declare @PREVIOUS_CALCULATED_DATE DATETIME
SET @PREVIOUS_CALCULATED_DATE = NULL;
declare @PREVIOUS_CALCULATED_TIME DATETIME
SET @PREVIOUS_CALCULATED_TIME = NULL;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @START_DATE = CAST(@active_start_date AS varchar(MAX));
DECLARE @START_SECOND INT
SET @START_SECOND = DATEDIFF(second,0,Stuff(Stuff(right('000000'+Cast(@active_start_time as Varchar),6),3,0,':'),6,0,':'));
SET @START_DATE = DATEADD(SECOND, @START_SECOND, @START_DATE);
SET @END_DATE = CAST(@active_end_date AS varchar(MAX));
DECLARE @END_SECOND INT
SET @END_SECOND = DATEDIFF(second,0,Stuff(Stuff(right('000000'+Cast(@active_end_time as Varchar),6),3,0,':'),6,0,':'));
SET @END_DATE = DATEADD(SECOND, @END_SECOND, @END_DATE);
SET @PREVIOUS_CALCULATED_DATE = NULL;
IF NOT (@enabled = 0 OR @START_DATE > @NOW OR @END_DATE < @NOW)
BEGIN
SET @PREVIOUS_CALCULATED_DATE = @START_DATE;
IF @freq_type = 4 OR --Daily
@freq_type = 8 OR -- Weekly
@freq_type = 16 OR -- Monthly
@freq_type = 32 -- Monthly, relative to freq_interval
BEGIN
-- GLOBAL VARS
DECLARE @ITER_DATE DATETIME
SET @ITER_DATE = @START_DATE;
DECLARE @ITERATOR INT
SET @ITERATOR = 0;
DECLARE @DAY_COUNTER INT
SET @DAY_COUNTER = 0;
DECLARE @WEEK_COUNTER INT
SET @WEEK_COUNTER = 0;
DECLARE @MONTH_COUNTER INT
SET @MONTH_COUNTER = 0;
IF @freq_type = 4 --Daily
BEGIN
SET @DAY_COUNTER = 0;
WHILE @ITER_DATE < @NOW
BEGIN
IF (@DAY_COUNTER % @freq_interval) = 0
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
SET @DAY_COUNTER = @DAY_COUNTER + 1;
SET @ITER_DATE = DATEADD(DAY, 1, @ITER_DATE);
END
END
IF @freq_type = 8 -- Weekly
BEGIN
SET @ITERATOR = 1;
-- FIND USED DAY OF WEEK
WHILE @ITERATOR <= 7
BEGIN
DECLARE @DEGREE_OF_TWO INT
SET @DEGREE_OF_TWO = CAST(POWER(2, @ITERATOR-1) AS INT);
IF @freq_interval & @DEGREE_OF_TWO = @DEGREE_OF_TWO
INSERT INTO @USED_DAYS VALUES(@ITERATOR);
SET @ITERATOR= @ITERATOR + 1;
END
-- LOOP ON ALL DAYS FROM START TO NOW AND CHECK IF CONDITION PASSED
DECLARE @IS_PREVIOUS_RUNS_EXIST BIT
SET @IS_PREVIOUS_RUNS_EXIST = 0;
SET @WEEK_COUNTER = 0;
WHILE @ITER_DATE < @NOW
BEGIN
IF EXISTS (SELECT * FROM @USED_DAYS WHERE DAY_NUMBER = DATEPART(DW, @ITER_DATE))
BEGIN
SET @IS_PREVIOUS_RUNS_EXIST = 1;
IF (@WEEK_COUNTER % @freq_recurrence_factor) = 0
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_PREVIOUS_RUNS_EXIST = 1 AND DATEPART(DW, @ITER_DATE) = 7
SET @WEEK_COUNTER = @WEEK_COUNTER + 1;
SET @ITER_DATE = DATEADD(DAY, 1, @ITER_DATE);
END
-- CLEAR TABLE @USED_DAYS
DELETE FROM @USED_DAYS
END
IF @freq_type = 16 -- Monthly
BEGIN
SET @PREVIOUS_CALCULATED_DATE = NULL
DECLARE @DAYS_IN_MONTH INT;
IF DATEPART(DAY, @START_DATE) > @freq_interval
BEGIN
SET @ITERATOR = 1;
-- FIND FIRST RUN
WHILE @ITERATOR < 3
BEGIN
SET @START_DATE = DATEADD(MONTH, 1, @START_DATE);
SET @DAYS_IN_MONTH = DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@START_DATE),0)))
IF @DAYS_IN_MONTH > @freq_interval
SET @START_DATE = CAST(DATEPART(YEAR, @START_DATE) AS VARCHAR)+ '-' +CAST(DATEPART(MONTH, @START_DATE) AS VARCHAR)+ '-' +CAST(@freq_interval AS VARCHAR) + ' ' + Stuff(Stuff(right('000000'+Cast(@active_start_time as Varchar),6),3,0,':'),6,0,':');
SET @ITERATOR = @ITERATOR + 1;
END
END
ELSE
SET @START_DATE = CAST(DATEPART(YEAR, @START_DATE) AS VARCHAR)+ '-' +CAST(DATEPART(MONTH, @START_DATE) AS VARCHAR)+ '-' +CAST(@freq_interval AS VARCHAR) + ' ' + Stuff(Stuff(right('000000'+Cast(@active_start_time as Varchar),6),3,0,':'),6,0,':');
SET @ITER_DATE = @START_DATE
IF @START_DATE > @NOW
SET @START_DATE = NULL;
SET @PREVIOUS_CALCULATED_DATE = @START_DATE;
SET @MONTH_COUNTER = 0;
WHILE @ITER_DATE < @NOW
BEGIN
SET @DAYS_IN_MONTH = DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0)))
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @DAYS_IN_MONTH > @freq_interval
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
SET @MONTH_COUNTER = @MONTH_COUNTER + 1;
SET @ITER_DATE = DATEADD(MONTH, 1, @ITER_DATE);
END
END
IF @freq_type = 32 -- Monthly, relative to freq_interval
BEGIN
SET @PREVIOUS_CALCULATED_DATE = NULL
--1 = Sunday
--2 = Monday
--3 = Tuesday
--4 = Wednesday
--5 = Thursday
--6 = Friday
--7 = Saturday
--8 = Day
--9 = Weekday
--10 = Weekend day
DECLARE @IS_SUNDAY BIT
SET @IS_SUNDAY = 0;
DECLARE @IS_MONDAY BIT
SET @IS_MONDAY = 0;
DECLARE @IS_TUESDAY BIT
SET @IS_TUESDAY = 0;
DECLARE @IS_WEDNESDAY BIT
SET @IS_WEDNESDAY = 0;
DECLARE @IS_THURSDAY BIT
SET @IS_THURSDAY = 0;
DECLARE @IS_FRIDAY BIT
SET @IS_FRIDAY = 0;
DECLARE @IS_SATURDAY BIT
SET @IS_SATURDAY = 0;
DECLARE @IS_DAY BIT
SET @IS_DAY = 0;
DECLARE @IS_WEEKDAY BIT
SET @IS_WEEKDAY = 0;
DECLARE @IS_WEEKEND_DAY BIT
SET @IS_WEEKEND_DAY = 0;
IF @freq_interval = 1
SET @IS_SUNDAY = 1;
IF @freq_interval = 2
SET @IS_MONDAY = 1;
IF @freq_interval = 3
SET @IS_TUESDAY = 1;
IF @freq_interval = 4
SET @IS_WEDNESDAY = 1;
IF @freq_interval = 5
SET @IS_THURSDAY = 1;
IF @freq_interval = 6
SET @IS_FRIDAY = 1;
IF @freq_interval = 7
SET @IS_SATURDAY = 1;
IF @freq_interval = 8
SET @IS_DAY = 1;
IF @freq_interval = 9
SET @IS_WEEKDAY = 1;
IF @freq_interval = 10
SET @IS_WEEKEND_DAY = 1;
--1 = First
--2 = Second
--4 = Third
--8 = Fourth
--16 = Last
DECLARE @EVERY INT;
DECLARE @IS_LAST BIT
SET @IS_LAST = 0;
IF @freq_relative_interval = 16
SET @IS_LAST = 1
ELSE
SET @EVERY = CAST(LOG(@freq_relative_interval)/LOG(2) AS INT) + 1;
DECLARE @CURRENT_DAY_COUNTER INT
SET @CURRENT_DAY_COUNTER = 0;
SET @MONTH_COUNTER = 0;
DECLARE @PREVIOUS_MONTH INT
SET @PREVIOUS_MONTH = DATEPART(MONTH, @START_DATE);
WHILE @ITER_DATE < @NOW
BEGIN
IF @IS_SUNDAY = 1 AND DATEPART(DW, @ITER_DATE) = 1
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_MONDAY = 1 AND DATEPART(DW, @ITER_DATE) = 2
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_TUESDAY = 1 AND DATEPART(DW, @ITER_DATE) = 3
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_WEDNESDAY = 1 AND DATEPART(DW, @ITER_DATE) = 4
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_THURSDAY = 1 AND DATEPART(DW, @ITER_DATE) = 5
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_FRIDAY = 1 AND DATEPART(DW, @ITER_DATE) = 6
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_SATURDAY = 1 AND DATEPART(DW, @ITER_DATE) = 7
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 7 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_DAY = 1
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) = 0 --IF LAST DAY OF MONTH
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @EVERY = DATEPART(DAY, @ITER_DATE)
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
IF @IS_WEEKDAY = 1 AND
(DATEPART(DW, @ITER_DATE) = 2 OR
DATEPART(DW, @ITER_DATE) = 3 OR
DATEPART(DW, @ITER_DATE) = 4 OR
DATEPART(DW, @ITER_DATE) = 5 OR
DATEPART(DW, @ITER_DATE) = 6)
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 3 -- CHECK LAST 3 LFY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
IF @IS_WEEKEND_DAY = 1 AND
(DATEPART(DW, @ITER_DATE) = 1 OR
DATEPART(DW, @ITER_DATE) = 7)
IF @IS_LAST = 1 AND (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND (DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@ITER_DATE),0))) - DATEPART(DAY, @ITER_DATE)) < 6 --IF DAYS TO END OF MONTH < 7
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
ELSE
BEGIN
SET @CURRENT_DAY_COUNTER = @CURRENT_DAY_COUNTER + 1;
IF (@MONTH_COUNTER % @freq_recurrence_factor) = 0 AND @CURRENT_DAY_COUNTER = @EVERY
SET @PREVIOUS_CALCULATED_DATE = @ITER_DATE;
END
SET @ITER_DATE = DATEADD(DAY, 1, @ITER_DATE);
IF @PREVIOUS_MONTH <> DATEPART(MONTH, @ITER_DATE)
SET @MONTH_COUNTER = @MONTH_COUNTER + 1;
END
END
IF @PREVIOUS_CALCULATED_DATE IS NOT NULL AND @freq_subday_type <> 1 -- ONCE AT DAY
BEGIN
DECLARE @MULTIPLIER INT
SET @MULTIPLIER = @freq_subday_interval;
IF CAST(FLOOR(CAST(@NOW AS FLOAT)) AS DATETIME) = CAST(FLOOR(CAST(@PREVIOUS_CALCULATED_DATE AS FLOAT)) AS DATETIME)
BEGIN
DECLARE @NOW_SECONDS INT
SET @NOW_SECONDS = DATEDIFF(SECOND, CAST(FLOOR(CAST(@NOW AS FLOAT)) AS DATETIME), @NOW)
IF @NOW_SECONDS < @END_SECOND
SET @END_SECOND = @NOW_SECONDS
END
IF @freq_subday_type = 4
SET @MULTIPLIER = @freq_subday_interval*60;
IF @freq_subday_type = 8
SET @MULTIPLIER = @freq_subday_interval*3600;
SET @PREVIOUS_CALCULATED_DATE = DATEADD(SECOND, (@END_SECOND - @START_SECOND)/@MULTIPLIER*@MULTIPLIER, @PREVIOUS_CALCULATED_DATE);
END
END
END
INSERT INTO @CALCULATED_DATE_LIST VALUES(@schedule_id, @schedule_uid, @enabled, @PREVIOUS_CALCULATED_DATE);
FETCH NEXT FROM CURRENT_SCHEDULE_CURSOR
INTO @schedule_id ,
@schedule_uid ,
@enabled ,
@freq_type ,
@freq_interval ,
@freq_subday_type ,
@freq_subday_interval ,
@freq_relative_interval,
@freq_recurrence_factor,
@active_start_date ,
@active_end_date ,
@active_start_time ,
@active_end_time ;
END
CLOSE CURRENT_SCHEDULE_CURSOR;
DEALLOCATE CURRENT_SCHEDULE_CURSOR;
DECLARE @JobSatusList TABLE(
job_id uniqueidentifier
,run_date_outcome datetime
,run_duration_outcome int
,run_status_outcome int
,run_date datetime
,run_duration int
,run_status int
)
INSERT INTO @JobSatusList
SELECT
sjso.job_id
,sjso.run_date_outcome
,sjso.run_duration_outcome
,sjso.run_status_outcome
,sja.run_date
,sja.run_duration
,sja.run_status
FROM
(
SELECT
sjs.job_id
,CASE
WHEN (last_run_date IS NOT NULL AND last_run_time IS NOT NULL) AND (last_run_date <> 0) THEN msdb.dbo.agent_datetime(last_run_date,last_run_time)
ELSE NULL
END run_date_outcome
,(CASE
--hours, minutes and seconds
WHEN LEN(last_run_duration) > 4 THEN CONVERT(VARCHAR(4),LEFT(last_run_duration,LEN(last_run_duration)-4)) * 3600
+ LEFT(RIGHT(last_run_duration,4),2) * 60 + RIGHT(last_run_duration,2)
--minutes and seconds
WHEN LEN(last_run_duration) = 4 THEN LEFT(last_run_duration,2) * 60 + RIGHT(last_run_duration,2)
WHEN LEN(last_run_duration) = 3 THEN LEFT(last_run_duration,1) * 60 + RIGHT(last_run_duration,2)
ELSE --only seconds
RIGHT(last_run_duration,2)
END) run_duration_outcome
,sjs.last_run_outcome run_status_outcome
FROM msdb.dbo.sysjobservers sjs
WHERE last_run_duration is not null
) sjso
OUTER APPLY
(
SELECT TOP 1
msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) run_date
,(CASE
--hours, minutes and seconds
WHEN LEN(run_duration) > 4 THEN CONVERT(VARCHAR(4),LEFT(run_duration,LEN(run_duration)-4)) * 3600
+ LEFT(RIGHT(run_duration,4),2) * 60 + RIGHT(run_duration,2)
--minutes and seconds
WHEN LEN(run_duration) = 4 THEN LEFT(run_duration,2) * 60 + RIGHT(run_duration,2)
WHEN LEN(run_duration) = 3 THEN LEFT(run_duration,1) * 60 + RIGHT(run_duration,2)
ELSE --only seconds
RIGHT(run_duration,2)
END) run_duration
,sjh.run_status
FROM msdb.dbo.sysjobhistory sjh
WHERE sjh.job_id = sjso.job_id
AND (sjso.run_date_outcome IS NULL OR msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) > dateadd(SECOND, sjso.run_duration_outcome, sjso.run_date_outcome))
ORDER BY sjh.run_date desc, run_time desc, run_duration desc
) sja
DECLARE @Continuous_Job_Id_List TABLE(job_id uniqueidentifier)
INSERT INTO @Continuous_Job_Id_List
SELECT
DISTINCT(sjs.job_id)
FROM msdb.dbo.sysjobschedules sjs
JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = sjs.schedule_id
WHERE ss.enabled = 1 AND ss.freq_type = 64
DECLARE @XpJobList TABLE (
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)
DECLARE @can_see_all_running_jobs INT
DECLARE @job_owner sysname
SELECT @can_see_all_running_jobs = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
IF (@can_see_all_running_jobs = 0)
BEGIN
declare @table_role table (rolecount int null);
declare @query_role nvarchar(max)
set @query_role = 'USE msdb; ' +
'SELECT ISNULL(IS_MEMBER(N''SQLAgentReaderRole''), 0)'
insert into @table_role exec (@query_role)
SELECT @can_see_all_running_jobs = rolecount from @table_role
END
SELECT @job_owner = SUSER_SNAME()
IF (SELECT value from sys.configurations where name = 'Agent XPs') = 1
BEGIN
INSERT INTO @XpJobList
EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner
END
DECLARE @Job_State_Output TABLE(
[State] int
,[Description] int
,JobId uniqueidentifier
,JobName nvarchar(max)
,JobLocation nvarchar(max)
,Distributor sysname
,DistributionDb sysname
,Publisher nvarchar(max)
,PublicationDb nvarchar(max)
,Subscriber nvarchar(max)
,SubscriptionDb nvarchar(max)
,PublicationName nvarchar(max)
,AgentName nvarchar(max)
)
DECLARE @job_id uniqueidentifier
DECLARE @job_name nvarchar(max)
DECLARE @agent_name nvarchar(max)
DECLARE @SCHEDULE_ENABLED int
DECLARE @JOB_ENABLED int
DECLARE @job_location nVARCHAR(MAX)
DECLARE @distributor SYSNAME
DECLARE @distribution_db sysname
DECLARE @publiher nVARCHAR(MAX)
DECLARE @publication_name nVARCHAR(MAX)
DECLARE @publication_db nVARCHAR(MAX)
DECLARE @subscriber nVARCHAR(MAX)
DECLARE @subscription_db nVARCHAR(MAX)
DECLARE @run_date_outcome datetime
DECLARE @run_duration_outcome int
DECLARE @run_status_outcome int
DECLARE @run_date datetime
DECLARE @run_duration int
DECLARE @run_end_date datetime
DECLARE @run_status int
DECLARE @CALCULATED_DATE datetime
DECLARE @job_is_continuous int
DECLARE @xp_job_state int
DECLARE @xp_running int
DECLARE @xp_current_step int
DECLARE @xp_current_retry_attempt int
DECLARE @current_job_state int
DECLARE @current_job_description int
DECLARE @current_date datetime
SET @current_date = GETDATE()
DECLARE job_state_source CURSOR FOR
SELECT DISTINCT
JL.job_id
,JL.job_name
,JL.agent_name
,JS.enabled SCHEDULE_ENABLED
,jobs.enabled JOB_ENABLED
,JL.job_location
,JL.distributor
,JL.distribution_db
,JL.publiher
,JL.publication_name
,JL.publication_db
,JL.subscriber
,JL.subscription_db
,jsl.run_date_outcome
,jsl.run_duration_outcome
,jsl.run_status_outcome
,jsl.run_date
,jsl.run_duration
,CASE
WHEN jsl.run_date IS NOT NULL AND jsl.run_duration IS NOT NULL THEN dateadd(second,jsl.run_duration,jsl.run_date)
ELSE NULL
END run_end_date
,jsl.run_status
,JS.CALCULATED_DATE
,CASE
when cj.Continuous is not null then 1
else 0
END job_is_continuous
,xpjl.job_state xp_job_state
,xpjl.running xp_running
,xpjl.current_step xp_current_step
,xpjl.current_retry_attempt xp_current_retry_attempt
FROM @JOB_LIST JL
LEFT JOIN (SELECT JS.job_id,MAX(enabled) enabled, MAX(CALCULATED_DATE) CALCULATED_DATE
FROM msdb.[dbo].[sysjobschedules] JS
JOIN @CALCULATED_DATE_LIST LIST ON LIST.schedule_id = JS.schedule_id
GROUP BY JS.job_id) JS ON JL.job_id = JS.job_id
LEFT JOIN msdb.[dbo].[sysjobs] jobs ON JL.job_id = jobs.job_id
LEFT JOIN msdb.dbo.sysjobservers jobservers ON JL.job_id = jobservers.job_id
LEFT JOIN @JobSatusList jsl ON JL.job_id = jsl.job_id
JOIN @XpJobList xpjl ON JL.job_id = xpjl.job_id
OUTER APPLY (SELECT 1 AS Continuous FROM @Continuous_Job_Id_List jil WHERE jil.job_id = JL.job_id) cj
OPEN job_state_source
FETCH NEXT FROM job_state_source
INTO
@job_id,
@job_name,
@agent_name,
@SCHEDULE_ENABLED,
@JOB_ENABLED,
@job_location,
@distributor,
@distribution_db,
@publiher,
@publication_name,
@publication_db,
@subscriber,
@subscription_db,
@run_date_outcome,
@run_duration_outcome,
@run_status_outcome,
@run_date,
@run_duration,
@run_end_date,
@run_status,
@CALCULATED_DATE,
@job_is_continuous,
@xp_job_state,
@xp_running,
@xp_current_step,
@xp_current_retry_attempt;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @current_job_state = 4--'Unknown'
SET @current_job_description = 11--'Unknown'
IF @JOB_ENABLED = 0
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 3--'Job is disabled'
END
ELSE IF @SCHEDULE_ENABLED = 0 AND @JOB_ENABLED = 1
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 12--'Job is enabled but schedule is disabled'
END
ELSE IF @job_is_continuous = 1
BEGIN
IF @xp_running = 0 --continuous job is stopped
BEGIN
IF @agent_name != 'Snapshot'
BEGIN
SET @current_job_state = CASE @run_status_outcome
WHEN 0 THEN 1--'Error'
ELSE 2--'Warning'
END
SET @current_job_description = CASE @run_status_outcome
WHEN 0 THEN 1--'Job Failed'
ELSE 15--'Agent is not running'
END
END
ELSE
BEGIN
SET @current_job_state = CASE @run_status_outcome
WHEN 1 THEN 3--'Healthy'
WHEN 0 THEN 1--'Error'
ELSE 3--'Healthy'
END
SET @current_job_description = CASE @run_status_outcome
WHEN 1 THEN 2--'Job is running'
WHEN 0 THEN 1--'Job failed'
ELSE 2--'Job is running'
END
END
END
ELSE
BEGIN
IF @xp_job_state = 3
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 10--'Job is retry'
END
ELSE
BEGIN
SET @current_job_state = 3--'Healhy'
SET @current_job_description = 2--'Job is running'
END
END
END
--end of continuous job processing
ELSE
BEGIN
--DECLARE @curr_time_last_run_is_valid bit
--SET @curr_time_last_run_is_valid = 0
--IF @run_end_date IS NOT NULL
-- IF ABS(datediff(MINUTE,@run_end_date,@current_date)) <= @estimated_job_duration
-- SET @curr_time_last_run_is_valid = 1
IF @SCHEDULE_ENABLED IS NULL
BEGIN
IF (@run_status_outcome IS NULL) AND (@CALCULATED_DATE IS NULL) AND @run_date IS NULL
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 9--'Job exist but never run and has not shcedule'
END
END
ELSE IF @SCHEDULE_ENABLED = 1
BEGIN
IF (@run_date_outcome IS NULL) AND (@run_date IS NULL)
IF(@CALCULATED_DATE IS NOT NULL)
BEGIN
SET @current_job_state = 1--'Error'
SET @current_job_description = 4--'Job never run but job schedule order to run'
END
ELSE
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 5--'Job never run'
END
ELSE
BEGIN
IF (@CALCULATED_DATE IS NULL) AND (@run_date_outcome IS NULL) AND (@run_date IS NULL)
BEGIN
SET @current_job_state = 2--'Warning'
SET @current_job_description = 6--'Possibly Job expired, please, check configuration of job scheduler'
END
ELSE
BEGIN
DECLARE @last_outcome_is_according_schedule bit
SET @last_outcome_is_according_schedule = 0;
IF (@run_date_outcome IS NOT NULL) AND (@CALCULATED_DATE IS NOT NULL)
IF ABS(datediff(MINUTE, @run_date_outcome,@CALCULATED_DATE)) <= @estimated_job_duration
SET @last_outcome_is_according_schedule = 1
IF @last_outcome_is_according_schedule = 1
BEGIN
SET @current_job_state = CASE @run_status_outcome
WHEN 1 THEN 3--'Healhy'
WHEN 3 THEN 3--'Healhy' (Job was cancelled)
WHEN 0 THEN 1--'Error'
ELSE 4--'Unknown'
END
SET @current_job_description = CASE @run_status_outcome
WHEN 1 THEN 7--'Job is successfuly done according to the schedule'
WHEN 3 THEN 7--'Job is successfuly done according to the schedule' (but was cancelled)
WHEN 0 THEN 1--'Job failed'
ELSE 11--'Unknown'
END
END
ELSE
BEGIN
DECLARE @first_step_begin_date datetime;
DECLARE @last_job_execution_end_date datetime
IF @run_date_outcome IS NOT NULL AND @run_duration_outcome IS NOT NULL
SET @last_job_execution_end_date = dateadd(second,@run_duration_outcome,@run_date_outcome)
SET @first_step_begin_date =(
SELECT TOP 1
msdb.dbo.agent_datetime(run_date, run_time)
FROM msdb.dbo.sysjobhistory
WHERE msdb.dbo.agent_datetime(run_date, run_time) > @last_job_execution_end_date
ORDER BY run_date, run_time
)
--process jobs in progress
IF (@run_date IS NOT NULL) AND (@run_date > @CALCULATED_DATE) /*AND (@curr_time_last_run_is_valid = 1)*/
BEGIN
DECLARE @can_use_last_outcome_status bit
SET @can_use_last_outcome_status = 0
IF @first_step_begin_date IS NOT NULL AND @run_end_date IS NOT NULL
IF abs(datediff(minute,@first_step_begin_date,@run_end_date)) <= @estimated_job_duration
SET @can_use_last_outcome_status = 1
IF @can_use_last_outcome_status = 1 AND @run_status_outcome IS NOT NULL
BEGIN
--state will be correct ONLY if previous outcome executed according to the schedule
SET @current_job_state = CASE @run_status_outcome
WHEN 1 THEN 3--'Healhy'
WHEN 3 THEN 2--'Warning' (Job was cancelled)
WHEN 0 THEN 1--'Error'
ELSE 4--'Unknown'
END
SET @current_job_description = CASE @run_status_outcome
WHEN 1 THEN 17--'Last job execution succeeded'
WHEN 3 THEN 18--'Last job execution was stopped'
WHEN 0 THEN 19--'Last job execution failed'
ELSE 11--'Unknown'
END
END
ELSE
BEGIN
SET @current_job_state = CASE @run_status
WHEN 2 THEN 2--'Warning'
ELSE 3--'Healhy'
END
SET @current_job_description = CASE @run_status
WHEN 2 THEN 10--'Job is retry'
ELSE 2--'Job is running'
END
END
END
ELSE
BEGIN
SET @current_job_state = CASE @run_status_outcome
WHEN 0 THEN 1--'Error'
WHEN 1 THEN 2--'Warning'
WHEN 3 THEN 2--'Warning'
ELSE 4--'Unknown'
END
SET @current_job_description = CASE @run_status_outcome
WHEN 0 THEN 13--'Job is failed and last run time less than presume the schedule'
WHEN 1 THEN 8--'Job is successfuly done but last run time less than presume the schedule'
WHEN 3 THEN 14--'Job was cancelled and last run time less than presume the schedule'
ELSE 11--'Unknown'
END
END
END
END
END
END
END
INSERT INTO @Job_State_Output
VALUES(
@current_job_state
,@current_job_description
,@job_id
,@job_name
,@job_location
,@distributor
,@distribution_db
,@publiher
,@publication_db
,@subscriber
,@subscription_db
,@publication_name
,@agent_name
)
FETCH NEXT FROM job_state_source
INTO
@job_id,
@job_name,
@agent_name,
@SCHEDULE_ENABLED,
@JOB_ENABLED,
@job_location,
@distributor,
@distribution_db,
@publiher,
@publication_name,
@publication_db,
@subscriber,
@subscription_db,
@run_date_outcome,
@run_duration_outcome,
@run_status_outcome,
@run_date,
@run_duration,
@run_end_date,
@run_status,
@CALCULATED_DATE,
@job_is_continuous,
@xp_job_state,
@xp_running,
@xp_current_step,
@xp_current_retry_attempt;
END
CLOSE job_state_source;
DEALLOCATE job_state_source;
select * from @Job_State_Output
--DistributorFailJobsQuery
DECLARE @sDelimiter VARCHAR(1)
SET @sDelimiter = ','
DECLARE @sItem nVARCHAR(max)
DECLARE @List TABLE (NAME nVARCHAR(max), IS_POSITIVE bit)
WHILE CHARINDEX(@sDelimiter,@sInputPositiveList,0) <> 0
BEGIN
SET @sItem = RTRIM(LTRIM(SUBSTRING(@sInputPositiveList,1,CHARINDEX(@sDelimiter,@sInputPositiveList,0)-1)));
SET @sInputPositiveList = RTRIM(LTRIM(SUBSTRING(@sInputPositiveList,CHARINDEX(@sDelimiter,@sInputPositiveList,0)+LEN(@sDelimiter),LEN(@sInputPositiveList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem, 1
END
IF LEN(@sInputPositiveList) > 0
INSERT INTO @List SELECT @sInputPositiveList, 1
WHILE CHARINDEX(@sDelimiter,@sInputNegativeList,0) <> 0
BEGIN
SET @sItem = RTRIM(LTRIM(SUBSTRING(@sInputNegativeList,1,CHARINDEX(@sDelimiter,@sInputNegativeList,0)-1)));
SET @sInputNegativeList = RTRIM(LTRIM(SUBSTRING(@sInputNegativeList,CHARINDEX(@sDelimiter,@sInputNegativeList,0)+LEN(@sDelimiter),LEN(@sInputNegativeList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem, 0
END
IF LEN(@sInputNegativeList) > 0
INSERT INTO @List SELECT @sInputNegativeList, 0
select JobName, ExecDate from (
SELECT
max(jobsInfo.name) as JobName
,max(jobsInfo.run_status) RunStatus
,count(jobsInfo.job_id) RunCount
,max(jobsInfo.exec_date) ExecDate
FROM (
SELECT job.name
,history.job_id
,history.step_id
,history.sql_message_id
,history.sql_severity
,history.run_status
,((SUBSTRING(CAST(history.run_date AS VARCHAR(8)), 5, 2) + '/'
+ SUBSTRING(CAST(history.run_date AS VARCHAR(8)), 7, 2) + '/'
+ SUBSTRING(CAST(history.run_date AS VARCHAR(8)), 1, 4) + ' '
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(history.run_time AS varchar)))
+ CAST(history.run_time AS VARCHAR)), 1, 2) + ':'
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(history.run_time AS VARCHAR)))
+ CAST(history.run_time AS VARCHAR)), 3, 2) + ':'
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(history.run_time as varchar)))
+ CAST(history.run_time AS VARCHAR)), 5, 2))) AS exec_date
,history.run_duration
,history.retries_attempted
,history.server
FROM msdb.dbo.sysjobs job
join msdb.dbo.syscategories cat on cat.category_id = job.category_id
CROSS apply ( SELECT top (@n) history.job_id, history.instance_id FROM msdb.dbo.sysjobhistory history where history.job_id = job.job_id AND step_id = 0 order by run_date desc , run_time desc ) ca
join msdb.dbo.sysjobhistory history on ca.instance_id = history.instance_id
where
(cat.name IN(SELECT CASE WHEN NAME LIKE 'REPL-%' THEN NAME ELSE 'REPL-' + NAME END FROM @List where IS_POSITIVE = 1) or (NOT EXISTS(SELECT 1 FROM @List where IS_POSITIVE = 1) AND cat.name LIKE 'REPL-%')) and
cat.name not IN(SELECT CASE WHEN NAME LIKE 'REPL-%' THEN NAME ELSE 'REPL-' + NAME END FROM @List where IS_POSITIVE = 0)
) AS jobsInfo
group by jobsInfo.job_id
) T
where RunStatus = 0 and RunCount = @n
--DistributorGetAllPublisherWithWorkingDirectory
DECLARE @query NVARCHAR(max);
SET @query = N'
DECLARE @publishers TABLE (
Publisher NVARCHAR(4000)
,PublisherDb SYSNAME
,PublicationName SYSNAME
,DefaultSnapshotLocation NVARCHAR(255)
);
DECLARE @replication_servers TABLE (
server_id INT NOT NULL
,[name] SYSNAME NOT NULL
,[data_source] NVARCHAR(4000)
);';
SELECT @query = @query + N'
USE ' + QUOTENAME([name]) + N';
DELETE
FROM @replication_servers;
IF OBJECT_ID(N''dbo.MSreplservers'') IS NOT NULL
INSERT INTO @replication_servers
SELECT srvid
,srvname
,[data_source]
FROM dbo.MSreplservers AS r
INNER JOIN [master].sys.servers AS s ON s.[name] = r.srvname COLLATE SQL_Latin1_General_CP1_CI_AS;
ELSE
INSERT INTO @replication_servers
SELECT server_id
,[name]
,[data_source]
FROM [master].sys.servers;
INSERT INTO @publishers
SELECT p.[data_source] Publisher
,publisher_db PublisherDb
,publication PublicationName
,working_directory DefaultSnapshotLocation
FROM dbo.MSpublications pp
INNER JOIN @replication_servers p ON p.server_id = pp.publisher_id
INNER JOIN [msdb].dbo.MSdistpublishers dp ON p.[data_source] = dp.name COLLATE SQL_Latin1_General_CP1_CI_AS;'
FROM [master].sys.databases
WHERE is_distributor = 1;
SET @query = @query + N'
SELECT *
FROM @publishers;';
EXEC (@query);
--DistributorPendingCmdQuery
DECLARE @query NVARCHAR(max);
SET @query = N'
DECLARE @replication_servers TABLE (
server_id INT NOT NULL
,[name] SYSNAME NOT NULL
,[data_source] NVARCHAR(4000)
);
DECLARE @data TABLE (
subscription NVARCHAR(max)
,subscriber NVARCHAR(4000)
,pendingcmdcount INT NULL
);
DECLARE @temp TABLE (pendingcmdcount INT NULL);
DECLARE @agent_id INT
,@xact_seqno VARBINARY(16)
,@subscriber_db SYSNAME
,@publisher_db SYSNAME
,@publication SYSNAME
,@publisher NVARCHAR(4000)
,@subscriber NVARCHAR(4000)
,@subscription NVARCHAR(max)
,@publisher_id INT
,@lastrunts TIMESTAMP
,@pendingcmdcount INT;';
SELECT @query = @query + N'
USE ' + QUOTENAME([name]) + N';
DELETE
FROM @replication_servers;
IF OBJECT_ID(N''dbo.MSreplservers'') IS NOT NULL
INSERT INTO @replication_servers
SELECT srvid
,srvname
,[data_source]
FROM dbo.MSreplservers AS r
INNER JOIN [master].sys.servers AS s ON s.[name] = r.srvname COLLATE SQL_Latin1_General_CP1_CI_AS;
ELSE
INSERT INTO @replication_servers
SELECT server_id
,[name]
,[data_source]
FROM [master].sys.servers;
DECLARE INFO_CURSOR CURSOR
FOR
SELECT a.id
,isnull(h.xact_seqno, 0x0)
,p.[data_source]
,s.[data_source]
,a.publication
,a.publisher_db
,a.subscriber_db
,a.publisher_id
,h.[timestamp]
FROM dbo.MSdistribution_agents a
JOIN dbo.MSdistribution_history h ON a.id = h.agent_id
LEFT JOIN @replication_servers p ON p.server_id = a.publisher_id
LEFT JOIN @replication_servers s ON s.server_id = a.subscriber_id
WHERE a.subscriber_id > - 1
AND h.[timestamp] = (
SELECT max(mts)
FROM (
SELECT max([timestamp]) mts
FROM dbo.MSdistribution_history
WHERE agent_id = a.id
AND runstatus IN (2 ,3 ,4)
GROUP BY start_time
) AS T
)
OPEN INFO_CURSOR;
FETCH NEXT
FROM INFO_CURSOR
INTO @agent_id
,@xact_seqno
,@publisher
,@subscriber
,@publication
,@publisher_db
,@subscriber_db
,@publisher_id
,@lastrunts;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @lastrunts IS NOT NULL
OR NOT EXISTS (
SELECT *
FROM dbo.MSpublications p
JOIN dbo.MSsubscriptions s ON p.publication_id = s.publication_id
WHERE p.publisher_id = @publisher_id
AND p.publisher_db = @publisher_db
AND p.publication = @publication
AND p.immediate_sync = 1
AND s.[status] = 1
AND s.subscriber_id = - 1
)
BEGIN
INSERT INTO @temp
EXEC dbo.sp_MSget_repl_commands @agent_id = @agent_id
,@last_xact_seqno = @xact_seqno
,@get_count = 2
,@compatibility_level = 9000000;
SELECT @pendingcmdcount = pendingcmdcount
FROM @temp;
DELETE
FROM @temp;
SET @subscription = ''['' + @subscriber_db + ''] - '' + ''['' + @publisher + ''].['' + @publisher_db + '']: '' + @publication;
INSERT INTO @data
VALUES (
@subscription
,@subscriber
,@pendingcmdcount
)
END
FETCH NEXT
FROM INFO_CURSOR
INTO @agent_id
,@xact_seqno
,@publisher
,@subscriber
,@publication
,@publisher_db
,@subscriber_db
,@publisher_id
,@lastrunts;
END
CLOSE INFO_CURSOR;
DEALLOCATE INFO_CURSOR;'
FROM [master].sys.databases
WHERE is_distributor = 1;
SET @query = @query + N'
SELECT *
FROM @data
WHERE pendingcmdcount > 0;';
EXEC (@query);
--DistributorSubscriptionsQuery
declare @ParmDefinition nVARCHAR(40);
declare @CountOfSubscription int;
declare @CountOfWrongSubscription int;
SET @ParmDefinition = N'@CountOfSubscriptionOut int OUTPUT';
declare @query nvarchar(max)
SET @query = N'';
SELECT @query = @query + N'SELECT COUNT(*) [COUNTER] from (select [publication_id],[subscriber_id],[subscriber_db] FROM ' + QUOTENAME(NAME, '"') + '.[dbo].[MSsubscriptions] where subscriber_id > -1 group by [publication_id],[subscriber_id],[subscriber_db]) t UNION ALL '
FROM sys.databases where is_distributor = 1;
SELECT @query = @query + N'SELECT COUNT(*) [COUNTER] FROM ' + QUOTENAME(NAME, '"') + '.[dbo].[MSmerge_subscriptions] UNION ALL '
FROM sys.databases where is_distributor = 1;
declare @query_tail int
SET @query_tail = LEN('UNION ALL')
IF (LEN(@query) > @query_tail)
BEGIN
SET @query = SUBSTRING ( @query ,0 , LEN(@query) - @query_tail )
SET @query = N'SELECT @CountOfSubscriptionOut = SUM([COUNTER]) FROM ('+@query+N') T0';
END
EXECUTE sp_executesql @query, @ParmDefinition, @CountOfSubscriptionOut = @CountOfSubscription OUTPUT;
select ISNULL(@CountOfSubscription, 0) DistributorSubscriptionsCount
--SubscriptionLongEstimatedTimeQuery
DECLARE @query NVARCHAR(max);
SET @query = N'
DECLARE @replication_servers TABLE (
server_id INT NOT NULL
,[name] SYSNAME NOT NULL
,[data_source] NVARCHAR(4000)
);
DECLARE @data TABLE (
Publisher NVARCHAR(4000)
,Subscriber NVARCHAR(4000)
,PublisherDb SYSNAME
,SubscriberDb SYSNAME
,Publication SYSNAME
,EstimatedTime INT NULL
);
DECLARE @temp TABLE (pendingcmdcount INT NULL);
DECLARE @agent_id INT
,@xact_seqno VARBINARY(16)
,@subscriber_db SYSNAME
,@publisher_db SYSNAME
,@publication SYSNAME
,@publisher NVARCHAR(4000)
,@subscriber NVARCHAR(4000)
,@delivery_rate float
,@estimatedprocesstime int
,@pendingcmdcount INT;
DECLARE @in_subscriber NVARCHAR(4000) = ' + QUOTENAME(@subscriber, '''') + ';';
SELECT @query = @query + N'
USE ' + QUOTENAME([name]) + N';
DELETE
FROM @replication_servers;
IF OBJECT_ID(N''dbo.MSreplservers'') IS NOT NULL
INSERT INTO @replication_servers
SELECT srvid
,srvname
,[data_source]
FROM dbo.MSreplservers AS r
INNER JOIN [master].sys.servers AS s ON s.[name] = r.srvname COLLATE SQL_Latin1_General_CP1_CI_AS;
ELSE
INSERT INTO @replication_servers
SELECT server_id
,[name]
,[data_source]
FROM [master].sys.servers;
DECLARE INFO_CURSOR CURSOR
FOR
SELECT a.id
,ISNULL(h.xact_seqno, 0x0)
,p.[data_source]
,s.[data_source]
,a.publication
,a.publisher_db
,a.subscriber_db
,ISNULL(h.delivery_rate, 0.0)
FROM dbo.MSdistribution_agents a
JOIN dbo.MSdistribution_history h ON a.id = h.agent_id
LEFT JOIN @replication_servers p ON p.server_id = a.publisher_id
LEFT JOIN @replication_servers s ON s.server_id = a.subscriber_id
WHERE s.[data_source] = @in_subscriber COLLATE SQL_Latin1_General_CP1_CI_AS
AND a.subscriber_id > - 1
AND h.[timestamp] = (
SELECT MAX(mts)
FROM (
SELECT MAX([timestamp]) mts
FROM dbo.MSdistribution_history
WHERE agent_id = a.id
AND runstatus IN (2, 3, 4)
GROUP BY start_time
) AS T
)
OPEN INFO_CURSOR;
FETCH NEXT
FROM INFO_CURSOR
INTO @agent_id
,@xact_seqno
,@publisher
,@subscriber
,@publication
,@publisher_db
,@subscriber_db
,@delivery_rate;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @temp
EXEC dbo.sp_MSget_repl_commands @agent_id = @agent_id
,@last_xact_seqno = @xact_seqno
,@get_count = 2
,@compatibility_level = 9000000;
SELECT @pendingcmdcount = pendingcmdcount
FROM @temp;
DELETE
FROM @temp;
IF (@delivery_rate = 0.0)
BEGIN
SELECT @delivery_rate = ISNULL(AVG(delivery_rate), 0.0)
FROM dbo.MSdistribution_history
WHERE agent_id = @agent_id
END
SELECT @estimatedprocesstime = CASE
WHEN (@delivery_rate != 0.0)
THEN CAST((CAST(@pendingcmdcount AS FLOAT) / @delivery_rate) AS INT)
ELSE @pendingcmdcount
END
INSERT INTO @data
VALUES (
@publisher
,@in_subscriber
,@publisher_db
,@subscriber_db
,@publication
,@estimatedprocesstime
);
FETCH NEXT
FROM INFO_CURSOR
INTO @agent_id
,@xact_seqno
,@publisher
,@subscriber
,@publication
,@publisher_db
,@subscriber_db
,@delivery_rate;
END
CLOSE INFO_CURSOR;
DEALLOCATE INFO_CURSOR;'
FROM [master].sys.databases
WHERE is_distributor = 1;
SET @query = @query + N'
SELECT *
FROM @data;';
EXEC (@query);
--DistributorUnsyncSubscriptionsQuery
DECLARE @query NVARCHAR(max);
SET @query = N'
DECLARE @replication_servers TABLE (
server_id INT NOT NULL
,[name] SYSNAME NOT NULL
,[data_source] NVARCHAR(4000)
);
DECLARE @data TABLE (
Publisher NVARCHAR(4000)
,Subscriber NVARCHAR(4000)
,PublisherDb SYSNAME
,SubscriberDb SYSNAME
,Publication SYSNAME
,PendingCmdCount INT NULL
);
DECLARE @temp TABLE (pendingcmdcount INT NULL);
DECLARE @agent_id INT
,@xact_seqno VARBINARY(16)
,@subscriber_db SYSNAME
,@publisher_db SYSNAME
,@publication SYSNAME
,@publisher NVARCHAR(4000)
,@subscriber NVARCHAR(4000)
,@pendingcmdcount INT;
DECLARE @pend_command_limit INT = ' + CAST(@pendingCommandLimit AS NVARCHAR(MAX)) + ';';
SELECT @query = @query + N'
USE ' + QUOTENAME([name]) + N';
DELETE
FROM @replication_servers;
IF OBJECT_ID(N''dbo.MSreplservers'') IS NOT NULL
INSERT INTO @replication_servers
SELECT srvid
,srvname
,[data_source]
FROM dbo.MSreplservers AS r
INNER JOIN [master].sys.servers AS s ON s.[name] = r.srvname COLLATE SQL_Latin1_General_CP1_CI_AS;
ELSE
INSERT INTO @replication_servers
SELECT server_id
,[name]
,[data_source]
FROM [master].sys.servers;
DECLARE INFO_CURSOR CURSOR
FOR
SELECT a.id
,ISNULL(h.xact_seqno, 0x0) AS xact_seqno
,a.publisher_db
,a.subscriber_db
,a.publication
,p.[data_source] publisher
,s.[data_source] subscriber
FROM dbo.MSdistribution_agents a
INNER JOIN dbo.MSdistribution_history h ON a.id = h.agent_id
INNER JOIN @replication_servers p ON p.server_id = a.publisher_id
INNER JOIN @replication_servers s ON s.server_id = a.subscriber_id
WHERE h.[timestamp] = (
SELECT MAX(mts)
FROM (
SELECT MAX(timestamp) AS mts
FROM dbo.MSdistribution_history
WHERE agent_id = a.id
AND runstatus IN (2 ,3 ,4)
GROUP BY start_time
) AS T
)
OPEN INFO_CURSOR;
FETCH NEXT
FROM INFO_CURSOR
INTO @agent_id
,@xact_seqno
,@publisher_db
,@subscriber_db
,@publication
,@publisher
,@subscriber;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @temp
EXEC sp_MSget_repl_commands @agent_id = @agent_id
,@last_xact_seqno = @xact_seqno
,@get_count = 2
,@compatibility_level = 9000000;
SELECT TOP 1 @pendingcmdcount = pendingcmdcount
FROM @temp;
DELETE
FROM @temp;
IF (@pendingcmdcount > @pend_command_limit)
INSERT INTO @data
VALUES (
@publisher
,@subscriber
,@publisher_db
,@subscriber_db
,@publication
,@pendingcmdcount
)
FETCH NEXT
FROM INFO_CURSOR
INTO @agent_id
,@xact_seqno
,@publisher_db
,@subscriber_db
,@publication
,@publisher
,@subscriber;
END
CLOSE INFO_CURSOR;
DEALLOCATE INFO_CURSOR;'
FROM [master].sys.databases
WHERE is_distributor = 1;
SET @query = @query + N'
SELECT *
FROM @data;';
EXEC (@query);
--DistributorPercentOfDeactivatedSubscriptionsQuery
DECLARE @query NVARCHAR(max);
SET @query = N'
DECLARE @replication_servers TABLE (
server_id INT NOT NULL
,[name] SYSNAME NOT NULL
,[data_source] NVARCHAR(4000)
);
DECLARE @data TABLE (
Publisher NVARCHAR(4000)
,Subscriber NVARCHAR(4000)
,Publication SYSNAME
,PublisherDb SYSNAME
,SubscriberDb SYSNAME
,Status TINYINT NOT NULL
);
DECLARE @total_count INT = 0;
DECLARE @in_status TINYINT = ' + CAST(@status AS NVARCHAR(MAX)) + ';';
SELECT @query = @query + N'
USE ' + QUOTENAME([name]) + N';
DELETE
FROM @replication_servers;
IF OBJECT_ID(N''dbo.MSreplservers'') IS NOT NULL
INSERT INTO @replication_servers
SELECT srvid
,srvname
,[data_source]
FROM dbo.MSreplservers AS r
INNER JOIN [master].sys.servers AS s ON s.[name] = r.srvname COLLATE SQL_Latin1_General_CP1_CI_AS;
ELSE
INSERT INTO @replication_servers
SELECT server_id
,[name]
,[data_source]
FROM [master].sys.servers;
SELECT @total_count = @total_count + COUNT(*)
FROM (
SELECT publication_id
,subscriber_id
,subscriber_db
FROM dbo.MSsubscriptions
WHERE subscriber_id > - 1
GROUP BY publication_id
,subscriber_id
,subscriber_db
) t;
INSERT INTO @data
SELECT p.[data_source]
,s.[data_source]
,pp.publication
,pp.publisher_db
,ss.subscriber_db
,ss.[status]
FROM (
SELECT MIN([status]) [status]
,publication_id
,subscriber_id
,subscriber_db
FROM dbo.MSsubscriptions
WHERE subscriber_id > - 1
GROUP BY publication_id
,subscriber_id
,subscriber_db
) ss
INNER JOIN dbo.MSpublications pp ON ss.publication_id = pp.publication_id
LEFT JOIN @replication_servers p ON p.server_id = pp.publisher_id
LEFT JOIN @replication_servers s ON s.server_id = ss.subscriber_id
WHERE [status] = @in_status;'
FROM [master].sys.databases
WHERE is_distributor = 1;
SET @query = @query + N'
SELECT @total_count AS [TotalCount], d.*
FROM @data d;';
EXEC (@query);
--GetInfoFromPublisherQuery
DECLARE @Databases TABLE (
DatabaseName sysname
)
DECLARE @UnfilteredDatabases TABLE (
DatabaseName sysname
)
DECLARE @dbname sysname, @sql nvarchar(max), @quotedName nvarchar(max);
INSERT INTO @UnfilteredDatabases
SELECT DISTINCT name as DatabaseName FROM
(SELECT name FROM sys.databases WHERE is_published = 1
UNION ALL
SELECT name FROM sys.databases WHERE is_merge_published = 1) a
Declare temp Cursor LOCAL For
Select db.DatabaseName
From @UnfilteredDatabases db;
Open temp;
Fetch next From temp into @dbname;
While @@Fetch_Status=0 Begin
set @quotedName = QUOTENAME(@dbname ,'"')
set @sql = N'USE '+ @quotedName;
BEGIN TRY
EXEC (@sql);
insert into @Databases values (@dbname);
END TRY
BEGIN CATCH
END CATCH;
Fetch next From temp into @dbname;
End
Close temp;
Deallocate temp;
declare @query nvarchar(max)
set @query = 'CREATE TABLE #TranPublTemp_GetPublisherInfoQuery ( ' +
'pubid int, ' +
'name sysname, ' +
'restricted int, ' +
'"status" int, ' +
'task int, ' +
'"replication frequency" int, ' +
'"synchronization method" int, ' +
'"description" nvarchar(max), ' +
'immediate_sync int, ' +
'enabled_for_internet int, ' +
'allow_push int, ' +
'allow_pull int, ' +
'allow_anonymous int, ' +
'independent_agent int, ' +
'immediate_sync_ready int, ' +
'allow_sync_tran int, ' +
'autogen_sync_procs int, ' +
'snapshot_jobid varbinary(255), ' +
'"retention" int, ' +
'"has subscription" int, ' +
'allow_queued_tran int, ' +
'snapshot_in_defaultfolder int, ' +
'alt_snapshot_folder nvarchar(255), ' +
'pre_snapshot_script nvarchar(255), ' +
'post_snapshot_script nvarchar(255), ' +
'compress_snapshot int, ' +
'ftp_address nvarchar(255), ' +
'ftp_port int, ' +
'ftp_subdirectory nvarchar(255), ' +
'ftp_login nvarchar(255), ' +
'allow_dts int, ' +
'allow_subscription_copy int, ' +
'centralized_conflicts int, ' +
'conflict_retention int, ' +
'conflict_policy int, ' +
'queue_type int, ' +
'backward_comp_level int, ' +
'publish_to_AD int, ' +
'allow_initialize_from_backup int, ' +
'replicate_ddl int, ' +
'enabled_for_p2p int, ' +
'publish_local_changes_only int, ' +
'enabled_for_het_sub int, ' +
'enabled_for_p2p_conflictdetection int, ' +
'originator_id int, ' +
'p2p_continue_onconflict int, ' +
'allow_partition_switch int, ' +
'replicate_partition_switch int ' +
'); '
set @query = @query + 'CREATE TABLE #MergePublTemp_GetPublisherInfoQuery ( id int, ' +
'name sysname, ' +
'description nvarchar(max), ' +
'"status" int, ' +
'"retention" int, ' +
'sync_mode int, ' +
'allow_push int, ' +
'allow_pull int, ' +
'allow_anonymous int, ' +
'centralized_conflicts int, ' +
'priority int, ' +
'snapshot_ready int, ' +
'publication_type int, ' +
'pubid nvarchar(255), ' +
'snapshot_jobid varbinary(255), ' +
'enabled_for_internet int, ' +
'dynamic_filters int, ' +
'has_subscription int, ' +
'snapshot_in_defaultfolder int, ' +
'alt_snapshot_folder nvarchar(255), ' +
'pre_snapshot_script nvarchar(max), ' +
'post_snapshot_script nvarchar(max), ' +
'compress_snapshot int, ' +
'ftp_address nvarchar(255), ' +
'ftp_port int, ' +
'ftp_subdirectory nvarchar(255), ' +
'ftp_login nvarchar(255), ' +
'conflict_retention int, ' +
'keep_partition_changes int, ' +
'allow_subscription_copy int, ' +
'allow_synctoalternate int, ' +
'validate_subscriber_info int, ' +
'backward_comp_level int, ' +
'publish_to_activedirectory int, ' +
'max_concurrent_merge int, ' +
'max_concurrent_dynamic_snapshots int, ' +
'use_partition_groups int, ' +
'num_of_articles int, ' +
'replicate_ddl int, ' +
'publication_number int, ' +
'allow_subscriber_initiated_snapshot int, ' +
'allow_web_synchronization int, ' +
'web_synchronization_url nvarchar(255), ' +
'allow_partition_realignment int, ' +
'retention_period_unit int, ' +
'has_downloadonly_articles int, ' +
'decentralized_conflicts int, ' +
'generation_leveling_threshold int, ' +
'automatic_reinitialization_policy int ' +
'); '
set @query = @query + 'DECLARE @temp TABLE ( ' +
'DatabaseName nvarchar(255), ' +
'PublicationName nvarchar(255), ' +
'"Description" nvarchar(max), ' +
'"Type" int, ' +
'"Retention" int, ' +
'RetentionInterval int, ' +
'SynchronizationMethod int, ' +
'AltSnapshotFolder nvarchar(255), ' +
'SnapshotInDefaultFolder int ' +
'); '
select @query = @query + 'BEGIN TRY ' +
'USE ' + QUOTENAME(DatabaseName, '"') + '; ' +
'BEGIN TRY ' +
'INSERT INTO #TranPublTemp_GetPublisherInfoQuery EXEC sp_helppublication; ' +
'END TRY ' +
'BEGIN CATCH ' +
-- to support SQL Server 14 sp2+ schema
'ALTER TABLE #TranPublTemp_GetPublisherInfoQuery ADD allow_drop int; ' +
'INSERT INTO #TranPublTemp_GetPublisherInfoQuery EXEC sp_helppublication; ' +
'END CATCH ' +
'INSERT INTO #MergePublTemp_GetPublisherInfoQuery EXEC sp_helpmergepublication; ' +
'INSERT INTO @temp ' +
'SELECT ' + QUOTENAME(DatabaseName, '''') + ' as DatabaseName, ' +
'name as PublicationName, '+
'"description" as "Description", '+
'"replication frequency" + 1 as "Type", '+
'"retention" as "Retention", '+
'5 as RetentionInterval, '+
'"synchronization method" + 1 as "SynchronizationMethod", '+
'alt_snapshot_folder as AltShapshotFolder, '+
'snapshot_in_defaultfolder as SnapshotInDefaultFolder '+
'FROM #TranPublTemp_GetPublisherInfoQuery '+
'UNION ALL '+
'SELECT ' + QUOTENAME(DatabaseName, '''') + ' as DatabaseName, ' +
'name as PublicationName, '+
'"description" as "Description", '+
'3 as "Type", '+
'"retention" as "Retention", '+
'retention_period_unit + 1 as RetentionInterval, '+
'sync_mode + 1 as "SynchronizationMethod", '+
'alt_snapshot_folder as AltShapshotFolder, '+
'snapshot_in_defaultfolder as SnapshotInDefaultFolder '+
'FROM #MergePublTemp_GetPublisherInfoQuery; '+
'END TRY ' +
'BEGIN CATCH ' +
'END CATCH ' +
'TRUNCATE TABLE #TranPublTemp_GetPublisherInfoQuery; ' +
'TRUNCATE TABLE #MergePublTemp_GetPublisherInfoQuery; '
from @Databases
set @query = @query + 'select @@SERVERNAME Publisher, PublicationName, DatabaseName PublisherDb, AltSnapshotFolder, SnapshotInDefaultFolder from @temp; ' +
'DROP TABLE #TranPublTemp_GetPublisherInfoQuery; ' +
'DROP TABLE #MergePublTemp_GetPublisherInfoQuery; '
exec (@query)
--PublicationSubscriptionSyncStatusQuery
-- Type of publication: 0 = Transactional, 1 = Snapshot, 2 = Merge
DECLARE @query NVARCHAR(MAX);
SET @query = N'
DECLARE @in_publisher NVARCHAR(4000) = ' + QUOTENAME(@publisher, N'''') + N';';
SELECT @query = @query + N'
USE ' + QUOTENAME([name]) + N';
EXEC sys.sp_replmonitorhelpsubscription @publisher = @in_publisher, @publication_type = 2;
EXEC sys.sp_replmonitorhelpsubscription @publisher = @in_publisher, @publication_type = 1;
EXEC sys.sp_replmonitorhelpsubscription @publisher = @in_publisher, @publication_type = 0;'
FROM [master].sys.databases
WHERE is_distributor = 1;
EXEC (@query);
--DistributorAgentsLoadQuery
DECLARE @sDelimiter VARCHAR(1)
SET @sDelimiter = ','
DECLARE @sItem VARCHAR(max)
DECLARE @List TABLE (NAME VARCHAR(max), IS_POSITIVE bit)
WHILE CHARINDEX(@sDelimiter,@sInputPositiveList,0) <> 0
BEGIN
SET @sItem = RTRIM(LTRIM(SUBSTRING(@sInputPositiveList,1,CHARINDEX(@sDelimiter,@sInputPositiveList,0)-1)));
SET @sInputPositiveList = RTRIM(LTRIM(SUBSTRING(@sInputPositiveList,CHARINDEX(@sDelimiter,@sInputPositiveList,0)+LEN(@sDelimiter),LEN(@sInputPositiveList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem, 1
END
IF LEN(@sInputPositiveList) > 0
INSERT INTO @List SELECT @sInputPositiveList, 1
WHILE CHARINDEX(@sDelimiter,@sInputNegativeList,0) <> 0
BEGIN
SET @sItem = RTRIM(LTRIM(SUBSTRING(@sInputNegativeList,1,CHARINDEX(@sDelimiter,@sInputNegativeList,0)-1)));
SET @sInputNegativeList = RTRIM(LTRIM(SUBSTRING(@sInputNegativeList,CHARINDEX(@sDelimiter,@sInputNegativeList,0)+LEN(@sDelimiter),LEN(@sInputNegativeList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem, 0
END
IF LEN(@sInputNegativeList) > 0
INSERT INTO @List SELECT @sInputNegativeList, 0
DECLARE @dateHigherBoundary datetime
SET @dateHigherBoundary = GETDATE()
DECLARE @dateLowerBoundary datetime
SET @dateLowerBoundary = dateadd(HOUR, -@LAST_HOURS, @dateHigherBoundary)
DECLARE @dateSelectedIntervalLength INT
SET @dateSelectedIntervalLength = @LAST_HOURS * 3600
;with jobLastOutcomeDate AS(
SELECT
sji.job_id
,jhi.run_datetime
,CASE
WHEN jhi.run_datetime IS NOT NULL THEN dateadd(SECOND, jhi.run_duration, jhi.run_datetime)
ELSE NULL
END run_end_datetime
,CASE
WHEN jhi.run_datetime IS NULL OR run_duration IS NULL THEN 6
WHEN jhi.run_datetime >= @dateLowerBoundary AND jhi.run_datetime <= @dateHigherBoundary AND dateadd(SECOND, jhi.run_duration, jhi.run_datetime) <= @dateHigherBoundary THEN 1
WHEN jhi.run_datetime < @dateLowerBoundary AND dateadd(SECOND, jhi.run_duration, jhi.run_datetime) <= @dateHigherBoundary AND dateadd(SECOND, jhi.run_duration, jhi.run_datetime) >= @dateLowerBoundary THEN 2
WHEN jhi.run_datetime < @dateLowerBoundary AND dateadd(SECOND, jhi.run_duration, jhi.run_datetime) < @dateLowerBoundary THEN 3
WHEN jhi.run_datetime >= @dateLowerBoundary AND jhi.run_datetime <= @dateHigherBoundary AND dateadd(SECOND, jhi.run_duration, jhi.run_datetime) > @dateHigherBoundary THEN 4
WHEN jhi.run_datetime < @dateLowerBoundary AND dateadd(SECOND, jhi.run_duration, jhi.run_datetime) > @dateHigherBoundary THEN 5
END [Type]
,jhi.instance_id
FROM msdb.dbo.sysjobs sji
outer apply (
SELECT TOP 1
job_id
,msdb.dbo.agent_datetime(run_date, run_time) run_datetime
,(CASE
--hours, minutes and seconds
WHEN LEN(run_duration) > 4 THEN CONVERT(VARCHAR(4),LEFT(run_duration,LEN(run_duration)-4)) * 3600
+ LEFT(RIGHT(run_duration,4),2) * 60 + RIGHT(run_duration,2)
--minutes and seconds
WHEN LEN(run_duration) = 4 THEN LEFT(run_duration,2) * 60 + RIGHT(run_duration,2)
WHEN LEN(run_duration) = 3 THEN LEFT(run_duration,1) * 60 + RIGHT(run_duration,2)
ELSE --only seconds
RIGHT(run_duration,2)
END) run_duration
,instance_id
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0
AND job_id = sji.job_id
ORDER BY run_datetime desc
) jhi
)
SELECT
job.name JobName,
jd.TotalRunDuration
FROM
(
--Get fully executed jobs
SELECT
stat.job_id
,SUM(stat.run_duration) TotalRunDuration
FROM
(
SELECT
job_id
,(CASE
--hours, minutes and seconds
WHEN LEN(history.run_duration) > 4 THEN CONVERT(VARCHAR(4),LEFT(history.run_duration,LEN(history.run_duration)-4)) * 3600
+ LEFT(RIGHT(history.run_duration,4),2) * 60 + RIGHT(history.run_duration,2)
--minutes and seconds
WHEN LEN(history.run_duration) = 4 THEN LEFT(history.run_duration,2) * 60 + RIGHT(history.run_duration,2)
WHEN LEN(history.run_duration) = 3 THEN LEFT(history.run_duration,1) * 60 + RIGHT(history.run_duration,2)
ELSE --only seconds
RIGHT(history.run_duration,2)
END) run_duration
FROM msdb.dbo.sysjobhistory history
WHERE
history.step_id = 0
AND msdb.dbo.agent_datetime(run_date, run_time) >= @dateLowerBoundary
AND msdb.dbo.agent_datetime(run_date, run_time) <= @dateHigherBoundary
UNION ALL
--Get partly executed jobs near the beginning of the interval:
--jobs that began before the beginning of the interval and ended in the given interval
SELECT
job_id
,DATEDIFF(SECOND,@dateLowerBoundary,dateadd(SECOND, run_duration, hist.run_start)) run_duration
FROM (
SELECT
job_id,
instance_id,
msdb.dbo.agent_datetime(run_date, run_time) run_start
,(CASE
--hours, minutes and seconds
WHEN LEN(history.run_duration) > 4 THEN CONVERT(VARCHAR(4),LEFT(history.run_duration,LEN(history.run_duration)-4)) * 3600
+ LEFT(RIGHT(history.run_duration,4),2) * 60 + RIGHT(history.run_duration,2)
--minutes and seconds
WHEN LEN(history.run_duration) = 4 THEN LEFT(history.run_duration,2) * 60 + RIGHT(history.run_duration,2)
WHEN LEN(history.run_duration) = 3 THEN LEFT(history.run_duration,1) * 60 + RIGHT(history.run_duration,2)
ELSE --only seconds
RIGHT(history.run_duration,2)
END) run_duration
FROM msdb.dbo.sysjobhistory history
WHERE
step_id = 0
) hist
WHERE
hist.run_start < @dateLowerBoundary
AND dateadd(SECOND, run_duration, hist.run_start) >= @dateLowerBoundary
UNION ALL
--Get jobs that are still executing
SELECT
job_id
,CASE
WHEN run_datetime < @dateLowerBoundary THEN @dateSelectedIntervalLength
ELSE DATEDIFF(SECOND,run_datetime,@dateHigherBoundary)
END run_duration
FROM
(
SELECT
hist.job_id
,MIN(msdb.dbo.agent_datetime(run_date, run_time)) run_datetime
FROM msdb.dbo.sysjobhistory hist
JOIN jobLastOutcomeDate jlo ON jlo.job_id = hist.job_id AND (jlo.Type = 1 OR jlo.Type = 3 OR jlo.Type = 6)
WHERE
hist.step_id <> 0
AND (((jlo.Type = 1 OR jlo.Type = 3) AND msdb.dbo.agent_datetime(run_date, run_time) > jlo.run_end_datetime)
OR jlo.Type = 6)
AND msdb.dbo.agent_datetime(run_date, run_time) <= @dateHigherBoundary
GROUP BY hist.job_id
) lr
) stat
GROUP BY stat.job_id
) jd
JOIN msdb.dbo.sysjobs job ON job.job_id = jd.job_id
JOIN msdb.dbo.syscategories cat on cat.category_id = job.category_id
OUTER APPLY(
SELECT TOP 1
1 AS Continuous
FROM msdb.dbo.sysjobschedules sjs
join msdb.dbo.sysschedules ss on ss.schedule_id = sjs.schedule_id
where sjs.job_id = jd.job_id AND ss.freq_type = 64
) ft
WHERE
cat.name IN(SELECT CASE WHEN NAME LIKE 'REPL-%' THEN NAME ELSE 'REPL-' + NAME END FROM @List where IS_POSITIVE = 1) and
cat.name not IN(SELECT CASE WHEN NAME LIKE 'REPL-%' THEN NAME ELSE 'REPL-' + NAME END FROM @List where IS_POSITIVE = 0)
and ft.Continuous IS NULL
--PublisherPublicationsQuery
SELECT count(*) as PublisherPublicationsCount FROM
(SELECT name FROM sys.databases WHERE is_published = 1
UNION ALL
SELECT name FROM sys.databases WHERE is_merge_published = 1) a
--PublisherSubscriptionsMonitorQuery
DECLARE @query NVARCHAR(max);
SET @query = N'
USE ' + QUOTENAME(@distributorDb) + N';
DECLARE @replication_servers TABLE (
server_id INT NOT NULL
,[name] SYSNAME NOT NULL
,[data_source] NVARCHAR(4000)
);
DECLARE @in_publisher NVARCHAR(4000) = N' + QUOTENAME(@publisher, '''') + ';
IF OBJECT_ID(N''dbo.MSreplservers'') IS NOT NULL
INSERT INTO @replication_servers
SELECT srvid
,srvname
,[data_source]
FROM dbo.MSreplservers AS r
INNER JOIN [master].sys.servers AS s ON s.[name] = r.srvname COLLATE SQL_Latin1_General_CP1_CI_AS;
ELSE
INSERT INTO @replication_servers
SELECT server_id
,[name]
,[data_source]
FROM [master].sys.servers;
SELECT DISTINCT p.[data_source] AS PublisherName
,s.[data_source] AS SubscriberName
,pp.publication AS PublicationName
,ss.publisher_db AS PublisherDb
,ss.subscriber_db AS SubscriberDb
,(
CASE ss.[status]
WHEN 0
THEN ''Inactive''
WHEN 1
THEN ''Subscribed''
WHEN 2
THEN ''Active''
ELSE ''Unknown''
END
) AS [Status]
FROM @replication_servers p
INNER JOIN (
SELECT publication_id
,publisher_db
,subscriber_db
,MIN([status]) [status]
,publisher_id
,subscriber_id
FROM dbo.MSsubscriptions
WHERE [status] <> 2 AND subscriber_id > - 1
GROUP BY publisher_id
,publisher_db
,publication_id
,subscriber_id
,subscriber_db
) ss ON ss.publisher_id = p.server_id
INNER JOIN @replication_servers s ON ss.subscriber_id = s.server_id
INNER JOIN dbo.MSpublications pp ON pp.publication_id = ss.publication_id
WHERE p.name = @in_publisher COLLATE SQL_Latin1_General_CP1_CI_AS;'
EXEC (@query);
