July 11, 2009 at 5:14 am
A full backup backups up the data in the database and then backs up just enough of the transaction log so that when the backup is restored, it can be brought to a consistent state. It backs up no more of the log than that and it does not mark log space as reusable.
You could make an argument that all the previous log information is incorporated, because the changes made by those log records are reflected within the data file, but all the previous log information is not included in the full backup.
Each log backup then contains the log records since the previous log backup. The only time a log backup contains info since a full backup is when that was the first full backup of the log chain.
All restores have to start with a full backup, because that's the only thing that contains the entire database. To restore to a point in time, all you need is a full backup and all of the log backups taken since that full backup.
Take a scenario where these backups are made
Full backup 1
Log backup 1
Log backup 2
Log backup 3
Full backup 2
Log backup 4
Log backup 5
Log backup 6
-- Database fails here--
To restore to the point of failure, I can restore either full backup 2 and then the log backups 4,5 and 6. I can also restore full backup 1 then log backups 1,2,3,4,5 and 6.
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
July 14, 2009 at 1:34 pm
Thanks for the clarification. One other question if you’ll allow, this being a little outside of the scope of this discussion… will doing a log backup on a mirror principal server while the mirror server is suspended case an condition where the mirror can no longer be synchronized, or will the log sequence number values allow the mirror knows where the principal is? Thanks again.
July 14, 2009 at 1:48 pm
I'm not too familiar with mirroring, but I'd be surprised if you could back the log up on the mirror. If you can, it shouldn't affect anything. It's the principal who's log has to go to the mirror, not the other way around.
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
July 14, 2009 at 2:00 pm
I'm sorry I was not clear "will doing a log backup on a mirror principal server while the mirror server is suspended, (typo) cause an condition where the mirror can no longer be synchronized, or will the log sequence number values allow the mirror knows where the principal is"...is what I was trying to ask...and you are correct, the principal is the only one that can be backed up. Any ideas on how I might find an answer to this? Thanks much...
July 14, 2009 at 2:14 pm
ed (7/14/2009)
I'm sorry I was not clear "will doing a log backup on a mirror principal server while the mirror server is suspended, (typo) cause an condition where the mirror can no longer be synchronized, or will the log sequence number values allow the mirror knows where the principal is"...is what I was trying to ask...and you are correct, the principal is the only one that can be backed up. Any ideas on how I might find an answer to this? Thanks much...
Here - let me google that for you: http://tinyurl.com/l4dshm :w00t:
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2009 at 4:28 pm
Yes, thanks, I saw that information… and again I wasn’t clear in my question. I should have said disconnected instead of suspended concerning the servers state. The question was provoked by information I saw on different chats that said in essence “servers that were mirrored could be rebooted with no ill effect to mirror”. I was in the process of trying to find out if scheduled backups running during one of these reboots would screw up the logs. I posed the question on another user group and someone pointed me to Gail’s very informative article, (which has already cleared up some of my error’s in thinking). Again.. thank you all for your responses.
August 19, 2009 at 8:05 am
After re-reading this thread, I realize the error of my ways in having a couple of our databases set to simple recovery model.
Is there a problem if I change to Full model while users are in the system or must I do this at an "off-time"?
Thanks,
Bob
August 19, 2009 at 8:15 am
Bob Bridges (8/19/2009)
Is there a problem if I change to Full model while users are in the system or must I do this at an "off-time"?
It can be done any time.
Just note that once you switch to full and take a full database backup, you are responsible for managing the transaction logs from that point on. Make sure that you have log backups set up.
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
May 3, 2010 at 11:12 am
TY Gail for the backup article. It demystifies the MS explanation. I see that Master, MSDB and tempdb default to Simple Recovery Model. In previous versions of SQL Server, my recollection is that msdb and tempdb log files would sometimes fill up and lock the production databases if not truncated regularly. Can you comment if there is any change to this process or should I include truncation of those logs as part of my daily maintenence plan?
May 3, 2010 at 11:28 am
carlb 28852 (5/3/2010)
I see that Master, MSDB and tempdb default to Simple Recovery Model.
Master is in simple recovery and, even if switched to full behaves as if it is in simple (cannot back the logs up)
TempDB cannot be backed up at all. If its log grows it's because of heavy activity.
MSDB, as far as I know, defaults to simple recovery. You can switch it to full if you like, but then you need to start taking log backups. Unless your jobs and job history data change that fast and are that important, you probably wouldn't need to switch to full.
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
May 5, 2010 at 2:34 pm
I know I may be a bit late in responding to this article, but would a good "rule of thumb" (I know those can be dangerous with SQL Server) be to size the transaction log to be at least the same size of your largest index as the rebuild will take care of that? So if you have a 5 gig, 4 gig, and 1 gig clustered index on three tables, size your transaction log to be at least 5GB (may be 1.5 the index, similar to resizing the windows pagefile?)
Thanks.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
May 5, 2010 at 4:01 pm
It depends. 😀 (Did you really expect any other answer?)
If you do your index rebuild in bulk-logged recovery, the log may not need to be that big as rebuilds are minimally logged. If you're doing index rebuilds in full, you'll need more space than largest index, because of overhead of log record headers & metadata. I believe the figure of 1.2*(size of largest index) is often banded around.
I don't know where that figure comes from, so I'm not sure how trustworthy it is.
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
May 6, 2010 at 7:19 am
GilaMonster (5/5/2010)
It depends. 😀 (Did you really expect any other answer?)If you do your index rebuild in bulk-logged recovery, the log may not need to be that big as rebuilds are minimally logged. If you're doing index rebuilds in full, you'll need more space than largest index, because of overhead of log record headers & metadata. I believe the figure of 1.2*(size of largest index) is often banded around.
I don't know where that figure comes from, so I'm not sure how trustworthy it is.
Yeah...I know. 😀
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
July 5, 2010 at 6:36 am
Nice article with gentle introduction. thanks.
July 5, 2010 at 9:16 am
Excellent article Gail. Any chance of an overview of the SQL Server operations which take place internally. Am I correct in thinking that a checkpoint flushes the buffer to disk and by disk I mean the transaction log (ldf). If so, at what stage do the records in the log file get written to the data file (mdf)? When a user requests another record, presumable this is fetched from the mdf, and then any changes to the record prompt the cycle to occur again, ie checkpoint flushes memory to LDF and then somehow from the LDF back to the MDF. I'd really appreciate some clarification on how this process works.
Many thanks for taking the time to share your knowledge.
Viewing 15 posts - 61 through 75 (of 128 total)
You must be logged in to reply to this topic. Login to reply