Logshipping -- What am I missing??

  • Greetings all;

    OK, so I've set up logshipping before. It is really quite a simple concept. In an ideal world I'd be able to utilize all of the features of Log Shipping right out of the box. But, due to security reasons I can't.

    So here is the deal. I've gone ahead and configured my primary database (production) for log shipping. For now it is easy to deal with because it is not really loaded with data. Due to security reasons the receiving database is unaccessible from the production box. However I can FTP to it from the receiving database server (pull). OK, so no big deal. I simply write a simple process to ftp the transaction logs to the receiving database. Works just fine. This is where things start to fall apart.

    I FTP over the first transaction log to receiving database server. No problem

    I load the first transaction log. No problem

    I FTP over the next transaction log to receiving database server. No problem

    I go to load the second transaction log and kaput! Doesn't load. In essence it states that I'm out of sequence. Driving me nuts! :crazy:

    So, what am I missing?

    FYI, I've done this before with success. This one is about to make me rip my hair out.

    Specs:

    Production Box (source)

    Intel Xeon E7520 dual 8 core processors

    16gb memory

    Data drive 500gb

    Log drive 500gb

    Receiving Box(destination)

    Xeon 2.8 gh dual core processor

    Memory ??

    Disk 100 gb (data & log)

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • hmm .. tricky. The boxes shouldn't make any difference - that's the joy of log shipping, I've used since sql 6.5 but my own implementation.

    I usually check the headers as a first step - you should be able to get the lsns to make sure you're in sequence. An operation on source will mess the restores, can't remember what offhand but some operations can mess the lsn sequence.

    Are you doing this in standby or read only ( not that it should make any difference )

    I'd check your log restores onto another database on your source machine just to verify they all restore ok.That way you eliminate log shipping and ftp, when it works there then go back to your ftp. I've only used ftp for log shipping once but it worked fine.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Thanks... I'll keep poking at it.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • You have another log backup job running?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Nope... that was my first thought too. Thanks.

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • GilaMonster (5/20/2011)


    You have another log backup job running?

    or someone\something is taking a log backup

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Query the backup tables in MSDB (or check the error log). If it says the logs are out of sequence, they are.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • OK, well here is something interesting... It appears that I can load ...20110520170000.trn and nothing beyond it. Odd. Any suggestions?

    --==================

    Processing E:\dbtransfer\CONX_Prod_20110520163000.trn

    RESTORE LOG [CONX_Prod] FROM DISK = N'E:\dbtransfer\CONX_Prod_20110520163000.trn' WITH FILE = 1 , NORECOVERY , NOUNLOAD , STATS = 10

    Msg 4326, Level 16, State 1, Server SP5DB, Line 1

    The log in this backup set terminates at LSN 1526000000683700001, which is too early to apply to the database. A more recent log backup that includes LSN 1526000000791400001 can be restored.

    Msg 3013, Level 16, State 1, Server SP5DB, Line 1

    RESTORE LOG is terminating abnormally.

    --==================

    --==================

    Processing E:\dbtransfer\CONX_Prod_20110520170000.trn

    RESTORE LOG [CONX_Prod] FROM DISK = N'E:\dbtransfer\CONX_Prod_20110520170000.trn' WITH FILE = 1 , NORECOVERY , NOUNLOAD , STATS = 10

    100 percent processed.

    Processed 0 pages for database 'CONX_Prod', file 'CONX_Prod' on file 1.

    Processed 68 pages for database 'CONX_Prod', file 'CONX_Prod_log' on file 1.

    RESTORE LOG successfully processed 68 pages in 0.256 seconds (2.054 MB/sec).

    --==================

    --==================

    Processing E:\dbtransfer\CONX_Prod_20110520173000.trn

    RESTORE LOG [CONX_Prod] FROM DISK = N'E:\dbtransfer\CONX_Prod_20110520173000.trn' WITH FILE = 1 , NORECOVERY , NOUNLOAD , STATS = 10

    Msg 4305, Level 16, State 1, Server SP5DB, Line 1

    The log in this backup set begins at LSN 1526000000793200001, which is too recent to apply to the database. An earlier log backup that includes LSN 1526000000791400001 can be restored.

    Msg 3013, Level 16, State 1, Server SP5DB, Line 1

    RESTORE LOG is terminating abnormally.

    --==================

    --==================

    Processing E:\dbtransfer\CONX_Prod_20110520180001.trn

    RESTORE LOG [CONX_Prod] FROM DISK = N'E:\dbtransfer\CONX_Prod_20110520180001.trn' WITH FILE = 1 , NORECOVERY , NOUNLOAD , STATS = 10

    Msg 4305, Level 16, State 1, Server SP5DB, Line 1

    The log in this backup set begins at LSN 1526000000900100001, which is too recent to apply to the database. An earlier log backup that includes LSN 1526000000791400001 can be restored.

    Msg 3013, Level 16, State 1, Server SP5DB, Line 1

    RESTORE LOG is terminating abnormally.

    --==================

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • There is a log backup missing between CONX_Prod_20110520170000.trn and CONX_Prod_20110520173000.trn. Either you have a log backup job/maint plan, someone took an ad-hoc log backup or there's a third party tool backing up the log.

    The error is a clear one, there is a log backup missing, nothing else causes that message.

    Did you check the MSDB system tables or error log like I suggested?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail et el;

    To my discovery there IS another backup process taking place. This time by the the host provider. I was unaware of this, but it was just mentioned to me by the Ops Mgr. This causing the logs to be out of sequence. Turns out to be something that was never disclosed to me... go figure. I know I'm not crazy ... :crazy:

    Thanks all for your help.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply