October 28, 2008 at 11:27 am
We have a VLDB that takes over 15 hours to perform a full BU. An intense processing job kicked off just after the start of the BU and took approximately 10 hours to complete. This grew our Transaction Log to over 200 GB even though the DB is in SIMPLE recovery mode. Upon completion of the backup, the space utilization of the TLOG was extremely small.
I have read that a CHECKPOINT is issued before a BU begins, but have not been able to confirm that they are NOT issued DURING the BU. If someone has knowledge in this, please confirm this or correct my thinking. I thank any help in advance.
October 28, 2008 at 8:12 pm
I'll bet you auto checkpointed because of your recovery interval setting.
I'm assuming you have 'truncate log on checkpoint' turned on?
You can run a trace to verify this theory.
Craig Outcalt
October 28, 2008 at 8:52 pm
Checkpoints happen by those "Activities That Cause a Checkpoint" (BOL2005).
Just find a good site to refer to for backup with/without checkpoints. http://www.sqlbackuprestore.com/recoveringindividualtables.htm
October 29, 2008 at 10:15 am
The recovery interval is set to the default of 0, so this would not be preventing a checkpoint from occurring for this amount of time. As stated, the DB is in SIMPLE recovery mode, so it is truncating the log with each checkpoint.
November 3, 2008 at 8:02 am
Vivien, Thanks for the reply. The BOL article "Activities That Cause a Checkpoint" was the main source of my research for this issue. This states that "Checkpoints occur in the following situations: ....A database backup is taken"
It also states that a checkpoint will automatically be taken:
"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:
-The log becomes 70 percent full.
-The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option." In my case, the log became 100 percent full and caused several file growths.
My question is - Does a BU of the database cause a "lock" on SQL Server to not allow checkpoints from being generated?
November 4, 2008 at 9:04 am
The transaction log exists to maintain database integrity regardless of the recovery model. If you start a backup and then start an intensive set of update transactions then the log must grow for the database to remain consistent for each process. A checkpoint can occur for each connection. If I'm backing up database XX which contains table A and you launch a process that starts updating table A then I need to give your process a place to track your updates without disturbing the integrity of other users. That place is the transaction log. The fact that the database uses the simple recovery model doesn't change that. As far as I know, backup does not set locks nor can it be locked out under normal conditions.
A full backup includes part of the transaction log so a full backup would include those changes that were made to Table A while backups were occurring. A backup includes everything up to the point that the backup completed (not when it started). If your full backup is taking 15 hours, you might consider trying differential backups.
The purpose of a checkpoint is to flush dirty database pages to disk from the buffer cache of the current disk. Since a full backup includes part of the transaction log it doesn't really matter when a checkpoint occurs because you're guaranteed database integrity up until the backup completes.
From BOL:
A checkpoint performs the following processes in the database:
* Writes a record to the log file, marking the start of the checkpoint.
* Stores information recorded for the checkpoint in a chain of checkpoint log records.
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. This LSN is called the Minimum Recovery LSN (MinLSN). The MinLSN is the minimum of the:
o LSN of the start of the checkpoint.
o LSN of the start of the oldest active transaction.
o LSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.
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.
* Writes all dirty log and data pages to disk.
* Writes a record marking the end of the checkpoint to the log file.
* Writes the LSN of the start of this chain to the database boot page.
"Beliefs" get in the way of learning.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply