Transaction logs is a part of a database structure but often ignored. Since it is not active as Schema in the database, few people pay attention to the transaction log.
The transaction log is a record made for database changes, which can record any operations for the database and save the records in a separate file. For any of every transaction, the transaction log has a very comprehensive record, and the data file can be restored to the status before the transaction based on these records. From the trading action, the transaction log is in a recording state, and any operation of the database during the transaction process is in the record range until the user clicks on commit or retreats to end the record. Each database has at least one transaction log and a data file.
For performance, SQL Server uses the user's change in the cache, which will immediately write to the transaction log, but not to write to the data file immediately. The transaction log will determine if a transaction has written data files in the cache. When SQL Server is restarted, it will view the latest tag points in the log and erase the transaction records behind this tag, because these transaction records do not really write data in the cache to data files. This prevents the transaction of those interrupted to modify the data file.
Maintenance trading logs because many people often forget the trading log, so it will bring some problems to the system. With the continuous operation of the system, the content of logging will be increasing, and the volume of log files will become larger and larger, which ultimately leads to insufficient disk space available. The log file will eventually invade all free space within the partition in everyday work. The default configuration of the log is an unlimited capacity. If you work in this configuration, it will continue to expand, and it will eventually occupy all available space. Both cases cause the database to stop working.
The daily backup work of the transaction log can effectively prevent the log file from excessive disk space. The backup process will cut the parts that are no longer needed in the log. The method of cutting is to first mark the old record as an inactive state, then cover the new log to the position of the old log, so that the volume of the transaction log is constantly expanding. It is best to set the database to "Simple Recovery Mode" if it is unable to make a backup of the log. In this mode, the system will force the trading log to automatically cut an operation when the markup is recorded, and the old log is overwritten in the new log.
When the cutout process occurs when the backup is backed up or labeled in an inactive state, it can be covered by the old transaction record, but this does not reduce the disk space occupied by the actual transaction log. Even if you no longer use the log, it will still occupy a certain space. Therefore, it is also necessary to compress the transaction log in maintenance. The method of compressing the transaction log is to delete non-active records, thereby reducing the physical hard disk space occupied by the log file.
By using the DBCC SHRINKDATABASE statement, the current database can be compressed, the DBCC ShrinkFile statement is used to compress the specified transaction log file, and the automatic compression operation can be activated in the database. When the log is compressed, the old record is marked as an inactive state, and then the record with the non-active tag is completely deleted. Depending on the compression method used, you may not see the results immediately. Ideally, the compression work should be selected in the system not very busy, otherwise it may affect database performance.
Recovery 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 file is required to participate in the recovery work. When restoring the database, the restoration of the data file is first performed. Before the entire data file is restored, do not set it to the completion status, otherwise the transaction log will not be recovered. When the data file is restored, the system will return the database to the user's desired state via backup of the transaction log. If there is a backup of multiple log files after the last backup of the database, the backup program will restore it in turn according to the time they established.
Another process known as log shipping can provide stronger database backup capabilities. When log shipping is configured, it can copy the entire database to another server. In this case, the transaction log will also send it to the backup server for recovery data. This allows the server to be in a thermal backup state, and it is also updated when the data changes. Another server is called a monitor server, which can be used to monitor the shipping signal sent by the specified time interval. If the signal is not received within the specified time, the monitoring server records this event to the event log. This mechanism allows log shipping to become a scheme used in disaster recovery plans. Performance Optimization The transaction log has an important role in the database, and it also has a certain impact on the overall performance of the system. With a few options, we can optimize the performance of the transaction log. Since the transaction log is a continuous disk write process, reading actions will not occur in this. Therefore, the log file is placed in a separate disk and has a role in optimization performance.
Another optimization measures are related to the volume of the log file. We can set the volume of the log file does not exceed a few percent of the hard disk space, or determine its size. If you have a waste of disk space, if you set too small, you will force the record file to try to expand, resulting in decline in database performance. Transaction log file Transaction log file is the file used to record the database update, extension is LDF. In SQL Server 7.0 and SQL Server 2000, if automatic growth is set, the transaction log file will extends automatically. In general, the size of the transaction log is stable when it is possible to accommodate the maximum number of transactions between the truncation of the transaction log, and the transaction log truncated by the checkpoint or transaction log backup. However, in some cases, the transaction log may become very large, in order to use the space or full. Typically, you will receive the following error message in the transaction log files and cannot expand it, you will receive the following error message: Error: 9002, Severity: 17, State: 2The log file for database '%. * Ls' is full. Except In addition to this error message, SQL Server may also mark the database as Suspect due to lack of transaction log extension space. For additional information about how to recover from this situation, see the "Insufficient Disk Space" topic in SQL Server online help. In addition, transaction log extensions can cause the following scenarios: Very large transaction log file. · The transaction may fail and may start rollback. · The transaction may be used for a long time. · Performance issues may occur. · Blocking phenomenon may occur. Cause Transaction log extensions may occur due to the following reasons or situations: • Uncommitted transactions · Very large transactions · Operation: DBCC DBREINDEX and CREATE INDEX · When restored from the transaction log Backup · Client Application does not process all results · Query Before the transaction log completes the extension, you receive a false "log full" error message · The unproduced transaction solution log file is full, the SQL database cannot be written to the file, the two methods can be used: a method: empty the log . 1. Open the query analyzer, enter the command dump transaction database name with no_log2. Open Enterprise Manager - Right click on the database you want to compress - All Tasks - Shrine Database - Shrink File - Select Log File - In the Contincture Mode Select to shrink to XXM, here a minimum M number allowed to shrink, enter this number directly, and determine it. Another method has certain risk, because SQL Server log files are not instantewasked to the database main file, such as improper handling, resulting in data loss. 1: Delete LOG Separate Database Enterprise Manager -> Server -> Database -> Right-click -> Separate Database 2: Delete Log File Attached Database Enterprise Manager -> Server -> Database -> Right-click -> Additional Database This method is new Log, only more than 500 K. Note: It is recommended to use the first method. If you don't want it to become bigger. Use under SQL2000: Right-click on the database -> Properties -> Options -> Fault Recovery - Model - Select - Simple Model.
