[IT168 Server Academy] Transaction logs are a very important but often ignored part of the database structure. Since it is not as active as the schema in the database, few people pay attention to the transaction log.
The transaction log is a record of database changes. It can record any operation on the database and save the recording results in a separate file. For every transaction process, the transaction log has very comprehensive records, and the data files can be restored to their pre-transaction state based on these records. From the beginning of the transaction action, the transaction log is in a recording state. Any operations on the database during the transaction are within the recording scope. The recording is not completed until the user clicks submit or back. Each database has at least one transaction log and one data file.
For performance reasons, SQL Server stores user changes in the cache. These changes are immediately written to the transaction log, but not to the data file. The transaction log uses a marking point to determine whether a transaction has written data from the cache to the data file. When SQL Server restarts, it will check the latest mark point in the log and erase the transaction records after this mark point, because these transaction records do not actually write the data in the cache to the data file. This prevents those interrupted transactions from modifying the data files.
Maintain transaction log
Because many people often forget transaction log, so it can also bring some problems to the system. As the system continues to run, more and more log records will be recorded, and the size of the log files will also become larger and larger, eventually leading to insufficient available disk space. Unless the logs are cleaned frequently in daily work, the log files will eventually occupy all the available space in the partition. The default configuration of the log is unlimited capacity. If you work in this configuration, it will continue to expand and eventually occupy all available space. Both situations can cause the database to stop working.
Routine backup of transaction logs can effectively prevent log files from consuming excessive disk space. The backup process truncates portions of the log that are no longer needed. The method of truncation is to first mark the old records as inactive, and then overwrite the new logs at the location of the old logs, thus preventing the transaction log from growing in size. If regular backups of the log cannot be performed, it is best to set the database to "simple recovery model". In this mode, the system will force the transaction log to automatically truncate each time a mark point is recorded, overwriting the old log with a new log.
The truncation process occurs when backing up or marking old points as inactive, which allows old transaction records to be overwritten, but does not reduce the actual disk space occupied by the transaction log. Even if the log is no longer used, it will still occupy a certain amount of space. Therefore, during maintenance, the transaction log also needs to be compressed. Transaction logs are compressed by deleting inactive records, thereby reducing the physical hard drive space occupied by the log files.
The transaction log file of the current database can be compressed by using the DBCC SHRINKDATABASE statement. The DBCC SHRINKFILE statement is used to compress the specified transaction log file. In addition, the automatic compression operation can also be activated in the database. When a log is compressed, old records are first marked as inactive, and then records marked as inactive are completely deleted. Depending on the compression method used, you may not see results immediately. Ideally, compression work should be performed during periods when the system is not very busy, otherwise it may affect database performance.
Restoring database
transaction record backup can be used to restore the database to a specified state, but transaction record backup itself is not enough to complete the task of restoring the database, and the backed up data files also need to participate in the recovery work. When restoring a database, the first step is to restore the data files. Do not set the entire data file to the completed state until the entire data file has been restored, otherwise the transaction log will not be restored. When the data file recovery is completed, the system will restore the database to the state desired by the user through the backup of the transaction log. If there are backups of multiple log files after the last backup of the database, the backup program will restore them in sequence according to the time they were created.
Another process called 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, transaction logs are also periodically sent to the backup server for data recovery. This keeps the server in a hot backup state, updating it as data changes. The other server is called the monitor server and can be used to monitor shipping signals sent at specified intervals. If no signal is received within the specified time, the monitoring server will log this event to the event log. This mechanism makes log shipping often used in disaster recovery plans.
Performance optimization
transaction log plays an important role in the database, and it also has a certain impact on the overall performance of the system. Through several options, we can optimize the performance of the transaction log. Since the transaction log is a continuous disk writing process, no reading occurs during this process. Therefore, placing the log file on an independent disk will play a certain role in optimizing performance.
Another optimization measure relates to log file size. We can set the size of the log file to not exceed a few percent of the hard disk space, or determine its size. If it is set too high, it will waste disk space, and if it is set too small, it will force the log file to continuously try to expand, causing database performance to decrease.
Transaction Log File Transaction Log File is a file used to record database updates, with an extension of ldf.
In SQL Server 7.0 and SQL Server 2000, if the auto-grow feature is set, the transaction log file will automatically expand.
In general, the size of the transaction log is stable when it can accommodate the maximum number of transactions that occur between transaction log truncation, which is triggered by a checkpoint or transaction log backup.
However, in some cases, the transaction log can become so large that it runs out of space or becomes full. Typically, when the transaction log file fills up the available disk space and can no longer be expanded, you will receive an error message like the following:
Error:9002, Severity:17, State:2
The log file for database '%.*ls' is full.
In addition to this error message, SQL Server may 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 "Low Disk Space" topic in SQL Server Online Help.
In addition, transaction log expansion may cause the following situations:
· Very large transaction log files.
· The transaction may fail and may start rolling back.
· Transactions may take a long time to complete.
· Performance issues may occur.
· Blockage may occur.
Cause Transaction log expansion can occur due to the following reasons or situations:
· Uncommitted transactions · Very large transactions · Operations: DBCC DBREINDEX and CREATE INDEX
· When restoring from a transaction log backup · The client application does not process all results · The query times out before the transaction log has completed expanding and you receive a false "Log Full" error message · Unreplicated transaction resolution caused by the log file being full When the SQL database cannot write to the file, there are two methods available:
One way: clear the log.
1. Open the query analyzer and enter the command DUMP TRANSACTION database name WITH NO_LOG
2. Open the Enterprise Manager again--right-click the database you want to compress--All tasks--Shrink the database--Shrink files--Select the log file--Select shrink to XXM in the shrink mode, and a permission to shrink will be given here. To reach the minimum number of M, directly enter this number and confirm.
The other method has certain risks, because the log file of SQL SERVER is not written to the main database file immediately. If it is not handled properly, it will cause data loss.
1: Delete LOG
Detach database Enterprise Manager -> Server -> Database -> Right click -> Detach database 2: Delete LOG file Attach database Enterprise Manager -> Server -> Database -> Right click -> Attach database This method generates a new LOG, the size is only More than 500K.
Note: It is recommended to use the first method.
If in the future, you don't want it to get bigger.
Used under SQL2000:
Right-click on the database->Properties->Options->Failure Recovery-Model-Select-Simple Model.
Or use SQL statement:
alter database database name set recovery simple
In addition, as shown in the figure above, the database properties have two options, related to the growth of the transaction log:
Truncate log on checkpoint
(This option is used in SQL7.0. In SQL 2000, the fault recovery model is selected as the simple model)
When the CHECKPOINT command is executed, the contents of the transaction log file are cleared if it exceeds 70% of its size. Always set this option to True when developing the database.
Auto shrink
Regularly check the database. When the unused space of the database file or log file exceeds 25% of its size, the system will automatically reduce the file to make the unused space equal to 25%. When the file size does not exceed the initial size when it was created, it will not The reduced file must also be larger than or equal to its original size. Reducing the transaction log file can only be done when backing it up or when the Truncate log on checkpoint option is set to True.
Note: Generally, the default properties of the database created by Li Cheng have been set, but in case of unexpected circumstances, the database properties are changed. Please clear the log and then check the above properties of the database to prevent the transaction log from filling up again.