April 1, 2015 at 8:41 am
I actually have couple of questions and I can't seem to find any answer online.
I configure log shipping. I take the FULL BACKUP of the DB and restore it on the secondary server with standby. So with standby, it takes forever to restore. I read that when STANDBY mode is used, the database restores as NORECOVERY, then analyzes and rolls back all uncommitted transactions in the log. It can then give read only access to users. So far so good.
This is what confuses me and please bare with me since the question may sound childish.
Q. My question is about "analyzing and rolls back uncommited transactions in the log." What's being rolled back. I though when you take a full backup, all the committed data gete backed up so where this uncommitted transaction come from? The same backup is being used to restore so what's rolling back?
Q. Why can't we have both DB online in log shipping? Is there any particular reason behind it?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
April 1, 2015 at 8:43 am
New Born DBA (4/1/2015)
where this uncommitted transaction come from?
Transactions which were in progress during the backup.
Q. Why can't we have both DB online in log shipping? Is there any particular reason behind it?
Once you bring a DB online, the LSNs no longer match what's in the log backups, so the log backups can't be restored. And that's not even getting into what would happen if someone made changes to data that the log restore needs to change as well.
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
April 1, 2015 at 8:48 am
Thanks Gail
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply