SQL Server Performance

Error 14420 during database backup

Discussion in 'SQL Server Log Shipping' started by viral, Aug 16, 2004.

  1. viral New Member

    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

  2. satya Moderator

  3. viral New Member

    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
  4. satya Moderator

    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.
  5. viral New Member

    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
  6. satya Moderator

    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.
  7. viral New Member

    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
  8. satya Moderator

    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.
  9. viral New Member

    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
  10. satya Moderator

    If the sequential transaction logs are restore properly after that backup job then simply ignore this issue.<br /><br />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)<br /><br />If ain't broke.... don't fix it [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] (my policy)<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  11. viral New Member

    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
  12. satya Moderator

    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.
  13. rfl New Member

    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
  14. rfl New Member

    Viral - During testing, we've discovered another bug in the Log Shipping Alert suppression process. <br /><br />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:<br />SELECT @cur_time = DATEPART (hh, GETDATE()) * 10000 +<br /> DATEPART (mi, GETDATE()) * 100 + <br /> DATEPART (ss, GETDATE())<br />i.e.<br /> 12:01 AM = 100<br />, 6:00 AM = 60000, etc...<br /><br />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). <br />i.e.<br /> 12:01 AM = 1<br />, 6:00 AM = 600, etc...<br /><br />In its original form, suppression would only be effective when suppression does not span midnight where 12:00 AM &lt;= @outage_start_time &lt;= current time &lt; @outage_end_time &lt; 12:24AM<br /><br />If suppression does span midnight(@outage_start_time &gt; @outage_end_time) alerts would be generated practically whenever logshipping is out of sync. In other words no suppression at all.<br /><br />To make the comparisions valid I modified the calculation as:<br />SELECT @cur_time = DATEPART (hh, GETDATE()) * 100 +<br /> DATEPART (mi, GETDATE()) <br /><br />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:<br /> @out_of_sync_outage_start_time INT = NULL,<br /> @out_of_sync_outage_end_time INT = NULL,<br /><br /><br />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. <br /><br />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<br /><br />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. <br /><br />-rl<br /><br /><br /><br />
  15. Hak New Member

    I am having similar problem and discovered an article at:<br /><br /<a target="_blank" href=http://www.sqlserverprofessionalnewsletter.com/Media/MediaManager/nSQLsample.pdf>http://www.sqlserverprofessionalnewsletter.com/Media/MediaManager/nSQLsample.pdf</a><br /><br />Go to page 13 - The Case of the Unsuppressed Log Shipping Alerts<br /><br />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.<br /><br />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.<br /><br />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.<br /><br />I can't find anything else on this problem and would appreciate help if anyone else has...seems very buggy this log shipping <br /><br />[<img src='/community/emoticons/emotion-6.gif' alt=':(' />!]<br /><br />
  16. rfl New Member

    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

Share This Page