Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL SERVER 2008 R2 transaction log shipping Expand / Collapse
Author
Message
Posted Tuesday, February 4, 2014 9:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:31 AM
Points: 11, Visits: 111
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
Post #1537832
Posted Tuesday, February 4, 2014 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:31 AM
Points: 11, Visits: 111
any help??
Post #1537838
Posted Tuesday, February 4, 2014 10:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 5,879, Visits: 13,009
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'



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

Post #1537845
Posted Tuesday, February 4, 2014 10:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:31 AM
Points: 11, Visits: 111
Hi,

Thanks for the reply I tired all the steps mentioed.

I do not see any errors reported too.

secondary_database secondary_id restore_delay restore_all restore_mode disconnect_users block_size buffer_count max_transfer_size last_restored_file last_restored_date
X A060BA96-931A-4018-9770-4BA2243D7869 2 1 1 1 -1 -1 -1 NULL NULL


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.

Post #1537860
Posted Tuesday, February 4, 2014 10:47 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 5,879, Visits: 13,009
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.



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

Post #1537870
Posted Tuesday, February 4, 2014 10:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:31 AM
Points: 11, Visits: 111
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.
Post #1537878
Posted Tuesday, February 4, 2014 11:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:31 AM
Points: 11, Visits: 111
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!
Post #1537881
Posted Tuesday, February 4, 2014 11:15 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 5,879, Visits: 13,009
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


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

Post #1537883
Posted Tuesday, February 4, 2014 11:21 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 5,879, Visits: 13,009
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.


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

Post #1537887
Posted Tuesday, February 4, 2014 11:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:31 AM
Points: 11, Visits: 111
Tell me the details.
Post #1537889
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse