USE [dba]
GO

/****** Object:  StoredProcedure [dbo].[start_snapshot]    Script Date: 15.04.2025 10:24:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*
start_snapshot

SP used to start the associated "repl-snapshot" job from a published database.

Variable @published_db
    If null, starts the snapshot job of all published dbs
    If not null, must contain the name of a db that is published and will start the snapshot job of that db only

15.04.2025	TSC	Adapted to avoid trying to start several time the same job
*/
CREATE OR ALTER   PROCEDURE [dbo].[start_snapshot]
  @published_db VARCHAR(500),
  @debug BIT = 0
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;

  DECLARE @jobs TABLE (publisher_db sysname NOT NULL,
                       agent_name sysname NOT NULL,
                       job_name sysname NOT NULL,
                       start_execution_date DATETIME NULL,
                       stop_execution_date DATETIME NULL,
                       next_scheduled_run_date DATETIME NULL,
                       current_job_status VARCHAR(50) NOT NULL,
                       start_job_cmd AS 'exec msdb.dbo.sp_start_job @job_name=''' + job_name + ''';');
  DECLARE @cmd NVARCHAR(MAX) = N'';

  INSERT INTO @jobs ([publisher_db],
                     [agent_name],
                     [job_name],
                     [start_execution_date],
                     [stop_execution_date],
                     [next_scheduled_run_date],
                     [current_job_status])
  SELECT distinct   
              [a].[publisher_db],
              [a].[name] AS [agent_name],
              [s].[name] AS [job_name],
              [ac].[start_execution_date],
              [ac].[stop_execution_date],
              [ac].[next_scheduled_run_date],
              CASE
                   WHEN [ac].[start_execution_date] IS NOT NULL
                    AND [ac].[stop_execution_date] IS NULL THEN 'running'
                   WHEN [ac].[start_execution_date] IS NOT NULL
                    AND [ac].[stop_execution_date] IS NOT NULL THEN 'finished'
                   WHEN [ac].[start_execution_date] IS NULL
                    AND [ac].[stop_execution_date] IS NULL THEN 'never started'
                   ELSE 'unknown' END AS current_job_status
    FROM      [distribution].dbo.[MSsnapshot_agents] a
    JOIN      [msdb].[dbo].[sysjobs] s
      ON [s].[job_id]  = [a].[job_id]
    LEFT JOIN ( SELECT TOP 1 --last execution, if ever there was one
					   [ja].[job_id],
                       [ja].[start_execution_date],
                       [ja].[stop_execution_date],
                       [ja].[next_scheduled_run_date]
                  FROM [msdb].[dbo].[sysjobactivity] ja
				  ORDER BY [start_execution_date] DESC
				  ) ac
      ON [ac].[job_id] = [s].[job_id]
   WHERE      [a].[publisher_db] = COALESCE(@published_db, [a].[publisher_db])
   ORDER BY [a].[publisher_db];

  IF NOT EXISTS (SELECT 1 FROM @jobs)
  BEGIN
    RAISERROR('Database %s is not existing or not published in a replication', 16, 5, @published_db);
    RETURN;
  END;

  if @debug=1
  begin
	select *
	from @jobs
  end

  SELECT @cmd = @cmd + j.start_job_cmd
    FROM @jobs j
   WHERE [j].[current_job_status] <> 'running';

  IF @debug = 1
  BEGIN
    PRINT @cmd;
  END;
  ELSE
  BEGIN
    EXEC (@cmd);
    
    --give a couple seconds for the agent(s) to start
    WAITFOR DELAY '00:00:15';

    --do not exit this proc until the last snapshot job started has finished
    WHILE EXISTS(
        --return something while a snapshot job is running
        SELECT 1
        FROM msdb.dbo.[sysjobs] j
            JOIN msdb.dbo.[sysjobactivity] ja ON [j].[job_id] = [ja].[job_id]
            JOIN [distribution].dbo.[MSsnapshot_agents] a ON a.[job_id] = j.[job_id]
        WHERE a.[publisher_db] = COALESCE(@published_db, a.[publisher_db])
        AND [ja].[start_execution_date] IS NOT NULL 
        AND [ja].[stop_execution_date] IS NULL 
    )
    BEGIN
        WAITFOR DELAY '00:00:05';
        RAISERROR('waiting for snapshot job(s) to finish',0,0) WITH NOWAIT;
    END
  END;
END;

GO