How does a transaction log backup deal with active log?
What happens to the log records that maintain any open uncommitted transactions when a transaction log backup starts? Will these records also be included in the log backup?
sql-server transaction-log
asked Jan 26 '16 at 5:03
karun_rkarun_r
156312
add a comment |
What happens to the log records that maintain any open uncommitted transactions when a transaction log backup starts? Will these records also be included in the log backup?
sql-server transaction-log
asked Jan 26 '16 at 5:03
karun_rkarun_r
156312
add a comment |
What happens to the log records that maintain any open uncommitted transactions when a transaction log backup starts? Will these records also be included in the log backup?
sql-server transaction-log
asked Jan 26 '16 at 5:03
karun_rkarun_r
156312
What happens to the log records that maintain any open uncommitted transactions when a transaction log backup starts? Will these records also be included in the log backup?
sql-server transaction-log
sql-server transaction-log
asked Jan 26 '16 at 5:03
karun_rkarun_r
156312
asked Jan 26 '16 at 5:03
karun_rkarun_r
156312
asked Jan 26 '16 at 5:03
karun_rkarun_r
156312
asked Jan 26 '16 at 5:03
karun_rkarun_r
156312
asked Jan 26 '16 at 5:03
karun_rkarun_r
156312
156312
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Yes, active transactions are included in transaction log backups and this is how the database restore WITH NORECOVERY option works.
I think it's also important to understand how database restore operations and options work with SQL Server and that too will help you get a better understanding of some of this.
Quoted explanations and some key points to help clarify further
1. Transaction Log Backup
A transaction log backup allows you to backup the active part of the
transaction log. So after you issue a "Full" or "Differential" backup
the transaction log backup will have any transactions that were
created after those other backups completed. After the transaction
log backup is issued, the space within the transaction log can be
reused for other processes. If a transaction log backup is not taken,
the transaction log will continue to grow.
2. Log Backup Chain
A log backup chain is an unbroken series of log backups that contain
all the transaction log records necessary to recover a database to a
point in time. A chain starts with a full database backup, and
continues until something breaks the chain, thus preventing more log
backups being taken until another full (or differential) backup is
taken.
3. Truncating the Transaction Log
When SQL Server finishes backing up the transaction log, it
automatically truncates the inactive portion of the transaction log.
This inactive portion contains completed transactions and so is no
longer used during the recovery process. Conversely, the active
portion of the transaction log contains transactions that are still
running and have not yet completed. SQL Server reuses this truncated,
inactive space in the transaction log instead of allowing the
transaction log to continue to grow and use more space.
Additional Reading
- Understanding How Restore and Recovery of Backups Work in SQL
Server
answered Jan 26 '16 at 5:48
Pimp Juice ITPimp Juice IT
1,675614
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f127297%2fhow-does-a-transaction-log-backup-deal-with-active-log%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Yes, active transactions are included in transaction log backups and this is how the database restore WITH NORECOVERY option works.
I think it's also important to understand how database restore operations and options work with SQL Server and that too will help you get a better understanding of some of this.
Quoted explanations and some key points to help clarify further
1. Transaction Log Backup
A transaction log backup allows you to backup the active part of the
transaction log. So after you issue a "Full" or "Differential" backup
the transaction log backup will have any transactions that were
created after those other backups completed. After the transaction
log backup is issued, the space within the transaction log can be
reused for other processes. If a transaction log backup is not taken,
the transaction log will continue to grow.
2. Log Backup Chain
A log backup chain is an unbroken series of log backups that contain
all the transaction log records necessary to recover a database to a
point in time. A chain starts with a full database backup, and
continues until something breaks the chain, thus preventing more log
backups being taken until another full (or differential) backup is
taken.
3. Truncating the Transaction Log
When SQL Server finishes backing up the transaction log, it
automatically truncates the inactive portion of the transaction log.
This inactive portion contains completed transactions and so is no
longer used during the recovery process. Conversely, the active
portion of the transaction log contains transactions that are still
running and have not yet completed. SQL Server reuses this truncated,
inactive space in the transaction log instead of allowing the
transaction log to continue to grow and use more space.
Additional Reading
- Understanding How Restore and Recovery of Backups Work in SQL
Server
answered Jan 26 '16 at 5:48
Pimp Juice ITPimp Juice IT
1,675614
add a comment |
Yes, active transactions are included in transaction log backups and this is how the database restore WITH NORECOVERY option works.
I think it's also important to understand how database restore operations and options work with SQL Server and that too will help you get a better understanding of some of this.
Quoted explanations and some key points to help clarify further
1. Transaction Log Backup
A transaction log backup allows you to backup the active part of the
transaction log. So after you issue a "Full" or "Differential" backup
the transaction log backup will have any transactions that were
created after those other backups completed. After the transaction
log backup is issued, the space within the transaction log can be
reused for other processes. If a transaction log backup is not taken,
the transaction log will continue to grow.
2. Log Backup Chain
A log backup chain is an unbroken series of log backups that contain
all the transaction log records necessary to recover a database to a
point in time. A chain starts with a full database backup, and
continues until something breaks the chain, thus preventing more log
backups being taken until another full (or differential) backup is
taken.
3. Truncating the Transaction Log
When SQL Server finishes backing up the transaction log, it
automatically truncates the inactive portion of the transaction log.
This inactive portion contains completed transactions and so is no
longer used during the recovery process. Conversely, the active
portion of the transaction log contains transactions that are still
running and have not yet completed. SQL Server reuses this truncated,
inactive space in the transaction log instead of allowing the
transaction log to continue to grow and use more space.
Additional Reading
- Understanding How Restore and Recovery of Backups Work in SQL
Server
answered Jan 26 '16 at 5:48
Pimp Juice ITPimp Juice IT
1,675614
add a comment |
Yes, active transactions are included in transaction log backups and this is how the database restore WITH NORECOVERY option works.
I think it's also important to understand how database restore operations and options work with SQL Server and that too will help you get a better understanding of some of this.
Quoted explanations and some key points to help clarify further
1. Transaction Log Backup
A transaction log backup allows you to backup the active part of the
transaction log. So after you issue a "Full" or "Differential" backup
the transaction log backup will have any transactions that were
created after those other backups completed. After the transaction
log backup is issued, the space within the transaction log can be
reused for other processes. If a transaction log backup is not taken,
the transaction log will continue to grow.
2. Log Backup Chain
A log backup chain is an unbroken series of log backups that contain
all the transaction log records necessary to recover a database to a
point in time. A chain starts with a full database backup, and
continues until something breaks the chain, thus preventing more log
backups being taken until another full (or differential) backup is
taken.
3. Truncating the Transaction Log
When SQL Server finishes backing up the transaction log, it
automatically truncates the inactive portion of the transaction log.
This inactive portion contains completed transactions and so is no
longer used during the recovery process. Conversely, the active
portion of the transaction log contains transactions that are still
running and have not yet completed. SQL Server reuses this truncated,
inactive space in the transaction log instead of allowing the
transaction log to continue to grow and use more space.
Additional Reading
- Understanding How Restore and Recovery of Backups Work in SQL
Server
answered Jan 26 '16 at 5:48
Pimp Juice ITPimp Juice IT
1,675614
Yes, active transactions are included in transaction log backups and this is how the database restore WITH NORECOVERY option works.
I think it's also important to understand how database restore operations and options work with SQL Server and that too will help you get a better understanding of some of this.
Quoted explanations and some key points to help clarify further
1. Transaction Log Backup
A transaction log backup allows you to backup the active part of the
transaction log. So after you issue a "Full" or "Differential" backup
the transaction log backup will have any transactions that were
created after those other backups completed. After the transaction
log backup is issued, the space within the transaction log can be
reused for other processes. If a transaction log backup is not taken,
the transaction log will continue to grow.
2. Log Backup Chain
A log backup chain is an unbroken series of log backups that contain
all the transaction log records necessary to recover a database to a
point in time. A chain starts with a full database backup, and
continues until something breaks the chain, thus preventing more log
backups being taken until another full (or differential) backup is
taken.
3. Truncating the Transaction Log
When SQL Server finishes backing up the transaction log, it
automatically truncates the inactive portion of the transaction log.
This inactive portion contains completed transactions and so is no
longer used during the recovery process. Conversely, the active
portion of the transaction log contains transactions that are still
running and have not yet completed. SQL Server reuses this truncated,
inactive space in the transaction log instead of allowing the
transaction log to continue to grow and use more space.
Additional Reading
- Understanding How Restore and Recovery of Backups Work in SQL
Server
answered Jan 26 '16 at 5:48
Pimp Juice ITPimp Juice IT
1,675614
edited 1 min ago
answered Jan 26 '16 at 5:48
Pimp Juice ITPimp Juice IT
1,675614
answered Jan 26 '16 at 5:48
Pimp Juice ITPimp Juice IT
1,675614
answered Jan 26 '16 at 5:48
Pimp Juice ITPimp Juice IT
1,675614
1,675614
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f127297%2fhow-does-a-transaction-log-backup-deal-with-active-log%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
