SQL SERVER 2008 R2 transaction log shipping

  • I have setup transaction log shipping in dev and QA successfully. But, on Prod log shipping everything working fine,

    the backup , copy and restore are working fine. not sure why the restore is not applying the transaction logs.

    Its skipping it.

    any help...

    it needs to apply the transactionlogs to destination database

  • any help??

  • probably missing a log backup so nothing for log shipping to apply as far as it is concerned.

    run these queries for info

    -- error details

    select * from msdb.dbo.log_shipping_monitor_error_detail

    select log_time_utc,message from msdb.dbo.log_shipping_monitor_error_detail

    -- tells you last log copied and restored, primary and secondary server names

    select * from msdb.dbo.log_shipping_monitor_secondary

    --tells you restore settings

    select * from msdb.dbo.log_shipping_secondary_databases

    --tells you where database log was backed up to (run on primary)

    select a.database_name,a.backup_finish_date,b.physical_device_name

    from msdb..backupset a inner join msdb..backupmediafamily b

    on a.media_set_id = b.media_set_id

    where database_name = 'dbname' and type = 'L'

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

  • Hi,

    Thanks for the reply I tired all the steps mentioed.

    I do not see any errors reported too.

    secondary_databasesecondary_idrestore_delayrestore_allrestore_modedisconnect_usersblock_sizebuffer_countmax_transfer_sizelast_restored_filelast_restored_date

    XA060BA96-931A-4018-9770-4BA2243D78692111-1-1-1NULLNULL

    X--> HERE is my database

    its skipping all the transaction logs,,, anything specific I need to provide.. SQLSVC is having admin permission too.

    Not sure why its not applying.

  • any messages about missing lsns from 'select log_time_utc,message from msdb.dbo.log_shipping_monitor_error_detail' ?

    run the last query in previous post and make sure you have all the log backups on your secondary since the full backup used to initiate log shipping.

    If in doubt, disable all the log shipping jobs, restore a new full backup to the secondary with norecovery and then reenable and run the logshipping jobs.

    Do not create any log backups via any process other than the logshipping transaction log backup job.

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

  • Its 38GB and its production environment and norecovery I did not understand you mean with standby rite.

    Initially during the setup I did not stop the replication.

    But later I see the replication is still showing under destination server

    you think anything do with it.

  • how about any other way ,

    I can get the destination server to recovery mode.

    Can I directly apply the transaction log back up

    or Do I need to need get full back up again and then followed by transaction log backup

    and then enable the jobs.

    let me know ,, its urgent!

  • sanku.raghuram (2/4/2014)


    Its 38GB and its production environment and norecovery I did not understand you mean with standby rite.

    do the initial full restore with norecovery, first log restore will put it in standby

    Initially during the setup I did not stop the replication.

    But later I see the replication is still showing under destination server

    you think anything do with it.

    very likely

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

  • sanku.raghuram (2/4/2014)


    how about any other way ,

    I can get the destination server to recovery mode.

    Can I directly apply the transaction log back up

    or Do I need to need get full back up again and then followed by transaction log backup

    and then enable the jobs.

    let me know ,, its urgent!

    if you have identified a missing log backup and have you can directly apply it (with norecovery or standby) then start the logshipping jobs.

    If you have not taken another full backup since the one you used to start the log shipping take a differential backup and restore that to the secondary with norecovery, then start logshipping jobs again.

    DO NOT recover the standby database, you will not be able to restore any further logs.

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

  • Tell me the details.

  • edited to avoid misrepresentation

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

  • Thanks for the details. I see that we had replication between the source and destination servers. Is it like mandatory to remove the replication and then setup transactional logshipping for sqlserver 2008 R2.

    Need details....

  • sanku.raghuram (2/4/2014)


    I see that we had replication between the source and destination servers. Is it like mandatory to remove the replication and then setup transactional logshipping for sqlserver 2008 R2.

    No, replication and log shipping work fine together.

    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
  • please don't go back and amend posts, you have made it look like I have refused to help you for no reason

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

  • Sorry for mis confusion and comming to Shawpoint-- you mean replication and transaction logshipping will work together.

    In that which order we go fist ,,

    i mean first setup snapshot replication and then followed by transaction log shipping or vice-versa.

    I have deleted the replication before the transaction log shipping betweeb source and destination [SQL SERVER 2008 R2]

    but replication some how was still shoowing on destination server replication folder.

    and when i HAVE set the log shipping on the destination server the logs are getting skipped this is production.

    I need to again test it.

    need assistance???????????

Viewing 15 posts - 1 through 15 (of 22 total)

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