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 «««910111213

Managing Transaction Logs Expand / Collapse
Author
Message
Posted Saturday, September 22, 2012 2:36 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:27 PM
Points: 481, Visits: 782
Excellent article! And thanks for clearing up, once and for all, the issue over full backups/log chain. It was always my understanding that full backups break the the log chain, and now I know this is not the case.

Post #1363172
Posted Thursday, November 8, 2012 1:51 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
Thank you for the article.
I found the section titled "How does SQL use the log?" particularly well written.

I hope I am not the only one that feels this way, but one cannot go over these concepts enough times, no matter how much DBA experience one has....


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1382702
Posted Friday, October 11, 2013 7:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
Excellent

Old article but still strikes the gold
Post #1504019
Posted Monday, November 25, 2013 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 1:17 PM
Points: 3, Visits: 32
Great post - very helpful!

One question though - does backup order matter? For instance, if you have a FULL backup being done on Friday and differentials being done every other day, does the transaction log need to be backed up before the differential/fulls happens or after? Or does it not matter?

Thanks,
Post #1517253
Posted Monday, November 25, 2013 7:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:27 PM
Points: 481, Visits: 782

It matters, but not for the reason you are asking.

Although they are not required in your restore scenario, most back-up/recovery strategies would have T-log backups conducted on a more frequent basis than fulls and diffs. T-logs backups are typically much smaller and easier to maintain, requiring fewer resources. They are also an essential database maintenance activity.

Bret

Post #1517261
Posted Monday, November 25, 2013 8:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
Tran log backups are unaffected by full/diff backups, so you can have your tran log backups running before, during or after the full/diff.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1517295
Posted Monday, November 25, 2013 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 1:17 PM
Points: 3, Visits: 32
Im assuming that there are a lot of considerations to account for when determining log backup frequency, but is a rule of thumb that says to perform them a few times a day or is it more of an hourly type of operation?

Thanks,
Post #1517304
Posted Monday, November 25, 2013 9:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
Straight from the article

Frequency of log backups

The frequency that log backups should be done is dependent on two considerations:

The maximum amount of data that can be lost in the case of a disaster
The size to which the log can grow.

The first consideration is by far the most important.

When in full recovery model a disaster that destroys or damages the database can be recovered from without data loss, providing the transaction log file is available. If the log file is not available then the best that can be done, assuming that the log backups are stored separately from the database files and are available, is a restore to the last log backup taken, losing all data after that point. From this it should be clear that the interval between log backups should be carefully chosen based on the RPO (recovery point objective) requirements for that database. If the mandate is that no more than 20 minutes of data can be lost, then scheduling log backups hourly is a risk, as a disaster can result in losing up to 60 minutes of data if the transaction log file is lost or damaged.

If (once the log backups have been setup and scheduled based on the database's RPO) the log is growing larger than is acceptable, then the log backup frequency can be increased in order to keep the size down. This is by no means guaranteed to have the desired effect as the log must be large enough to accommodate the largest single transaction run against the database (which is usually an index rebuild).



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1517327
Posted Monday, December 2, 2013 1:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 6:18 AM
Points: 3, Visits: 14
Thank you so much for this article.

I do have 1 question though in regards to sizing the log files. You mentioned a good starting point is to size each log file 150% of the size of the largest table. By this do you mean the data space or the index space? or the total of both?
Post #1519010
« Prev Topic | Next Topic »

Add to briefcase «««910111213

Permissions Expand / Collapse