SQL Server Log File Handling Method

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 backup, the backup program will restore them according to their creation time.

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.

Performance Optimization
Transaction logs play an important role in the database and also affect the overall performance of the system. With several options, we can optimize the transaction log performance. Transaction logs are a continuous disk write process, so no read operation will occur. Therefore, storing log files on an independent disk can optimize performance.

Another optimization is related to the volume of log files. We can set the size of the log file to no more than a few percent of the hard disk space, or determine its size. If you waste disk space by setting it too much, and if it is set too small, it will force the record file to continue to try to expand, resulting in a decline in database performance.

Transaction log file is a file used to record database updates with the extension LDF.
In SQL Server 7.0 and SQL Server 2000, if the automatic growth function is set, the transaction log file is automatically expanded.
Generally, when the maximum number of transactions between two transaction log truncation occurs, the transaction log size is stable. Transaction Log truncation is triggered by the checkpoint or transaction log backup.
However, in some cases, the transaction log may become very large, resulting in exhausted space or full. Generally, when the transaction log file occupies sufficient disk space and cannot be expanded, you will receive the following error message:
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 the lack of transaction log extended space. For more information about how to recover from this situation, see the "insufficient disk space" topic in SQL Server online help.

In addition, transaction log extensions may cause the following situations:
· Very large transaction log files.
· Transactions may fail and may start to roll back.
· Transactions may take a long time to complete.
· Performance problems may occur.
· Blocking may occur.

Cause
Transaction Log extensions may occur for the following reasons or situations:
· Uncommitted transactions
· Very large transactions
· Operation: DBCC dbreindex and create Index
· During restoration from transaction log backup
· Client applications do not process all results
· Query times out before the transaction log is extended. you receive a false "log full" error message.
· Unreplicated transactions

Solution
When the log file is full and the SQL database cannot write the file, you can use either of the following methods:
One method: Clear logs.
1. Open the query analyzer and enter the command
Dump transaction database name with no_log
2. open the Enterprise Manager and right-click the database you want to compress -- all tasks -- shrink database -- shrink file -- Select log file -- select to shrink to xxm In the shrink mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

Another method is risky because the log files of SQL Server are not immediately written to the master database file. improper processing may cause data loss.
1: Delete log
Detach Database Enterprise Manager-> server-> database-> right-click-> detach Database
2: delete log files
Attach Database Enterprise Manager-> server-> database-> right-click-> attach Database
This method generates a new log with a size of more than 500 K.

Note: The first method is recommended.

If you do not want it to become larger later.
Use in SQL2000:
Right-click the database and choose Properties> Options> fault recovery> model> select simple model.
Or use an SQL statement:
Alter database name set recovery simple

 
In addition, for example, the database attribute has two options, which are related to the growth of transaction logs:
Truncate log on checkpoint
(This option is used for sql7.0. in SQL 2000, the fault recovery model is selected as a simple model)
When the checkpoint command is executed, if the transaction log file exceeds 70% of its size, the content will be cleared. This option is often set to true in the development database.
Auto shrink
Regularly checks the database. When the unused space of database files or log files exceeds 25% of its size, the system will automatically reduce the file so that the unused space is equal to 25%. When the file size does not exceed the initial size when it is created, the file size after the file is not reduced must be greater than or equal to its initial size for the transaction the log file can be reduced only when it is backed up or the truncate log on checkpoint option is set to true.

Note: Generally, the default attribute of the database established is set, but the database attribute is changed in case of exceptions. Clear the log and check the preceding attributes of the database to prevent transaction logs from being full again.