How to maintain the "transaction log" of SQL Server"

Transaction logs are very important but often ignored in the database structure. 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. The cut-off method is to first mark the old record as inactive, and then overwrite the new log to the location of the old log, so as to 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.

 

You can use the DBCC shrinkdatabase statement to compress the transaction log file of the current database. The DBCC shrinkfile statement is used to compress the specified transaction log file. In addition, you can activate the automatic compression operation in the database. When logs are compressed, the old records are marked as inactive and deleted permanently. Depending on the compression method used, you may not see the result immediately. Under ideal conditions, the compression should be performed when the system is not very busy; otherwise, the database performance may be affected.

 

Restore database

 

Transaction Record backup can be used to restore the database to a specified state, but the transaction record backup itself is not enough to complete the task of restoring the database, and the backup data files need to be involved in the recovery. When restoring the database, the data file is first restored. Do not set the data file to the finished state before it is restored. Otherwise, the transaction log will not be restored. When the data file is restored, the system restores the database to the desired state through the transaction log backup. If multiple log files are backed up after the last database backupProgramIt will be restored according to the time they were created.

 

Another process called Log shipping can provide stronger database backup capabilities. After log shipping is configured, it can copy the entire database to another server. In this case, transaction logs are also regularly sent to the backup server for data recovery. This keeps the server in the Hot Backup state and updates the server when the data changes. Another server is called a monitoring server. It can be used to monitor shipping signals sent at specified intervals. If no signal is received within the specified time, the monitoring server records the event to the event log. This mechanism makes log shipping often a solution used in disaster recovery plans.