Mixing
Delayed durability - can data loss be prevented in case of crash
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
This book suggests that
"Data loss is possible with delayed durability because you might lose committed transactions. If SQL Server crashes after a transaction commits, but before the 60KB buffer is filled, that transaction cannot be recovered because it is lost."
However wont the transactions be available on the transaction log and can be replayed for recovery?
https://books.google.co.in/books?id=5Es3DwAAQBAJ&pg=PT159&lpg=PT159&dq="data+loss+is+possible+with+delayed+durability"&source=bl&ots=Mwmh4cKa6G&sig=tctRb18itYQyBxo7lLhkT1w4-z4&hl=en&sa=X&ved=2ahUKEwjAyau784jeAhWHMo8KHW1XCi4Q6AEwAHoECAEQAQ#v=onepage&q="data%20loss%20is%20possible%20with%20delayed%20durability"&f=false
sql-server transaction-log
asked 1 hour ago
variable
1929
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
1
down vote
favorite
This book suggests that
"Data loss is possible with delayed durability because you might lose committed transactions. If SQL Server crashes after a transaction commits, but before the 60KB buffer is filled, that transaction cannot be recovered because it is lost."
However wont the transactions be available on the transaction log and can be replayed for recovery?
https://books.google.co.in/books?id=5Es3DwAAQBAJ&pg=PT159&lpg=PT159&dq="data+loss+is+possible+with+delayed+durability"&source=bl&ots=Mwmh4cKa6G&sig=tctRb18itYQyBxo7lLhkT1w4-z4&hl=en&sa=X&ved=2ahUKEwjAyau784jeAhWHMo8KHW1XCi4Q6AEwAHoECAEQAQ#v=onepage&q="data%20loss%20is%20possible%20with%20delayed%20durability"&f=false
sql-server transaction-log
asked 1 hour ago
variable
1929
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
I see most of your questions are based on the doubts that you have with 1 book. Have you tried to look around e.g. blog posts to help answer/clear your doubts ? E.g. sqlskills.com/blogs/paul/delayed-durability-sql-server-2014 and sqlperformance.com/2014/04/io-subsystem/…
– Kin
1 hour ago
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
This book suggests that
"Data loss is possible with delayed durability because you might lose committed transactions. If SQL Server crashes after a transaction commits, but before the 60KB buffer is filled, that transaction cannot be recovered because it is lost."
However wont the transactions be available on the transaction log and can be replayed for recovery?
https://books.google.co.in/books?id=5Es3DwAAQBAJ&pg=PT159&lpg=PT159&dq="data+loss+is+possible+with+delayed+durability"&source=bl&ots=Mwmh4cKa6G&sig=tctRb18itYQyBxo7lLhkT1w4-z4&hl=en&sa=X&ved=2ahUKEwjAyau784jeAhWHMo8KHW1XCi4Q6AEwAHoECAEQAQ#v=onepage&q="data%20loss%20is%20possible%20with%20delayed%20durability"&f=false
sql-server transaction-log
asked 1 hour ago
variable
1929
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
This book suggests that
"Data loss is possible with delayed durability because you might lose committed transactions. If SQL Server crashes after a transaction commits, but before the 60KB buffer is filled, that transaction cannot be recovered because it is lost."
However wont the transactions be available on the transaction log and can be replayed for recovery?
https://books.google.co.in/books?id=5Es3DwAAQBAJ&pg=PT159&lpg=PT159&dq="data+loss+is+possible+with+delayed+durability"&source=bl&ots=Mwmh4cKa6G&sig=tctRb18itYQyBxo7lLhkT1w4-z4&hl=en&sa=X&ved=2ahUKEwjAyau784jeAhWHMo8KHW1XCi4Q6AEwAHoECAEQAQ#v=onepage&q="data%20loss%20is%20possible%20with%20delayed%20durability"&f=false
sql-server transaction-log
sql-server transaction-log
asked 1 hour ago
variable
1929
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 hour ago
variable
1929
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 hour ago
variable
1929
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 hour ago
variable
1929
asked 1 hour ago
variable
1929
1929
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
I see most of your questions are based on the doubts that you have with 1 book. Have you tried to look around e.g. blog posts to help answer/clear your doubts ? E.g. sqlskills.com/blogs/paul/delayed-durability-sql-server-2014 and sqlperformance.com/2014/04/io-subsystem/…
– Kin
1 hour ago
add a comment |Â
2
I see most of your questions are based on the doubts that you have with 1 book. Have you tried to look around e.g. blog posts to help answer/clear your doubts ? E.g. sqlskills.com/blogs/paul/delayed-durability-sql-server-2014 and sqlperformance.com/2014/04/io-subsystem/…
– Kin
1 hour ago
2
2
I see most of your questions are based on the doubts that you have with 1 book. Have you tried to look around e.g. blog posts to help answer/clear your doubts ? E.g. sqlskills.com/blogs/paul/delayed-durability-sql-server-2014 and sqlperformance.com/2014/04/io-subsystem/…
– Kin
1 hour ago
I see most of your questions are based on the doubts that you have with 1 book. Have you tried to look around e.g. blog posts to help answer/clear your doubts ? E.g. sqlskills.com/blogs/paul/delayed-durability-sql-server-2014 and sqlperformance.com/2014/04/io-subsystem/…
– Kin
1 hour ago
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
3
down vote
When you enable delayed durability, transactions aren't written to the transaction log file before they are committed.
That's why transactions aren't available in the log, and they will be permanently lost in that scenario.
answered 1 hour ago
Brent Ozar
32.9k1998228
Isnt the delay is to write to the disk? Instead of to the log?
– variable
1 hour ago
1
@variable the log file IS on disk. That's where it lives. I think you're misunderstanding the difference between the log file and the data pages. SQL Server has never held up transactions to write to the data pages (MDF/NDF files) - those are written asynchronously long after the transaction finishes.
– Brent Ozar
1 hour ago
Sorry I meant that: the delay is to write to the data page (disk). I thought it immeidately write to the transaction log as usual. I thought the delay was to write the changes to the data page. Is my understsnding incorrect?
– variable
1 hour ago
3
@variable Brent's answer is short, but accurate. If you want a more detailed answer, check out the links that Kin posted in the comments on your question. (This sqlperformance.com/2014/04/io-subsystem/… and this sqlskills.com/blogs/paul/delayed-durability-sql-server-2014). The whole point of delayed durability is that it delays writing your transaction to disk AT ALL--not even in the transaction log. Hence the durability of your transaction being delayed.
– AMtwo
45 mins ago
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
When you enable delayed durability, transactions aren't written to the transaction log file before they are committed.
That's why transactions aren't available in the log, and they will be permanently lost in that scenario.
answered 1 hour ago
Brent Ozar
32.9k1998228
Isnt the delay is to write to the disk? Instead of to the log?
– variable
1 hour ago
1
@variable the log file IS on disk. That's where it lives. I think you're misunderstanding the difference between the log file and the data pages. SQL Server has never held up transactions to write to the data pages (MDF/NDF files) - those are written asynchronously long after the transaction finishes.
– Brent Ozar
1 hour ago
Sorry I meant that: the delay is to write to the data page (disk). I thought it immeidately write to the transaction log as usual. I thought the delay was to write the changes to the data page. Is my understsnding incorrect?
– variable
1 hour ago
3
@variable Brent's answer is short, but accurate. If you want a more detailed answer, check out the links that Kin posted in the comments on your question. (This sqlperformance.com/2014/04/io-subsystem/… and this sqlskills.com/blogs/paul/delayed-durability-sql-server-2014). The whole point of delayed durability is that it delays writing your transaction to disk AT ALL--not even in the transaction log. Hence the durability of your transaction being delayed.
– AMtwo
45 mins ago
add a comment |Â
up vote
3
down vote
When you enable delayed durability, transactions aren't written to the transaction log file before they are committed.
That's why transactions aren't available in the log, and they will be permanently lost in that scenario.
answered 1 hour ago
Brent Ozar
32.9k1998228
Isnt the delay is to write to the disk? Instead of to the log?
– variable
1 hour ago
1
@variable the log file IS on disk. That's where it lives. I think you're misunderstanding the difference between the log file and the data pages. SQL Server has never held up transactions to write to the data pages (MDF/NDF files) - those are written asynchronously long after the transaction finishes.
– Brent Ozar
1 hour ago
Sorry I meant that: the delay is to write to the data page (disk). I thought it immeidately write to the transaction log as usual. I thought the delay was to write the changes to the data page. Is my understsnding incorrect?
– variable
1 hour ago
3
@variable Brent's answer is short, but accurate. If you want a more detailed answer, check out the links that Kin posted in the comments on your question. (This sqlperformance.com/2014/04/io-subsystem/… and this sqlskills.com/blogs/paul/delayed-durability-sql-server-2014). The whole point of delayed durability is that it delays writing your transaction to disk AT ALL--not even in the transaction log. Hence the durability of your transaction being delayed.
– AMtwo
45 mins ago
add a comment |Â
up vote
3
down vote
up vote
3
down vote
When you enable delayed durability, transactions aren't written to the transaction log file before they are committed.
That's why transactions aren't available in the log, and they will be permanently lost in that scenario.
answered 1 hour ago
Brent Ozar
32.9k1998228
When you enable delayed durability, transactions aren't written to the transaction log file before they are committed.
That's why transactions aren't available in the log, and they will be permanently lost in that scenario.
answered 1 hour ago
Brent Ozar
32.9k1998228
answered 1 hour ago
Brent Ozar
32.9k1998228
answered 1 hour ago
Brent Ozar
32.9k1998228
answered 1 hour ago
Brent Ozar
32.9k1998228
32.9k1998228
Isnt the delay is to write to the disk? Instead of to the log?
– variable
1 hour ago
1
@variable the log file IS on disk. That's where it lives. I think you're misunderstanding the difference between the log file and the data pages. SQL Server has never held up transactions to write to the data pages (MDF/NDF files) - those are written asynchronously long after the transaction finishes.
– Brent Ozar
1 hour ago
Sorry I meant that: the delay is to write to the data page (disk). I thought it immeidately write to the transaction log as usual. I thought the delay was to write the changes to the data page. Is my understsnding incorrect?
– variable
1 hour ago
3
@variable Brent's answer is short, but accurate. If you want a more detailed answer, check out the links that Kin posted in the comments on your question. (This sqlperformance.com/2014/04/io-subsystem/… and this sqlskills.com/blogs/paul/delayed-durability-sql-server-2014). The whole point of delayed durability is that it delays writing your transaction to disk AT ALL--not even in the transaction log. Hence the durability of your transaction being delayed.
– AMtwo
45 mins ago
add a comment |Â
Isnt the delay is to write to the disk? Instead of to the log?
– variable
1 hour ago
1
@variable the log file IS on disk. That's where it lives. I think you're misunderstanding the difference between the log file and the data pages. SQL Server has never held up transactions to write to the data pages (MDF/NDF files) - those are written asynchronously long after the transaction finishes.
– Brent Ozar
1 hour ago
Sorry I meant that: the delay is to write to the data page (disk). I thought it immeidately write to the transaction log as usual. I thought the delay was to write the changes to the data page. Is my understsnding incorrect?
– variable
1 hour ago
3
@variable Brent's answer is short, but accurate. If you want a more detailed answer, check out the links that Kin posted in the comments on your question. (This sqlperformance.com/2014/04/io-subsystem/… and this sqlskills.com/blogs/paul/delayed-durability-sql-server-2014). The whole point of delayed durability is that it delays writing your transaction to disk AT ALL--not even in the transaction log. Hence the durability of your transaction being delayed.
– AMtwo
45 mins ago
Isnt the delay is to write to the disk? Instead of to the log?
– variable
1 hour ago
Isnt the delay is to write to the disk? Instead of to the log?
– variable
1 hour ago
1
1
@variable the log file IS on disk. That's where it lives. I think you're misunderstanding the difference between the log file and the data pages. SQL Server has never held up transactions to write to the data pages (MDF/NDF files) - those are written asynchronously long after the transaction finishes.
– Brent Ozar
1 hour ago
@variable the log file IS on disk. That's where it lives. I think you're misunderstanding the difference between the log file and the data pages. SQL Server has never held up transactions to write to the data pages (MDF/NDF files) - those are written asynchronously long after the transaction finishes.
– Brent Ozar
1 hour ago
Sorry I meant that: the delay is to write to the data page (disk). I thought it immeidately write to the transaction log as usual. I thought the delay was to write the changes to the data page. Is my understsnding incorrect?
– variable
1 hour ago
Sorry I meant that: the delay is to write to the data page (disk). I thought it immeidately write to the transaction log as usual. I thought the delay was to write the changes to the data page. Is my understsnding incorrect?
– variable
1 hour ago
3
3
@variable Brent's answer is short, but accurate. If you want a more detailed answer, check out the links that Kin posted in the comments on your question. (This sqlperformance.com/2014/04/io-subsystem/… and this sqlskills.com/blogs/paul/delayed-durability-sql-server-2014). The whole point of delayed durability is that it delays writing your transaction to disk AT ALL--not even in the transaction log. Hence the durability of your transaction being delayed.
– AMtwo
45 mins ago
@variable Brent's answer is short, but accurate. If you want a more detailed answer, check out the links that Kin posted in the comments on your question. (This sqlperformance.com/2014/04/io-subsystem/… and this sqlskills.com/blogs/paul/delayed-durability-sql-server-2014). The whole point of delayed durability is that it delays writing your transaction to disk AT ALL--not even in the transaction log. Hence the durability of your transaction being delayed.
– AMtwo
45 mins ago
add a comment |Â
variable is a new contributor. Be nice, and check out our Code of Conduct.
variable is a new contributor. Be nice, and check out our Code of Conduct.
variable is a new contributor. Be nice, and check out our Code of Conduct.
variable is a new contributor. Be nice, and check out our Code of Conduct.
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
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f220151%2fdelayed-durability-can-data-loss-be-prevented-in-case-of-crash%23new-answer', 'question_page');
);
Post as a guest
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
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
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
2
I see most of your questions are based on the doubts that you have with 1 book. Have you tried to look around e.g. blog posts to help answer/clear your doubts ? E.g. sqlskills.com/blogs/paul/delayed-durability-sql-server-2014 and sqlperformance.com/2014/04/io-subsystem/…
– Kin
1 hour ago