I'm a beginner in transaction, just a question on transaction log. We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical files? If it is in RAM and when system failure happens, obviously the RAM will be re-erased so we lose the transaction information, so how can we recover the commit?
You can find a pretty comprehensive guide to this question here, but to summarise, SQL Server will not return control to the application that committed a transaction until that transaction has been hardened to disk. Specifically, once it has been hardened to the transaction log file, control can be returned.
The data, at this point, may not be hardened into the data file, it may still be in the data buffer cache, but because it has been hardened into the transaction log then database recovery, in the event of a failure, can recover this transaction and persist the changes safely.
There is a log buffer cache in memory used to reduce the performance impacts of the sequential writes to transaction logs. The buffer is flushed to disk on several conditions, but one of them is a transaction commit. Until this data has been hardened, control is not returned to the caller, so even if you have a failure during this buffer flush the transactional consistency is maintained because this transaction is not yet considered committed. You will lose the data changes in that transaction, but as it was not committed, your application would already consider those changes lost as the commit was never completed.
A database is made up two files, a data file and a transaction log file. These are stored on disk.
Each database has a log cache in RAM, when a transaction is committed it gets moved to the log cache waiting to be flushed to disk. Therefore temporarily when a transform has been committed and waiting to be flushed to disk, it is in memory, however ultimately it is stored on disk in the file, not RAM.
I am oversimplifying here, I suggest you read up on transaction logs, I recommend one of Paul Randals lectures here
As written from the other people the transaction log will always be written to the disk, before a
COMMIT returns control to your application. For this reason the log file should be always placed on a fast SSD.
But for the sake of completeness: With at least Windows Server 2016 plus SQL Server 2016 you could add NVDIMMs (non-volatile DIMM = Flash or battery backed RAM) to your server. In this case SQL Server would use this NVDIMMs to place the "hot" transaction log tail on the NVDIMMs, since they survive a power-off-event per definition.
This would increase the speed of writes drastically (since RAM is much faster than even a SSD), but you will only mention it on a database with many small writes / commits (e.g. the database behind a busy online shop or an online game with many concurrent players).