125 lines
4.0 KiB
Transact-SQL
125 lines
4.0 KiB
Transact-SQL
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
|
|
|