SQL Server Performance Forum – Threads Archive

Error 14420 during database backup

We are using SQL server W2k with service pack 3. We take the full database backup at 10.00 pm and runs for 1.30 -2 hours daily. We also have log shipping configured for 10 minutes delay. During the database backup, we have configured suppress of log alerts from 10.00 pm to 12.00 pm. But however, we are still getting the following errors: "The log shipping source SAPPROD.P46 has not backed up for 61 minutes.."
the moment it crosses the 60 minute threshold set. But the surprising thing is that errors occurs at 10.50 pm( which is within the suppress error window). I read the feedback from other people who have raised similar doubts and for your reference I am attaching the output from the following procedures from both the source and destination: source:
sp_get_log_shipping_monitor_info
primary_server_name ——————-
SAPPROD destination:
sp_get_log_shipping_monitor_info (null). So my question, is how to suppress the error 14420 during the window of 2 hours ? An early reply and guidance is highly appreciated. Rgds, Viral Shah

KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;329133 to resolve the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, Thanks for the input. As per the note, I checked the following things: 1. the threshold is set to 60 minutes and we do a transaction log backup every 10 minutes.. so we are well within the limit. 2. the original production server is the monitor server itself (we do not have a separate monitor server) and there is only one entry in the log_shipping_primaries table and log_shipping_primaries table which is as follows: 7 SAPPROD P46 BCC1C202-78CC-46C5-9650-5DD15ED0B117 60 14420 1 P46_tlog_200408171000.TRN 2004-08-17 10:00:04.363 2200 2359 127 NULL
(1 row(s) affected) The output from log_shipping_secondaries: 7 SAPLOAN-1 P46 P46_tlog_200408171000.TRN P46_tlog_200408170950.TRN 2004-08-17 10:10:05.440 2004-08-17 10:11:38.490 60C1298C-7E7C-4488-B95C-EDA7C53046F6 1 1 60 14421 1 2200 127 100 1 And the log shipping and copying is working fine but the alerts are still getting generated during the suppress alerts time. Please guide me. Rgds, Viral Shah

Are there any network issues between the Primary and Secondar servers?
What is the size of transaction log backup size?
How often you sync. the full database backup between the servers? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, No there is absolutely no network issues between primary and secondary servers. Both are in the same domain and we have never faced any problem on the network side. The transaction log backup size on an average are around 100MB…the transaction log files are successfully backed up on prodn, copied successfuly to failover and even they are getting uploaded on to fail over server too.. We have not sync the databases between production and failover since we configured… is that an issue? rgds, viral shah

If possible try to sync. the databases between Primary and secondary servers. From the KBA, confirm these points:

You may have set an incorrect value for the Out of Sync Alert threshold. Ideally, you must set this value to at least three times the frequency of the slower of the Copy and Restore jobs. If the frequency of the Copy or Restore jobs is modified after log shipping is set up and functional, you must modify the value of the Out of Sync Alert threshold accordingly. Problems either with the Backup job or Copy job are most likely to result in "out of sync" alert messages. If "out of sync" alert messages are raised and if there are no problems with the Backup or the Restore job, check the Copy job for potential problems. Additionally, network connectivity may cause the Copy job to fail.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, I double checked the threashold and they are set at 60 minutes and the copy and load frequency is only 10 minutes.. so we are correctly set…. What do u mean by syncing the database? do u want us to re-create the failover database once again ? our database is around 250 GB and takes long … so kindly guide me… I would like to highlight that this problem started only after we changed the collation of the sql server production database (due to SAP requirement)… we re-installed sql server and attached and de-attached SAP database… and then re-configured the log shipping from scratch.. so this problem is due to that ? kindly guide me.. rgds, viral shah

Right in that case have you re-configured the collation setting on secondary server also?
And I feel this message does not indicate a problem with log shipping, if the logs are restored properly and no issues on performance side then better to ignore the message. BTW as you’ve already configured log shipping to port the transaction logs between primary and secondary servers, then why to schedule another backup job on each night that is having this message. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

