May 7, 2008 at 2:50 pm
Hi everyone,
I am reading on the STANDBY option of the RESTORE command. So far, I've understood this command to behave in two distinct ways, and I'm not sure which one is true:
1) "STANDBY = standby_file_name" puts a database in read-only mode. All of the changes that were applied by the log backup are written to standby_file_name.
2) "STANDBY = standby_file_name" puts a database in read-only mode. All of the uncommitted transactions that were rolled back are written to standby_file_name.
My understanding was that the log backups do not back up uncommitted transactions, so to me, option 2 is wrong. However, that is precisely what I am reading in the SQL 2005 Unleashed book:
"With the STANDBY option, the database is left in a read-only state that allows some database access. standby_file_name must be specified with the STANDBY option. standby_file_name contains uncommitted transactions that were rolled back to place the database in a consistent state for read operations. If subsequent transaction log backups are applied to the STANDBY database, the uncommitted transactions in the standby file are reapplied to the database."
Thanks in advance for your input.
May 7, 2008 at 3:12 pm
Doesn't seem correct. I wouldn't think there would be uncommitted transactions after a restore. After a restart from sudden stop, but not a restore.
May 7, 2008 at 3:51 pm
both a full backup and a log backup will issue a checkpoint and put a marker in the log at the end of the backup process, this is the point at which the database is consistent.
The final part of any restore is the 'recovery' process. Any transaction with a begin tran before the checkpoint and a commit tran after the checkpoint will be rolled back. No further logs can be applied to the database after the recovery process. This is the part of a restore not done when you issue the 'norecovery' clause.
With standby mode those transactions that were rolled back are stored in the redo log, so when you apply the next log SQL can match those 'rolled back' transactions with the commits for them in the log backup, via the transaction id and LSN.
that way the databases is consistent and can be opened (but not for update) and subsequent logs can be applied.
My head hurts I'm going for a lie down now.
---------------------------------------------------------------------
May 7, 2008 at 4:54 pm
Thanks all.
My understanding is that SQL Server logs can and do contain transactions that are not yet committed. However, the log backups only back up committed transactions. So, how can a restore roll back uncommitted changes?
May 7, 2008 at 5:27 pm
Well it is reading the transaction log sequentially and it cannot tell ahead of time which transactions will ultimately be committed and which will not be.
So instead it just "Rolls Forward" everything. That is, applies each action that it finds in the transaction log, in order to the database. When it reaches the end of the transaction log, it then rolls-back all of the still open(uncommitted) transactions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply