Summary of SQL Server log files and handling methods

Transaction Log (Transaction logs) is DatabaseStructure that is very important but often ignored. Since it is not as active as the schema in the database, few people are concerned about transaction logs.

Transaction logs are records of database changes. They can record any operations on the database and save the record results in an independent file. Transaction logs are fully recorded for every transaction process. Based on these records, data files can be restored to the pre-transaction status. Starting from the transaction action, the transaction log is recorded, and any operations on the database during the transaction are within the record range until the user clicks submit or back to complete the record. Each database has at least one transaction log and one data file.

For performance considerations, SQL Server saves user changes to the cache. These changes are immediately written into the transaction log, but are not immediately written into the data file. The transaction log uses a tag to determine whether a transaction has written cached data to a data file. After the SQL Server restarts, it will view the latest mark point in the log and erase the transaction record after the mark point, because these transaction records do not actually write data in the cache into the data file. This prevents the interrupted transactions from modifying data files.

Maintain transaction logs
Because many people often forget the transaction log, it also brings some problems to the system. As the system continues to run, more and more logs are recorded, and the size of log files increases, leading to insufficient available disk space. Unless logs are frequently cleared during daily work, log files will eventually occupy all available space in the partition. The default log configuration is unlimited capacity. If you use this configuration, it will continue to expand and eventually occupy all the available space. Both cases will cause the database to stop working.

Daily transaction log backup can effectively prevent log files from consuming disk space too much. The backup process removes unnecessary parts of the log. First, mark the old record as non- ActivityStatus, and then overwrite the new log to the location of the old log, which can prevent the transaction log from expanding. If you cannot regularly back up logs, it is best to set the database to "simple recovery mode ". In this mode, the system will force the transaction log to automatically cut off each time the mark point is recorded to overwrite the old log with the new log.

The cut-off process occurs when the backup or marking the old tag as inactive, so that the old transaction record can be overwritten, but this does not reduce the disk space actually occupied by the transaction log. Even if the log is no longer used, it still occupies a certain amount of space. Therefore, transaction logs must be compressed during maintenance. Transaction logs can be compressed by deleting non-active records to reduce the physical hard disk space occupied by log files.