NO, i have not changed the collation on secondary server. Let me explain what we actually did. WE have SAP running on SQL server 2000. The SAP database requires specific collation (850 bin) and also the same collation setting for the SQL server. However, due to our oversight, the SQL server collation was different than that of SAP database. The secondary server had the exact same collation for SQL server database and SAP database. So only thing we changed was the collation of SQL server and not of the SAP database. So do u think the error is due to that ? because now the collation on both the primary and secondary server is same. And I only have log shipping configured and no other backup job running to back the transaction logs. Awaiting your guidance. Rgds, viral shah

If the sequential transaction logs are restore properly after that backup job then simply ignore this issue. I don’t think error 14420 is a typical error you need to show such an importance, its an informational or rather say warning message about timing of the transaction log restore operational sequence, if you aren’t having any performance issues with this behaviour then do not pay much attention to this error, just monitor the logs between the servers. (to be precise even I too get this message on couple of my services, as I know its an informational and I will make sure the corresponding Tlog sequence is completed) If ain’t broke…. don’t fix it [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] (my policy) <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

Hi Satya, As per one of your suggestion, I re-created the failover database by taking the backup of production and restoring on target server( i.e. the backup is after the collation change on production server). So both target and production database are exactly same in every sense however, still the error of 14420 and 14421 (log shipping out of sync is still occuring).. For the details : I have set the threshold to 60 minutes
The copy/restore frequency is set to 10 minutes. The time for suppressing the error is set to 10.00 pm – 3.00 am However, I start getting the error as soon as the day is changed ie. immediately after 12.00 am
(is it related to it)… Basically I am Oracle DBA and I m trying to co-relate the error I was getting in Oracle monitoring tools where u need to set two different suppress error times if it is crossing the day (i.e. from 10.00 pm to 11.59 pm and 12.00 am – 3.00 am).. is the same bug in sql server ? I m just doubting it.. Your thoughts on this would be highly appreciated. Thanks, Viral Shah

Viral As I said even I too get this warning message on my services, and once I check all jobs worked OK ignore the message. I reported this issue to MS Support and until now there is no response. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Viral –
We’re having problems suppressing log shipping alerts as well. Some research has led us to identify a bug in the procs msdb.dbo.sp_update_log_shipping_monitor_info & msdb.dbo.sp_get_log_shipping_monitor_info. By selecting from dbo.log_shipping_primaries & dbo.log_shipping_secondaries, you can see log_shipping_secondaries.planned_outage_end_time & log_shipping_secondaries.planned_outage_weekday_mask column values are transposed when compared to dbo.log_shipping_primaries. (Our alert suppress times were configured identically for backup and restore.) In sp_update_log_shipping_monitor_info, the bug appears to be in the update to log_shipping_secondaries where the end time is set to the weekday mask & vice versa:
planned_outage_end_time = @_out_of_sync_outage_weekday_mask,
planned_outage_weekday_mask = @_out_of_sync_outage_end_time The full unmodified statement from sp_update_log_shipping_monitor_info is:
UPDATE msdb.dbo.log_shipping_secondaries SET
copy_enabled = @_copy_enabled,
load_enabled = @_load_enabled,
out_of_sync_threshold = @_out_of_sync_threshold,
threshold_alert = @_out_of_sync_threshold_alert,
threshold_alert_enabled = @_out_of_sync_threshold_alert_enabled,
planned_outage_start_time = @_out_of_sync_outage_start_time,
planned_outage_end_time = @_out_of_sync_outage_weekday_mask,
planned_outage_weekday_mask = @_out_of_sync_outage_end_time
WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name By examining sp_get_log_shipping_monitor_info we’ve determined the
load_outage_end_time is being loaded (INSERT INTO #lsp) with s.planned_outage_weekday_mask,
and load_outage_weekday_mask is being loaded with s.planned_outage_end_time, Because both procs carry the same transposition error it does not manifest itself in the Log Shipping Pair Properties interface (SQL Enterprise Manager). The real problem is when sp_get_log_shipping_monitor_info executes sp_log_shipping_in_sync and the transposed values are propagated via @load_outage_end_time & @load_outage_weekday_mask (these values determine whether or not to suppress the load alert.) I’ve modified both sp_get_log_shipping_monitor_info & sp_update_log_shipping_monitor_info to address the transposition. I’m currently testing the mods. We are using:
Microsoft SQL Server 2000 – 8.00.857 (Intel X86)
Sep 2 2003 18:55:32
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4) -rl

