Skip to main content
added 204 characters in body
Source Link
sepupic
  • 11.2k
  • 17
  • 27

I wanted to clear this in a comment but there is no room to do this, so I post it as the answer.

While the the key point of what happens is right, i.e. the full backup cause the log to grow, this explanation below given by Ste Bov is completely wrong:

When you start a full backup that is the new point in time that the tLog backups will work from, but they cant work until that backup is complete, so during this time the transaction log grows, once the full backup is complete you now have a new point in time and the tLog backups will now be able to actually release the data from the transaction log and it will return to its normal usage levels

Fisrt of all full backup will NOT make "the new point in time that the tLog backups will work from", yes it creates a new recovery point, but the next log backup will start exaclty from the point where the previous log backup was stopped (this point in the log file, last_LSN, is written to the backup and can be seen using RESTORE HEADERONLY, or by querying msdb..backupset)

Second, starting with SQL Server 2005, concurrent log and full backups are possible, you can prove it yourself. What happens instead is that the log backup cannot clear the log when it finishes if there is a full/differential backup running concurrently. That is because diff/full backup need to write out the active portion"active portion"* of the log in order to get a database in consistent stay when restored.

*to be precise, it's not an "active portion", but all the log records from MIN(LSN of last checkpoint, LSN of oldest active transaction) to the LSN at which the data read portion of a backup ends

So the log should be large enough to be able to accomodate all the activity that is logged for the whole duration of the full backup.

More on this here:

Concurrent log and full backups by Randal

More on how much transaction log a full backup includes by Randal

I wanted to clear this in a comment but there is no room to do this, so I post it as the answer.

While the the key point of what happens is right, i.e. the full backup cause the log to grow, this explanation below given by Ste Bov is completely wrong:

When you start a full backup that is the new point in time that the tLog backups will work from, but they cant work until that backup is complete, so during this time the transaction log grows, once the full backup is complete you now have a new point in time and the tLog backups will now be able to actually release the data from the transaction log and it will return to its normal usage levels

Fisrt of all full backup will NOT make "the new point in time that the tLog backups will work from", yes it creates a new recovery point, but the next log backup will start exaclty from the point where the previous log backup was stopped (this point in the log file, last_LSN, is written to the backup and can be seen using RESTORE HEADERONLY, or by querying msdb..backupset)

Second, starting with SQL Server 2005, concurrent log and full backups are possible, you can prove it yourself. What happens instead is that the log backup cannot clear the log when it finishes if there is a full/differential backup running concurrently. That is because diff/full backup need to write out the active portion of the log in order to get a database in consistent stay when restored.

So the log should be large enough to be able to accomodate all the activity that is logged for the whole duration of the full backup.

More on this here:

Concurrent log and full backups by Randal

More on how much transaction log a full backup includes by Randal

I wanted to clear this in a comment but there is no room to do this, so I post it as the answer.

While the the key point of what happens is right, i.e. the full backup cause the log to grow, this explanation below given by Ste Bov is completely wrong:

When you start a full backup that is the new point in time that the tLog backups will work from, but they cant work until that backup is complete, so during this time the transaction log grows, once the full backup is complete you now have a new point in time and the tLog backups will now be able to actually release the data from the transaction log and it will return to its normal usage levels

Fisrt of all full backup will NOT make "the new point in time that the tLog backups will work from", yes it creates a new recovery point, but the next log backup will start exaclty from the point where the previous log backup was stopped (this point in the log file, last_LSN, is written to the backup and can be seen using RESTORE HEADERONLY, or by querying msdb..backupset)

Second, starting with SQL Server 2005, concurrent log and full backups are possible, you can prove it yourself. What happens instead is that the log backup cannot clear the log when it finishes if there is a full/differential backup running concurrently. That is because diff/full backup need to write out the "active portion"* of the log in order to get a database in consistent stay when restored.

*to be precise, it's not an "active portion", but all the log records from MIN(LSN of last checkpoint, LSN of oldest active transaction) to the LSN at which the data read portion of a backup ends

So the log should be large enough to be able to accomodate all the activity that is logged for the whole duration of the full backup.

More on this here:

Concurrent log and full backups by Randal

More on how much transaction log a full backup includes by Randal

Source Link
sepupic
  • 11.2k
  • 17
  • 27

I wanted to clear this in a comment but there is no room to do this, so I post it as the answer.

While the the key point of what happens is right, i.e. the full backup cause the log to grow, this explanation below given by Ste Bov is completely wrong:

When you start a full backup that is the new point in time that the tLog backups will work from, but they cant work until that backup is complete, so during this time the transaction log grows, once the full backup is complete you now have a new point in time and the tLog backups will now be able to actually release the data from the transaction log and it will return to its normal usage levels

Fisrt of all full backup will NOT make "the new point in time that the tLog backups will work from", yes it creates a new recovery point, but the next log backup will start exaclty from the point where the previous log backup was stopped (this point in the log file, last_LSN, is written to the backup and can be seen using RESTORE HEADERONLY, or by querying msdb..backupset)

Second, starting with SQL Server 2005, concurrent log and full backups are possible, you can prove it yourself. What happens instead is that the log backup cannot clear the log when it finishes if there is a full/differential backup running concurrently. That is because diff/full backup need to write out the active portion of the log in order to get a database in consistent stay when restored.

So the log should be large enough to be able to accomodate all the activity that is logged for the whole duration of the full backup.

More on this here:

Concurrent log and full backups by Randal

More on how much transaction log a full backup includes by Randal