![]() The log with the lowest LSN specifies the start of the active log that may be needed for any database activity, and the log with the highest LSN specifies the end of that active log. Once the transaction log record inserted into the log file, it will be marked with a specific value called Logical Sequence Number (LSN). Log truncation doesn’t mean that the transaction log size will be reduced, only it will be marked as inactive to be used in future. In order to mark these VLFs as inactive, it shouldn’t contain any active log related to an opened transaction, as these logs may be used for redone or redo process. The transaction log file internally consists of small units called Virtual Log Files (VLF).Īnd the process in which SQL Server marks these VLFs as inactive, making it available for reuse by the database is called Truncation. It is recommended also to keep the transaction log file in a separate drive from the database data files, as placing both data and log files on the same drive can result poor database performance. It will be useful if the transaction log file become full and you need to create another file in a different disk that has more space. No performance gain can be taken from having multiple transaction log files in the database, as the logs will be written one at a time. In this way SQL Server guarantees the database consistency.ĭata is written to the database data files randomly, but the logs are written to the transaction log file sequentially one by one, making the log write process faster. On the other hand, any data changes associated with uncommitted transaction will be rolled back by reversing the operation written in the transaction log file. ![]() The transaction log is very useful when a hardware or application failures occur, by restoring the database to a previous point in time, as the log will be written to the log file before writing the data in the buffer cache to the data file.ĭuring the database recovery process, any transaction that is committed, without reflecting the data changes to the data files due to failure will be redone again, as the record already written to the database transaction log file before writing it to the data file. The SQL Server transaction log is an important database component that is used to make sure that the committed transaction’s data is preserved and the uncommitted transaction’s change will be rolled back. SQL Server transaction log contains metadata about the recorded transaction, such as the transaction ID, the transaction start and end time, the changed pages and the data changes. SQL Server databases consist of two physical files types the data file in which the data and the database objects such as tables and indexes are stored, and the transaction log file in which SQL Server stores records for all transactions performed on that database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |