[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

SQL Locate data log files

I understand how SQL has data files and log files. Now let's say we have the recovery model set to full and transaction log backups are done every hour. People make changes to a customer record once every hour over a five hour period. Now I come along and look for data for this customer. SQL goes into the index to get the original data but then what does it do to get the msot recent version? Does it go though each transaction log file to get the final version?
0
rwheeler23
Asked:
rwheeler23
  • 2
1 Solution
 
sventhanCommented:
If the data is available (committed/checkpoint)  then it comes from the datafile. Whenever checkpoint happen the committed data is flushed from the log file to data file.

Mostly you will not touch the transaction log file when you search for the data.
0
 
sventhanCommented:
SQL Server keeps a buffer of all of the changes to data for performance reasons. It writes items to the transaction log immediately, but does not write changes to the data file immediately. A checkpoint is written to the transaction log to indicate that a particular transaction has been completely written from the buffer to the data file. When SQL Server is restarted, it looks for the most recent checkpoint in the transaction log and rolls forward all transactions that have occurred from that point forward since it is not guaranteed to have been written to the data file until a checkpoint is entered in the transaction log


http://www.techrepublic.com/article/understanding-the-importance-of-transaction-logs-in-sql-server/5173108
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now