如何在 TSQL 代理作業步驟中獲取作業開始時間？

有沒有一種好方法可以在給定的 sql 代理作業步驟 (TSQL) 中查詢作業開始時間？這將有助於以下場景中的第 3 步。

Job Foo（每天執行 @ 晚上 11:30）

Step1 - StartTime = 週六晚上 11:30 TSQL -（持續 15 分鐘）

Step2 - StartTime = 週六晚上 11:45 TSQL -（持續 30 分鐘）

Step3 - StartTime = 週日上午 12:15 TSQL -早點退出/除非 Foo 作業在星期六開始，否則不要執行。

這是一個用於建構顯示目前作業的執行狀態和目前步驟的視圖的腳本。

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE OR ALTER   PROCEDURE ETLSteps.ListJobsCurrentRunningStatus 
AS
BEGIN
   SET NOCOUNT ON
   SET ANSI_WARNINGS OFF
   DECLARE @currentjobstatus TABLE([Job ID] binary(16), [Last Run Date] int, [Last Run Time] int, [Next Run Date] int, [Next Run Time] int, [Next Run Schedule ID] int, [Requested To Run] int, [Request Source] int, [Request Source ID] nvarchar(66), [Running] int, [Current Step] int, [Current Retry Attempt] int, [State] int )
   INSERT INTO @currentjobstatus
   EXEC master.dbo.xp_sqlagent_enum_jobs 1, ''
   SELECT job_id_bin = [Job ID], job_is_running = Running, current_step_id = [Current Step], current_step_retry_attempt_no = [Current Retry Attempt], current_run_status = State FROM @currentjobstatus
   RETURN 0
   DECLARE @rtn int
   EXEC @rtn =  ETLSteps.ListJobsCurrentRunningStatus 
END
GO

EXEC master.dbo.sp_addlinkedserver @server = N'.\instance', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'.\instance',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'collation name', @optvalue=NULL
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'.\instance', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

CREATE OR ALTER VIEW ETLSteps.AgentJobsCurrentlyRunning AS
SELECT 
   job_id = CAST(job_id_bin AS UNIQUEIDENTIFIER)
,   job_id_bin
,   job_is_running 
,   current_step_id 
,   current_step_retry_attempt_no
,   current_run_status 
FROM OPENQUERY([.\instance], 'DBABnch_Local.ETLSteps.ListJobsCurrentRunningStatus') query
GO
SELECT * FROM ETLSteps.AgentJobsCurrentlyRunning

引用自：https://dba.stackexchange.com/questions/281241