Viral – During testing, we’ve discovered another bug in the Log Shipping Alert suppression process. In the proc msdb.dbo.sp_log_shipping_in_sync (which is called by both sp_log_shipping_monitor_backup & sp_log_shipping_monitor_restore) the calculation for @cur_time is: SELECT @cur_time = DATEPART (hh, GETDATE()) * 10000 + DATEPART (mi, GETDATE()) * 100 + DATEPART (ss, GETDATE()) i.e. 12:01 AM = 100 , 6:00 AM = 60000, etc… This yields time as hhmmss (integer). Unfortunately the values in log_shipping_primaries & log_shipping_secondaries for planned_outage_start_time & planned_outage_end_time are stored as hhmm (integer). i.e. 12:01 AM = 1 , 6:00 AM = 600, etc… In its original form, suppression would only be effective when suppression does not span midnight where 12:00 AM <= @outage_start_time <= current time < @outage_end_time < 12:24AM If suppression does span midnight(@outage_start_time > @outage_end_time) alerts would be generated practically whenever logshipping is out of sync. In other words no suppression at all. To make the comparisions valid I modified the calculation as: SELECT @cur_time = DATEPART (hh, GETDATE()) * 100 + DATEPART (mi, GETDATE()) Also, note seconds are truncated. The Log Shipping Pair Properties interface (SQL Enterprise Manager) displays time in a date control in the form hh:mm<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />s AM/PM, it only passes hhmm (integer) to the proc sp_update_log_shipping_monitor_info: @out_of_sync_outage_start_time INT = NULL, @out_of_sync_outage_end_time INT = NULL, Alternatively, the simplest way to suppress alerts would be to schedule the jobs ‘Log Shipping Alert Job – Backup’ & ‘Log Shipping Alert Job – Restore’ to run only at valid times. e.g. Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, every 1 minute(s) between 6:00:00 AM and 11:59:59 PM Also, as a side note msdb.dbo.sp_get_log_shipping_monitor_info would only return valid info from msdb on the SQL Server which serves as your log shipping monitor. Which looks to be in your case SAPPROD. -rl

I am having similar problem and discovered an article at: http://www.sqlserverprofessionalnewsletter.com/Media/MediaManager/nSQLsample.pdf</a> Go to page 13 – The Case of the Unsuppressed Log Shipping Alerts This outlines a problem with how the stored proc sp_log_shipping_in_sync has been written and holds values in the two tables log_shipping_primaries and log_shipping_secondaries differently to what needs to be read. However after trying the fix in the article above it fixed the Supress Alerts for Backup Failure but not for Copy and Load. I discovered another problem with the table log_shipping_secondaries where the columns planned_outage_end_time and planned_outage_weekday_mask are the wrong way round. i.e. if I select mon through sun on the Destination tab for Supress Alert Generation i should see a value of 127 (works this out like a binary number) in the planned_outage_weekday_mask but it appears in the planned_outage_end_time and vice versa if I put time’s in. I can’t find anything else on this problem and would appreciate help if anyone else has…seems very buggy this log shipping [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!]

