If your business requires that you minimize work-loss exposure, consider taking log backups more frequently. More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log files. To limit the number of log backups that you need to restore, it is essential to routinely back up your data. For example, you might schedule a weekly full database backup and daily differential database backups.
Again, think about the required RTO and RPO when implementing your recovery strategy, and specifically the full and differential database backup cadence. A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery.
Unless you choose to overwrite existing backup sets when creating a full database backup, the existing log chain remains intact. With the log chain intact, you can restore your database from any full database backup in the media set, followed by all subsequent log backups up through your recovery point. The recovery point could be the end of the last log backup or a specific recovery point in any of the log backups.
To restore a database up to the point of failure, the log chain must be intact. That is, an unbroken sequence of transaction log backups must extend up to the point of failure. Where this sequence of log must start depends on the type of data backups you are restoring: database, partial, or file. For a database or partial backup, the sequence of log backups must extend from the end of a database or partial backup.
For a set of file backups, the sequence of log backups must extend from the start of a full set of file backups. Restoring a log backup rolls forward the changes that were recorded in the transaction log to re-create the exact state of the database at the time the log backup operation started. When you restore a database, you will have to restore the log backups that were created after the full database backup that you restore, or from the start of the first file backup that you restore.
Typically, after you restore the most recent data or differential backup, you must restore a series of log backups until you reach your recovery point. Then, you recover the database.
This rolls back all transactions that were incomplete when the recovery started and brings the database online. After the database has been recovered, you cannot restore any more backups. Checkpoints flush dirty data pages from the buffer cache of the current database to disk. This minimizes the active portion of the log that must be processed during a full recovery of a database. During a full recovery, the following types of actions are performed:.
One piece of information recorded in the checkpoint is the log sequence number LSN of the first log record that must be present for a successful database-wide rollback. The MinLSN is the minimum of the:. The checkpoint records also contain a list of all the active transactions that have modified the database.
If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN. The interval between automatic checkpoints is based on the amount of log space used and the time elapsed since the last checkpoint.
The time interval between automatic checkpoints can be highly variable and long, if few modifications are made in the database. Automatic checkpoints can also occur frequently if lots of data is modified. Use the recovery interval server configuration option to calculate the interval between automatic checkpoints for all the databases on a server instance.
This option specifies the maximum time the Database Engine should use to recover a database during a system restart. The Database Engine estimates how many log records it can process in the recovery interval during a recovery operation. If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.
If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:. For information about setting the recovery interval, see Configure the recovery interval Server Configuration Option.
The -k setup option applies to automatic checkpoints and any otherwise unthrottled checkpoints. Automatic checkpoints truncate the unused section of the transaction log if the database is using the simple recovery model. However, if the database is using the full or bulk-logged recovery models, the log is not truncated by automatic checkpoints. For more information, see The Transaction Log. The section of the log file from the MinLSN to the last-written log record is called the active portion of the log, or the active log.
This is the section of the log required to do a full recovery of the database. When one VLF becomes full logging continue to write into the next available in the transaction log. The transaction log file can be represented as a circular file.
When the logging reaches the end of the file it starts again from the beginning, but only if all the requirements has been met and the inactive parts has been truncated. The truncation process is necessary to mark all inactive parts so they can be used again and overwritten. Logical log is an active part of the transaction log. The MinLSN is the starting point of the oldest active transaction in the online transaction log.
Mark US English. Daniel British. Libby British. Mia British. Karen Australian. Hayley Australian. Natasha Australian. Veena Indian. Priya Indian. Neerja Indian. Zira US English. Oliver British.
Wendy British. The transaction log is split up into small chunks called virtual log files, which we will discuss in a later section. When a virtual log file is full, transactions automatically move to the next virtual log file.
As long as the log records at the beginning of the transaction log have been truncated when logging reaches the end of the log, it will circle back around to the start and will overwrite what was there before:.
Source: Technet.
0コメント