Overview of MS SQL Server's transaction log

The transaction log, or Transaction logs, is a very important but often overlooked part of the database structure. Because it is not as active as the schema in the database, few people are concerned about the transaction log.

The transaction log is a record of changes to a database that can record any action against the database and save the record results in a separate file. For any transaction process, the transaction log has a very comprehensive record that can be used to restore data files to the state before the transaction. The transaction log is in the record State from the trading action, and any operations on the database during the transaction are recorded, until the user clicks on the submit or backward to complete the record. Each database has at least one transaction log and one data file.

For performance reasons, SQL Server stores user changes in the cache, and these changes are immediately written to the transaction log, but are not immediately written to the data file. The transaction log will pass a marker point to determine whether a trade has written data from the cache to the data file. When SQL Server restarts, it looks at the most recent tag points in the log and erases the transactions that follow the mark Point, because these transactions do not actually write data from the cache to the data file. This prevents the interrupted transaction from modifying the data file.

Maintain the transaction log

Because many people often forget the transaction log, it also brings some problems to the system. As the system continues to run, the content of the log records will be more and more, the volume of log files will become larger, resulting in insufficient free disk space. Unless the log is regularly cleaned up in daily work, the log file will eventually encroach on all available space within the partition. The default configuration for logging is unlimited, and if you work with this configuration, it expands and eventually occupies all of the available space. Both of these situations will cause the database to stop working.

The day-to-day backup of the transaction log can effectively prevent it from consuming excessive disk space. The backup process truncates the part of the log that is no longer needed. The method of truncation is to mark the old record as inactive first, and then overwrite the new log to the location of the stale logs, thus preventing the volume of the transaction log from expanding. If you cannot perform regular backups of the log, it is a good idea to set the database to the simple recovery model. In this mode, the system forces the transaction log to automatically truncate every time a mark point is logged, overwriting the old log with a new journal.

The truncation process occurs when the backup or the old Mark Point is not active, which makes the old transaction available for overwriting, but does not reduce the amount of disk space actually occupied by the transaction log. Even if you no longer use the log, it will still occupy a certain space. As a result, the transaction log is also compressed for maintenance. The method of compressing the transaction log is to delete inactive records, thereby reducing the physical hard disk space occupied by the log files.

You can compress the transaction log file for the current database by using the DBCC SHRINKDATABASE statement, which is used to compress the specified transaction log file, or to activate the automatic compression operation in the database. When you compress a log, you first mark the old record as inactive, and then delete the record with the inactive tag completely. Depending on the compression method you use, you may not see the results immediately. Ideally, the compression work should be done in a time when the system is not very busy, or it may affect database performance.

Restore Database

Transaction backups can be used to restore a database to a specified state, but the transaction backup itself is not sufficient to complete the task of restoring the database, and the backed-up data files are required to participate in the recovery effort. When you restore a database, the first thing you do is restore your data files. Do not set the entire data file to completion until it is restored, otherwise the transaction log will not be restored. When the data file is restored, the database is restored to the user's desired state through a backup of the transaction log. If multiple log files are backed up after the last backup of the database, the backup program restores them in turn according to the time they were established.

Another process known as log shipping can provide a stronger database backup capability. When log shipping is configured, it can replicate the entire database to another server. In this case, the transaction log is also sent to the backup server on a regular basis to restore data usage. This keeps the server in a hot backup state, and it is updated as the data changes. Another server, called a monitor server, can be used to monitor shipping signals sent at specified intervals. If no signal is received within the specified time, the monitor server logs the event to the event log. This mechanism makes log shipping often a scenario used in a disaster recovery plan.