info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



Log Shipping Real-Time Processing Engine v2
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2019-01-21









Execute on Log Shipping Secondary Server


- Replace DB1, DB2, DB3, DB4, DB57 with your Database Names
- Do not replace these: @IsDB4, @IsDB2, @IsDB3, @IsDB4, @IsDB5


Create Procedure dbo.spa_LS_Realtime_Processing
as

SET NOCOUNT ON

DECLARE @Debug bit = 0; --Set 1 returns top 25 backupset history
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @MSG NVARCHAR(1000)
DECLARE @fileList1 TABLE (backupFile NVARCHAR(255))
DECLARE @fileList2 TABLE (backupFile NVARCHAR(255))
DECLARE @backupFile NVARCHAR(500)
DECLARE @IsDB1 bit
DECLARE @IsDB2 bit
DECLARE @IsDB3 bit
DECLARE @IsDB4 bit
DECLARE @IsDB5 bit
DECLARE @IsDB6 bit
DECLARE @jobname varchar(150)
SET @backupPath = 'F:\LogShipping\Backups'

SELECT
reh.Destination_Database_Name, reh.Restore_Type, reh.User_Name, reh.Restore_Date,
bas.First_LSN, bas.Last_LSN, bas.Backup_Start_Date, bas.Backup_Finish_Date,
bmf.Physical_Device_Name,
LEFT(bmf.Physical_Device_Name,LEN(bmf.Physical_Device_Name) - charindex('\',reverse(bmf.Physical_Device_Name),1) + 1) as 'path',
RIGHT(bmf.Physical_Device_Name, CHARINDEX('\', REVERSE(bmf.Physical_Device_Name)) -1) as 'file_name',
bas.Server_Name, bas.Recovery_Model, bas.Compressed_Backup_Size,
bms.Is_Compressed, bas.Compatibility_Level
INTO #TMP1
FROM msdb.dbo.RESTOREHISTORY reh WITH (nolock)
Inner Join msdb.dbo.backupset bas on reh.backup_set_id = bas.backup_set_id
Inner Join msdb.dbo.backupmediaset bms on bas.backup_set_id = bms.media_set_id
Inner Join msdb.dbo.backupmediafamily bmf on bas.media_set_id = bmf.media_set_id

--Select * from #TMP1

SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList1(backupFile)
EXEC master.sys.xp_cmdshell @cmd

Delete from @filelist1 where backupfile IS NULL

RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - Log Shipping Real-Time Processing: Backup File --> Copy File --> Restore File', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT

RAISERROR ('', 0, 1) WITH NOWAIT

RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - List File System Primary Transaction Log Backup Files Outstanding...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT

RAISERROR ('', 0, 1) WITH NOWAIT

If NOT EXISTS (Select BackupFile from @fileList1 where backupFile not in (Select [file_name] from #TMP1))
BEGIN

RAISERROR (' - No Backup Files to process', 0, 1) WITH NOWAIT

RAISERROR ('', 0, 1) WITH NOWAIT

RAISERROR ('Finished !!!', 0, 1) WITH NOWAIT

DROP table #TMP1

RETURN

END

--Select BackupFile from @fileList1 where backupFile not in (Select [file_name] from #TMP1) order by backupFile asc

DECLARE @name1 nvarchar(128)

DECLARE cur1 CURSOR FOR
Select BackupFile from @fileList1 where backupFile not in (Select [file_name] from #TMP1) order by backupFile asc

OPEN cur1

FETCH NEXT FROM cur1 INTO @name1;

WHILE @@FETCH_STATUS = 0
BEGIN

IF CHARINDEX('DB1',@name1) > 0 Begin Set @IsDB1 = 1 RAISERROR (@name1, 0, 1) WITH NOWAIT End;
IF CHARINDEX('DB2',@name1) > 0 Begin Set @IsDB2 = 1 RAISERROR (@name1 , 0, 1) WITH NOWAIT End;
IF CHARINDEX('DB3',@name1) > 0 Begin Set @IsDB3 = 1 RAISERROR (@name1, 0, 1) WITH NOWAIT End;
IF CHARINDEX('DB4',@name1) > 0 Begin Set @IsDB4 = 1 RAISERROR (@name1 , 0, 1) WITH NOWAIT End;
IF CHARINDEX('DB5',@name1) > 0 Begin Set @IsDB5 = 1 RAISERROR (@name1 , 0, 1) WITH NOWAIT End;

FETCH NEXT FROM cur1 INTO @name1;
END

CLOSE cur1;
DEALLOCATE cur1;

WAITFOR DELAY '00:00:01';

RAISERROR ('', 0, 1) WITH NOWAIT

RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - Log Shipping Copy Job(s) on Secondary...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
PRINT CHAR(10)

If @IsDB1 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name--,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Copy%' AND sj.name like '%DB1%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Copy%' AND name like '%DB1%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End

WAITFOR DELAY '00:00:01';

If @IsDB2 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name--,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Copy%' AND sj.name like '%DB2%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Copy%' AND name like '%DB2%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End

WAITFOR DELAY '00:00:01';

If @IsDB3 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name--,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Copy%' AND sj.name like '%DB3%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Copy%' AND name like '%DB3%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End

WAITFOR DELAY '00:00:01';

If @IsDB4 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name--,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Copy%' AND sj.name like '%DB4%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Copy%' AND name like '%DB4%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End

WAITFOR DELAY '00:00:01';

If @IsDB5 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name--,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Copy%' AND sj.name like '%DB5%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Copy%' AND name like '%DB5%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End

WAITFOR DELAY '00:00:01';

RAISERROR ('', 0, 1) WITH NOWAIT

RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - Delay for 5s for Copy File processing to complete...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT

WAITFOR DELAY '00:00:05';

RAISERROR ('', 0, 1) WITH NOWAIT

RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - Starting Log Shipping Restore Job(s) on Secondary...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT

RAISERROR ('', 0, 1) WITH NOWAIT

If @IsDB1 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Restore%' AND sj.name like '%DB1%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Restore%' AND name like '%DB1%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End

WAITFOR DELAY '00:00:01';

If @IsDB2 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Restore%' AND sj.name like '%DB2%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Restore%' AND name like '%DB2%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End

WAITFOR DELAY '00:00:01';

If @IsDB3 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Restore%' AND sj.name like '%DB3%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Restore%' AND name like '%DB3%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End

WAITFOR DELAY '00:00:01';

If @IsDB4 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Restore%' AND sj.name like '%DB4%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Restore%' AND name like '%DB4%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End

WAITFOR DELAY '00:00:01';

If @IsDB5 = 1
Begin
IF NOT EXISTS
(
SELECT sj.name,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.run_Requested_date IS NOT NULL
AND sj.name like '%Restore%' AND sj.name like '%DB5%'
and not exists(
SELECT 1 from msdb..sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date )
)
Begin
Set @jobname = (Select name from msdb.dbo.sysjobs where category_id = 6 and name like '%Restore%' AND name like '%DB5%')
EXEC msdb.dbo.sp_start_job @jobname;
End
Else Begin Set @MSG = 'Job is already running' + @jobname + '''' RAISERROR (@MSG, 0, 1) WITH NOWAIT End
End

WAITFOR DELAY '00:00:01';

RAISERROR ('', 0, 1) WITH NOWAIT

RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - Delay for 5s for Restore File processing to complete...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT

WAITFOR DELAY '00:00:05';

RAISERROR ('', 0, 1) WITH NOWAIT

SELECT
reh.Destination_Database_Name, reh.Restore_Type, reh.User_Name, reh.Restore_Date,
bas.First_LSN, bas.Last_LSN, bas.Backup_Start_Date, bas.Backup_Finish_Date,
bmf.Physical_Device_Name,
LEFT(bmf.Physical_Device_Name,LEN(bmf.Physical_Device_Name) - charindex('\',reverse(bmf.Physical_Device_Name),1) + 1) as 'path',
RIGHT(bmf.Physical_Device_Name, CHARINDEX('\', REVERSE(bmf.Physical_Device_Name)) -1) as 'file_name',
bas.Server_Name, bas.Recovery_Model, bas.Compressed_Backup_Size,
bms.Is_Compressed, bas.Compatibility_Level
INTO #TMP2
FROM msdb.dbo.RESTOREHISTORY reh WITH (nolock)
Inner Join msdb.dbo.backupset bas on reh.backup_set_id = bas.backup_set_id
Inner Join msdb.dbo.backupmediaset bms on bas.backup_set_id = bms.media_set_id
Inner Join msdb.dbo.backupmediafamily bmf on bas.media_set_id = bmf.media_set_id

SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList2(backupFile)
EXEC master.sys.xp_cmdshell @cmd

Delete from @filelist2 where backupfile IS NULL

RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT
RAISERROR ('Starting - List File System Primary Log Backup Files Outstanding...', 0, 1) WITH NOWAIT
RAISERROR ('-----------------------------------------------------------------------------------------------------', 0, 1) WITH NOWAIT

RAISERROR ('', 0, 1) WITH NOWAIT

If NOT EXISTS (Select BackupFile from @fileList2 where backupFile not in (Select [file_name] from #TMP2))
BEGIN
RAISERROR (' - No Backup Files to process', 0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('Finished !!!', 0, 1) WITH NOWAIT

If @Debug = 1 Begin
Select top 25 Destination_Database_Name, Restore_Type, Restore_Date, File_Name, Compressed_Backup_Size, Server_Name from #TMP2 order by Restore_Date DESC
End

DROP Table #TMP1
DROP Table #TMP2

RETURN
END

DECLARE @name2 nvarchar(128)

DECLARE cur2 CURSOR FOR
Select BackupFile from @fileList2 where backupFile not in (Select [file_name] from #TMP2) order by backupFile asc

OPEN cur2

FETCH NEXT FROM cur2 INTO @name2;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name2
FETCH NEXT FROM cur2 INTO @name2;
END

CLOSE cur2;
DEALLOCATE cur2;

If @Debug = 1
Begin
Select top 25 Destination_Database_Name, Restore_Type, Restore_Date, File_Name, Compressed_Backup_Size, Server_Name from #TMP2 order by Restore_Date DESC
End

Drop Table #TMP1
Drop Table #TMP2

RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('- Re-run to process outstanding files !!!', 0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('Finished !!!', 0, 1) WITH NOWAIT
GO





Output:

-----------------------------------------------------------------------------------------------------
Starting - Log Shipping Real-Time Processing: Backup File --> Copy File --> Restore File
-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------
Starting - List File System Primary Transaction Log Backup Files Outstanding...
-----------------------------------------------------------------------------------------------------

DB1_20190121183001.trn
DB2_20190121183001.trn
DB3_20190121181500.trn
DB4_20190121181500.trn
DB5_20190121181500.trn

-----------------------------------------------------------------------------------------------------
Starting - Log Shipping Copy Job(s) on Secondary...
-----------------------------------------------------------------------------------------------------

Job 'LS_Copy_A1-NYC-PR_DB1' started successfully.
Job 'LS_Copy_A1-NYC-PR_DB2' started successfully.
Job 'LS_Copy_A1-NYC-PR_DB3' started successfully.
Job 'LS_Copy_A1-NYC-PR_DB4' started successfully.
Job 'LS_Copy_A1-NYC-PR_DB5' started successfully.
Job 'LS_Copy_A1-NYC-PR_DB6' started successfully.

-----------------------------------------------------------------------------------------------------
Starting - Delay for 5s for Copy File processing to complete...
-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------
Starting - Starting Log Shipping Restore Job(s) on Secondary...
-----------------------------------------------------------------------------------------------------

Job 'LS_Restore_A1-NYC-PR_DB1' started successfully.
Job 'LS_Restore_A1-NYC-PR_DB2' started successfully.
Job 'LS_Restore_A1-NYC-PR_DB3' started successfully.
Job 'LS_Restore_A1-NYC-PR_DB4' started successfully.
Job 'LS_Restore_A1-NYC-PR_DB5' started successfully.
Job 'LS_Restore_A1-NYC-PR_DB6' started successfully.

-----------------------------------------------------------------------------------------------------
Starting - Delay for 5s for Restore File processing to complete...
-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------
Starting - List File System Primary Log Backup Files Outstanding...
-----------------------------------------------------------------------------------------------------

- No Backup Files to process

Finished !!!




Version 1:

Log Shipping Real-Time Processing Engine (Version 1)