Managing Transaction Logs

Question

Managing Transaction Logs

Viewing 9 posts - 121 through 128 (of 128 total)

You must be logged in to reply to this topic. Login to reply

SQLmountain SSCommitted Points: 1505 More actions · Answer 1

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.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

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.... 🙂

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

sqlnaive SSCoach Points: 17437 More actions · Answer 3

Excellent

Old article but still strikes the gold 🙂

pwquigle SSC Rookie Points: 35 More actions · Answer 4

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,

SQLmountain SSCommitted Points: 1505 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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, MVP, M.Sc (Comp Sci)
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

pwquigle SSC Rookie Points: 35 More actions · Answer 7

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,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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, MVP, M.Sc (Comp Sci)
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

djaap SSC Journeyman Points: 93 More actions · Answer 9

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?