It is a very popular database development task of loading data from the database to files directly. But how does it happen? Is it implemented immediately or the data is stored in memory first and transited to files later? Which event causes data transfer to transactions registration table and to files? Let’s see how it goes.

Imagine that in your database development process you have a database with full recovery model and you are going to load data to files. Note that the data is being read or loaded part by part and it is a slow process. Redo is being written by commits – a continuous process of writing small data parts. If something goes wrong with data load (e.g. electricity problems), redo logs will be applied at sql server start and all the committed transactions will be restored, not committed transactions will be rolled back and the database itself will be consistent. In terms of database development, redo phase applies the logged transactions to the data copied from the backup to roll forward that data to the recovery point.

Actually, there are 2 things to know for implementing safety data loading to files:

· log-files – the most important part

· recent cache log data

Logs contain all the actions: inserts, updates, etc. and it is possible to recover the data for any time since database creating to the current moment.

Now let’s look at the specific example: let’s assume that we have a transaction that is going to INSERT a single row. To keep things simple, let’s assume there is no other activity going on and that the data cache and log buffer is empty. When a transaction begins, here is what happens:

– A BeginTran log record is written to the log buffer.
– A data page is retrieved from disk and put into a page of the data cache.
– SQL Server then creates an INSERT log record, which is then added to the log buffer.
– The new row is INSERTed into the data page stored in the data cache and the page is marked as being dirty.

A Commit log is created after that and written to the log buffer and all of the log records associated with the transaction are written to the transaction log file from the log buffer. The data transaction is considered to be completed since that.

Please note, that it is very important to keep data (MDF, NDF) and log (LDF) files separated in order to reduce IO contention. Each log file should be on its own array, as mixing multiple log files on the same array can introduce a lot of random reads and writes. The more log files there are on the same shared array, the bigger this problem becomes. This process can be easily established by Forrards database consultancy – just contact us and get it done.

‍