I think the article has a typo. I replaced (@outage_weekday_mask & DATEPART(dw, GETDATE ()) > 0) with (@outage_weekday_mask & POWER(2, DATEPART(dw, GETDATE()) -1 ) > 0) After modifying the code you will need review your suppression settings on your logshipping monitor. Here’s the code for the three modified procs (sp_log_shipping_in_sync, sp_get_log_shipping_monitor_info, sp_update_log_shipping_monitor_info): ALTER PROCEDURE sp_log_shipping_in_sync
@last_updated DATETIME,
@compare_with DATETIME,
@threshold INT,
@outage_start_time INT,
@outage_end_time INT,
@outage_weekday_mask INT,
@enabled BIT = 1,
@delta INT = NULL OUTPUT
AS BEGIN
/*
rl 10/04/05 modified @cur_time calculation
for valid comparison with stored values in:
log_shipping_primaries.planned_outage_start_time
log_shipping_primaries.planned_outage_end_time
log_shipping_secondaries.planned_outage_start_time
log_shipping_secondaries.planned_outage_end_time replaced (@outage_weekday_mask & DATEPART(dw, GETDATE ()) > 0)
with (@outage_weekday_mask & POWER(2, DATEPART(dw, GETDATE()) -1 ) > 0)
*/
SET NOCOUNT ON
DECLARE @cur_time INT SELECT @delta = DATEDIFF (mi, @last_updated, @compare_with)
— in sync
IF (@delta <= @threshold)
RETURN (0) — in sync IF (@enabled = 0)
RETURN(0) — in sync IF (@outage_weekday_mask & POWER(2, DATEPART(dw, GETDATE()) -1 ) > 0) — potentially in outage window
BEGIN
— SELECT @cur_time = DATEPART (hh, GETDATE()) * 10000 +
— DATEPART (mi, GETDATE()) * 100 +
— DATEPART (ss, GETDATE())
select @cur_time = DATEPART (hh, GETDATE()) * 100 +
DATEPART (mi, GETDATE())
— outage doesn’t span midnight
IF (@outage_start_time < @outage_end_time)
BEGIN
IF (@cur_time >= @outage_start_time AND @cur_time < @outage_end_time)
RETURN(1) — in outage
END
— outage does span midnight
ELSE IF (@outage_start_time > @outage_end_time)
BEGIN
IF (@cur_time >= @outage_start_time OR @cur_time < @outage_end_time)
RETURN(1) — in outage
END
END
RETURN(-1 ) — not in outage, not in sync
END GO
ALTER PROCEDURE sp_update_log_shipping_monitor_info
@primary_server_name sysname,
@primary_database_name sysname,
@secondary_server_name sysname,
@secondary_database_name sysname,
@backup_threshold INT = NULL,
@backup_threshold_alert INT = NULL,
@backup_threshold_alert_enabled BIT = NULL,
@backup_outage_start_time INT = NULL,
@backup_outage_end_time INT = NULL,
@backup_outage_weekday_mask INT = NULL,
@copy_enabled BIT = NULL,
@load_enabled BIT = NULL,
@out_of_sync_threshold INT = NULL,
@out_of_sync_threshold_alert INT = NULL,
@out_of_sync_threshold_alert_enabled BIT = NULL,
@out_of_sync_outage_start_time INT = NULL,
@out_of_sync_outage_end_time INT = NULL,
@out_of_sync_outage_weekday_mask INT = NULL
AS BEGIN
/*
rl 10/4modified update to log_shipping_secondaries — planned_outage_end_time = @_out_of_sync_outage_weekday_mask,
— planned_outage_weekday_mask = @_out_of_sync_outage_end_time
planned_outage_end_time = @_out_of_sync_outage_end_time,
planned_outage_weekday_mask = @_out_of_sync_outage_weekday_mask
*/
SET NOCOUNT ON
DECLARE @_backup_threshold INT
DECLARE @_backup_threshold_alert INT
DECLARE @_backup_threshold_alert_enabled BIT
DECLARE @_backup_outage_start_time INT
DECLARE @_backup_outage_end_time INT
DECLARE @_backup_outage_weekday_mask INT
DECLARE @_copy_enabled BIT
DECLARE @_load_enabled BIT
DECLARE @_out_of_sync_threshold INT
DECLARE @_out_of_sync_threshold_alert INT
DECLARE @_out_of_sync_threshold_alert_enabled BIT
DECLARE @_out_of_sync_outage_start_time INT
DECLARE @_out_of_sync_outage_end_time INT
DECLARE @_out_of_sync_outage_weekday_mask INT — check that the primary exists
IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name))
BEGIN
DECLARE @pp sysname
SELECT @pp = @primary_server_name + N’.’ + @primary_database_name
RAISERROR (14262, 16, 1, N’primary_server_name.primary_database_name’, @pp)
RETURN (1) — error
END — check that the secondary exists
IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondaries WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name))
BEGIN
DECLARE @sp sysname
SELECT @sp = @secondary_server_name + N’.’ + @secondary_database_name
RAISERROR (14262, 16, 1, N’secondary_server_name.secondary_database_name’, @sp)
RETURN (1) — error
END — load the original variables SELECT
@_backup_threshold = backup_threshold,
@_backup_threshold_alert = p.threshold_alert,
@_backup_threshold_alert_enabled = p.threshold_alert_enabled,
@_backup_outage_start_time = p.planned_outage_start_time,
@_backup_outage_end_time = p.planned_outage_end_time,
@_backup_outage_weekday_mask = p.planned_outage_weekday_mask,
@_copy_enabled = copy_enabled,
@_load_enabled = load_enabled,
@_out_of_sync_threshold = out_of_sync_threshold,
@_out_of_sync_threshold_alert = s.threshold_alert,
@_out_of_sync_threshold_alert_enabled = s.threshold_alert_enabled,
@_out_of_sync_outage_start_time = s.planned_outage_start_time,
@_out_of_sync_outage_weekday_mask = s.planned_outage_weekday_mask,
@_out_of_sync_outage_end_time = s.planned_outage_end_time
FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s
WHERE
p.primary_id = s.primary_id AND
primary_server_name = @primary_server_name AND
primary_database_name = @primary_database_name AND
secondary_server_name = @secondary_server_name AND
secondary_database_name = @secondary_database_name
SELECT @_backup_threshold = ISNULL (@backup_threshold, @_backup_threshold)
SELECT @_backup_threshold_alert = ISNULL (@backup_threshold_alert, @_backup_threshold_alert)
SELECT @_backup_threshold_alert_enabled = ISNULL (@backup_threshold_alert_enabled, @_backup_threshold_alert_enabled)
SELECT @_backup_outage_start_time = ISNULL (@backup_outage_start_time, @_backup_outage_start_time)
SELECT @_backup_outage_end_time = ISNULL (@backup_outage_end_time, @_backup_outage_end_time)
SELECT @_backup_outage_weekday_mask = ISNULL (@backup_outage_weekday_mask, @_backup_outage_weekday_mask)
SELECT @_copy_enabled = ISNULL (@copy_enabled, @_copy_enabled)
SELECT @_load_enabled = ISNULL (@load_enabled, @_load_enabled)
SELECT @_out_of_sync_threshold = ISNULL (@out_of_sync_threshold, @_out_of_sync_threshold)
SELECT @_out_of_sync_threshold_alert = ISNULL (@out_of_sync_threshold_alert, @_out_of_sync_threshold_alert)
SELECT @_out_of_sync_threshold_alert_enabled = ISNULL (@out_of_sync_threshold_alert_enabled, @_out_of_sync_threshold_alert_enabled)
SELECT @_out_of_sync_outage_start_time = ISNULL (@out_of_sync_outage_start_time, @_out_of_sync_outage_start_time)
SELECT @_out_of_sync_outage_end_time = ISNULL (@out_of_sync_outage_end_time, @_out_of_sync_outage_end_time)
SELECT @_out_of_sync_outage_weekday_mask = ISNULL (@out_of_sync_outage_weekday_mask, @_out_of_sync_outage_weekday_mask) — updates
UPDATE msdb.dbo.log_shipping_primaries SET
backup_threshold = @_backup_threshold,
threshold_alert = @_backup_threshold_alert,
threshold_alert_enabled = @_backup_threshold_alert_enabled,
planned_outage_start_time = @_backup_outage_start_time,
planned_outage_end_time = @_backup_outage_end_time,
planned_outage_weekday_mask = @_backup_outage_weekday_mask
WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name UPDATE msdb.dbo.log_shipping_secondaries SET
copy_enabled = @_copy_enabled,
load_enabled = @_load_enabled,
out_of_sync_threshold = @_out_of_sync_threshold,
threshold_alert = @_out_of_sync_threshold_alert,
threshold_alert_enabled = @_out_of_sync_threshold_alert_enabled,
planned_outage_start_time = @_out_of_sync_outage_start_time,
— planned_outage_end_time = @_out_of_sync_outage_weekday_mask,
— planned_outage_weekday_mask = @_out_of_sync_outage_end_time
planned_outage_end_time = @_out_of_sync_outage_end_time,
planned_outage_weekday_mask = @_out_of_sync_outage_weekday_mask
WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name
RETURN(0)
END GO
CREATE PROCEDURE sp_get_log_shipping_monitor_info
@primary_server_name sysname = N’%’,
@primary_database_name sysname = N’%’,
@secondary_server_name sysname = N’%’,
@secondary_database_name sysname = N’%’
AS BEGIN
/*
10/04/04 rlfixed order of
— s.planned_outage_weekday_mask,
— s.planned_outage_end_time,
*/
SET NOCOUNT ON
CREATE TABLE #lsp (
primary_server_name sysname COLLATE database_default NOT NULL,
primary_database_name sysname COLLATE database_default NOT NULL,
secondary_server_name sysname COLLATE database_default NOT NULL,
secondary_database_name sysname COLLATE database_default NOT NULL,
backup_threshold INT NOT NULL,
backup_threshold_alert INT NOT NULL,
backup_threshold_alert_enabled BIT NOT NULL,
last_backup_filename NVARCHAR(500) COLLATE database_default NOT NULL,
last_backup_last_updated DATETIME NOT NULL,
backup_outage_start_time INT NOT NULL,
backup_outage_end_time INT NOT NULL,
backup_outage_weekday_mask INT NOT NULL,
backup_in_sync INT NULL, — 0 = in sync, -1 = out of sync, 1 = in outage window
backup_delta INT NULL,
last_copied_filename NVARCHAR(500) COLLATE database_default NOT NULL,
last_copied_last_updated DATETIME NOT NULL,
last_loaded_filename NVARCHAR(500) COLLATE database_default NOT NULL,
last_loaded_last_updated DATETIME NOT NULL,
copy_delta INT NULL,
copy_enabled BIT NOT NULL,
load_enabled BIT NOT NULL,
out_of_sync_threshold INT NOT NULL,
load_threshold_alert INT NOT NULL,
load_threshold_alert_enabled BIT NOT NULL,
load_outage_start_time INT NOT NULL,
load_outage_end_time INT NOT NULL,
load_outage_weekday_mask INT NOT NULL,
load_in_sync INT NULL, — 0 = in sync, -1 = out of sync, 1 = in outage window
load_delta INT NULL,
maintenance_plan_id UNIQUEIDENTIFIER NULL,
secondary_plan_id UNIQUEIDENTIFIER NOT NULL) INSERT INTO #lsp SELECT
primary_server_name,
primary_database_name,
secondary_server_name,
secondary_database_name,
backup_threshold,
p.threshold_alert,
p.threshold_alert_enabled,
last_backup_filename,
p.last_updated,
p.planned_outage_start_time,
p.planned_outage_end_time,
p.planned_outage_weekday_mask,
NULL,
NULL,
last_copied_filename,
last_copied_last_updated,
last_loaded_filename,
last_loaded_last_updated,
NULL,
copy_enabled,
load_enabled,
out_of_sync_threshold,
s.threshold_alert,
s.threshold_alert_enabled,
s.planned_outage_start_time,
s.planned_outage_end_time,
s.planned_outage_weekday_mask,
— s.planned_outage_weekday_mask,
— s.planned_outage_end_time,
NULL,
NULL,
maintenance_plan_id,
secondary_plan_id
FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s
WHERE
p.primary_id = s.primary_id AND
primary_server_name LIKE @primary_server_name AND
primary_database_name LIKE @primary_database_name AND
secondary_server_name LIKE @secondary_server_name AND
secondary_database_name LIKE @secondary_database_name DECLARE @load_in_sync INT
DECLARE @backup_in_sync INT
DECLARE @_primary_server_name sysname
DECLARE @_primary_database_name sysname
DECLARE @_secondary_server_name sysname
DECLARE @_secondary_database_name sysname
DECLARE @last_loaded_last_updated DATETIME
DECLARE @last_loaded_filename NVARCHAR (500)
DECLARE @last_copied_filename NVARCHAR (500)
DECLARE @last_backup_last_updated DATETIME
DECLARE @last_backup_filename NVARCHAR (500)
DECLARE @backup_outage_start_time INT
DECLARE @backup_outage_end_time INT
DECLARE @backup_outage_weekday_mask INT
DECLARE @backup_threshold INT
DECLARE @backup_threshold_alert_enabled BIT
DECLARE @load_outage_start_time INT
DECLARE @load_outage_end_time INT
DECLARE @load_outage_weekday_mask INT
DECLARE @load_threshold INT
DECLARE @load_threshold_alert_enabled BIT
DECLARE @backupdt DATETIME
DECLARE @restoredt DATETIME
DECLARE @copydt DATETIME
DECLARE @rv INT
DECLARE @dt DATETIME
DECLARE @copy_delta INT
DECLARE @load_delta INT
DECLARE @backup_delta INT
DECLARE @last_copied_last_updated DATETIME SELECT @dt = GETDATE () DECLARE sync_update CURSOR FOR
SELECT
primary_server_name,
primary_database_name,
secondary_server_name,
secondary_database_name,
last_backup_filename,
last_backup_last_updated,
last_loaded_filename,
last_loaded_last_updated,
backup_outage_start_time,
backup_outage_end_time,
backup_outage_weekday_mask,
backup_threshold,
backup_threshold_alert_enabled,
load_outage_start_time,
load_outage_end_time,
out_of_sync_threshold,
load_outage_weekday_mask,
load_threshold_alert_enabled,
last_copied_filename,
last_copied_last_updated
FROM #lsp
FOR READ ONLY OPEN sync_update loop:
FETCH NEXT FROM sync_update INTO
@_primary_server_name,
@_primary_database_name,
@_secondary_server_name,
@_secondary_database_name,
@last_backup_filename,
@last_backup_last_updated,
@last_loaded_filename,
@last_loaded_last_updated,
@backup_outage_start_time,
@backup_outage_end_time,
@backup_outage_weekday_mask,
@backup_threshold,
@backup_threshold_alert_enabled,
@load_outage_start_time,
@load_outage_end_time,
@load_threshold,
@load_outage_weekday_mask,
@load_threshold_alert_enabled,
@last_copied_filename,
@last_copied_last_updated IF @@fetch_status <> 0
GOTO _loop EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_backup_filename, @backupdt OUTPUT
IF (@rv <> 0)
SElECT @backupdt = @last_backup_last_updated
EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_loaded_filename, @restoredt OUTPUT
IF (@rv <> 0)
SElECT @restoredt = @last_loaded_last_updated
EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_copied_filename, @copydt OUTPUT
IF (@rv <> 0)
SElECT @copydt = @last_copied_last_updated EXECUTE @load_in_sync = msdb.dbo.sp_log_shipping_in_sync
@restoredt,
@backupdt,
@load_threshold,
@load_outage_start_time,
@load_outage_end_time,
@load_outage_weekday_mask,
@load_threshold_alert_enabled,
@load_delta OUTPUT EXECUTE @backup_in_sync = msdb.dbo.sp_log_shipping_in_sync
@last_backup_last_updated,
@dt,
@backup_threshold,
@backup_outage_start_time,
@backup_outage_end_time,
@backup_outage_weekday_mask,
@backup_threshold_alert_enabled,
@backup_delta OUTPUT EXECUTE msdb.dbo.sp_log_shipping_in_sync
@copydt,
@backupdt,
1,0,0,0,0,
@copy_delta OUTPUT UPDATE #lsp
SET backup_in_sync = @backup_in_sync, load_in_sync = @load_in_sync,
copy_delta = @copy_delta, load_delta = @load_delta, backup_delta = @backup_delta
WHERE primary_server_name = @_primary_server_name AND
secondary_server_name = @_secondary_server_name AND
primary_database_name = @_primary_database_name AND
secondary_database_name = @_secondary_database_name
GOTO loop
_loop:
CLOSE sync_update
DEALLOCATE sync_update
SELECT * FROM #lsp
END
GO

]]>