February 18, 2011 at 6:07 am
paul.knibbs (2/18/2011)
When you say your log shipping is going out of sync, how long are you leaving it for? We have log shipping set up over an ADSL link (e.g. really slow!) and a big log backup will knock that out of sync, but it always gets back into sync by itself--I've never yet had to rebuild the log shipping just to fix this issue.
it(log shipping) starts at 6 am in morning, i came to work at 8:30 am
and until tht time its out of sync...
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 18, 2011 at 6:09 am
Doesn't sound like a very long time to me. What's the speed of the link this backup file is being copied over?
February 18, 2011 at 6:09 am
Andrzej Sulej (2/18/2011)
In what state you leave database after restoring log - standby or no recovery?If you have standby, change to no recovery,that will speed up process of restoring logs, no undo process occurs
I've the same situation rebuild large indexes generates large trn files.
Log shipped database are always in standyby/read only mode...
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 18, 2011 at 6:17 am
paul.knibbs (2/18/2011)
Doesn't sound like a very long time to me. What's the speed of the link this backup file is being copied over?
Do you think that once it gets out of sync , it can get back into sync automatically /widout doing anything?
Speed of hte link?? I dont have idea, iam sharing the backup folders on those 2 servers.....Firstly the log trn files are backed up on primary server, then they are copied to log shipping server widout any delay and then restored there on sec server....
The network speed between those 2 servers shld be very gud as wen i try to restore the bak file ( 140 gb) from primary server to secondary server it takes arnd 60-70 minutes...
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 18, 2011 at 6:21 am
Yes, it *can* get back into sync automatically. The only reason it would not do that is if one of the log file backups was lost. Once that big log file has transferred to the secondary server and been restored it should come back into sync quite quickly as it copies and restores the much smaller following ones.
February 18, 2011 at 6:26 am
You can set up state of database after restoring log in LS , if you would like to have read only database , then you use standby model or don’t have readable database between restoring logs then use no recovery, the restoring logs is much faster in that case.
If you don’t have to have read only database, change to “no recovery” or change it only during morning processing.
Our database was 500 GB and when we rebuild large indexes, we had out of sync 0.5 – 5 h. When we change recovery model we have out of sync.. max 10 – 20 minutes.
Try to change this option, if you don’t have problem with coping logs between servers, I’m sure it would solve your problem.
February 18, 2011 at 6:28 am
paul.knibbs (2/18/2011)
Yes, it *can* get back into sync automatically. The only reason it would not do that is if one of the log file backups was lost. Once that big log file has transferred to the secondary server and been restored it should come back into sync quite quickly as it copies and restores the much smaller following ones.
Maybe, u r right, but its a prod database log shipped database and I cant wait and wait for it to get into sync.
Will work around as cirquedsql told to rebuild only some tables or maybe leave log shipping enabled in night during rebuilds...
Thxs for ur inputs.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 18, 2011 at 6:33 am
Andrzej Sulej (2/18/2011)
You can set up state of database after restoring log in LS , if you would like to have read only database , then you use standby model or don’t have readable database between restoring logs then use no recovery, the restoring logs is much faster in that case.If you don’t have to have read only database, change to “no recovery” or change it only during morning processing.
Our database was 500 GB and when we rebuild large indexes, we had out of sync 0.5 – 5 h. When we change recovery model we have out of sync.. max 10 – 20 minutes.
Try to change this option, if you don’t have problem with coping logs between servers, I’m sure it would solve your problem.
You mean I should restore logs manually.??
and also, when i come to work, how do i know, how many logs have been already applied since the first trn file in the morning...?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 18, 2011 at 6:53 am
When you restore log you can chose state of database after that :
RECOVERY – Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored.
NORECOVERY – Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored
STANDBY – Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a file so that recovery effects can be reverted
In Log Shipping you can set only NORECOVERY or STANDBY – there is a option when you configure LS, you can also change this in running LS.
If you have STANDBY , server have to do extra operation to rollback all the changes written in undo file, to let restore next transaction log.
So, you don’t have to restore log manually , just change recovery model from standby to norecovery (if you have Stnadby), process will speed up, and you will not have out of sync..
February 18, 2011 at 9:18 am
SKYBVI (2/18/2011)
paul.knibbs (2/18/2011)
Doesn't sound like a very long time to me. What's the speed of the link this backup file is being copied over?Do you think that once it gets out of sync , it can get back into sync automatically
Regards,
Sushant
Yes it can get back into sync but it will take some time for it to do so.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply