Thierry Schork
486b43a912
* Drop a publication and all subscription * Check that a snapshot agent finished without errors
51 lines
1.8 KiB
Transact-SQL
51 lines
1.8 KiB
Transact-SQL
DECLARE @jobs TABLE (
|
|
[name] sysname NOT NULL
|
|
,[job_id] UNIQUEIDENTIFIER NOT NULL
|
|
,[failed] BIT NOT NULL
|
|
DEFAULT 0
|
|
,[lastRun] DATETIME NULL
|
|
);
|
|
|
|
INSERT INTO @jobs ([name], [job_id])
|
|
SELECT
|
|
[s].[name]
|
|
,[s].[job_id]
|
|
FROM [msdb].[dbo].[sysjobs] [s]
|
|
WHERE [s].[name] IN (
|
|
'D00480 - ActivePos_read Snapshot'
|
|
);
|
|
|
|
|
|
SELECT
|
|
CONVERT(VARCHAR(20), SERVERPROPERTY('ServerName')) AS [ServerName]
|
|
,[j].[name] AS [job_name]
|
|
,CASE [j].[enabled]
|
|
WHEN 1 THEN 'Enabled'
|
|
ELSE 'Disabled'
|
|
END AS [job_status]
|
|
,CASE [jh].[run_status]
|
|
WHEN 0 THEN 'Error Failed'
|
|
WHEN 1 THEN 'Succeeded'
|
|
WHEN 2 THEN 'Retry'
|
|
WHEN 3 THEN 'Cancelled'
|
|
WHEN 4 THEN 'In Progress'
|
|
ELSE 'Status Unknown'
|
|
END AS [last_run_status]
|
|
,[ja].[run_requested_date] AS [last_run_date]
|
|
,CONVERT(
|
|
VARCHAR(10)
|
|
,CONVERT(DATETIME, RTRIM(19000101))
|
|
+ ([jh].[run_duration] * 9 + [jh].[run_duration] % 10000 * 6 + [jh].[run_duration] % 100 * 10) / 216e4
|
|
,108
|
|
) AS [run_duration]
|
|
,[ja].[next_scheduled_run_date]
|
|
,CONVERT(VARCHAR(500), [jh].[message]) AS [step_description]
|
|
FROM([msdb].[dbo].[sysjobactivity] [ja]
|
|
JOIN @jobs [j2] ON j2.[job_id] = ja.[job_id]
|
|
LEFT JOIN [msdb].[dbo].[sysjobhistory] [jh]
|
|
ON [ja].[job_history_id] = [jh].[instance_id])
|
|
JOIN [msdb].[dbo].[sysjobs_view] [j]
|
|
ON [ja].[job_id] = [j].[job_id]
|
|
WHERE [ja].[session_id] = (SELECT MAX([session_id])FROM [msdb].[dbo].[sysjobactivity])
|
|
ORDER BY [job_status] DESC,[last_run_status];
|