SQL Server log file summary and full processing

The transaction log (Transaction logs) is a very important but often overlooked part of the database structure. Because it is not as active as the schema in the database, few people are concerned about the transaction log.

The transaction log is a record of changes to a database that can record any action against the database and save the record results in a separate file. For any transaction process, the transaction log has a very comprehensive record that can be used to restore data files to the state before the transaction. The transaction log is in the record State from the trading action, and any operations on the database during the transaction are recorded, until the user clicks on the submit or backward to complete the record. Each database has at least one transaction log and one data file.

For performance reasons, SQL Server stores user changes in the cache, and these changes are immediately written to the transaction log, but are not immediately written to the data file. The transaction log will pass a marker point to determine whether a trade has written data from the cache to the data file. When SQL Server restarts, it looks at the most recent tag points in the log and erases the transactions that follow the mark Point, because these transactions do not actually write data from the cache to the data file. This prevents the interrupted transaction from modifying the data file.

Maintain the transaction log

Because many people often forget the transaction log, it also brings some problems to the system. As the system continues to run, the content of the log records will be more and more, the volume of log files will become larger, resulting in insufficient free disk space. Unless the log is regularly cleaned up in daily work, the log file will eventually encroach on all available space within the partition. The default configuration for logging is unlimited, and if you work with this configuration, it expands and eventually occupies all of the available space. Both of these situations will cause the database to stop working.

The day-to-day backup of the transaction log can effectively prevent it from consuming excessive disk space. The backup process truncates the part of the log that is no longer needed. The method of truncation is to mark the old record as inactive first, and then overwrite the new log to the location of the stale logs, thus preventing the volume of the transaction log from expanding. If you cannot perform regular backups of the log, it is a good idea to set the database to the simple recovery model. In this mode, the system forces the transaction log to automatically truncate every time a mark point is logged, overwriting the old log with a new journal.

The truncation process occurs when the backup or the old Mark Point is not active, which makes the old transaction available for overwriting, but does not reduce the amount of disk space actually occupied by the transaction log. Even if you no longer use the log, it will still occupy a certain space. As a result, the transaction log is also compressed for maintenance. The method of compressing the transaction log is to delete inactive records, thereby reducing the physical hard disk space occupied by the log files.

You can compress the transaction log file for the current database by using the DBCC SHRINKDATABASE statement, which is used to compress the specified transaction log file, or to activate the automatic compression operation in the database. When you compress a log, you first mark the old record as inactive, and then delete the record with the inactive tag completely. Depending on the compression method you use, you may not see the results immediately. Ideally, the compression work should be done in a time when the system is not very busy, or it may affect database performance.

Restore Database

Transaction backups can be used to restore a database to a specified state, but the transaction backup itself is not sufficient to complete the task of restoring the database, and the backed-up data files are required to participate in the recovery effort. When you restore a database, the first thing you do is restore your data files. Do not set the entire data file to completion until it is restored, otherwise the transaction log will not be restored. When the data file is restored, the database is restored to the user's desired state through a backup of the transaction log. If multiple log files are backed up after the last backup of the database, the backup program restores them in turn according to the time they were established.

Another process known as log shipping can provide a stronger database backup capability. When log shipping is configured, it can replicate the entire database to another server. In this case, the transaction log is also sent to the backup server on a regular basis to restore data usage. This keeps the server in a hot backup state, and it is updated as the data changes. Another server, called a monitor server, can be used to monitor shipping signals sent at specified intervals. If no signal is received within the specified time, the monitor server logs the event to the event log. This mechanism makes log shipping often a scenario used in a disaster recovery plan.

Performance optimization

The transaction log plays an important role in the database, and it also has a certain impact on the overall performance of the system. With several options, we can optimize the transaction log's performance. Because the transaction log is a continuous disk write process, no read action occurs. Therefore, putting the log file on a separate disk has a certain effect on optimizing performance.

Another optimization measure relates to the volume of the log file. We can set the size of the log file to be no more than a few percent of the hard disk space, or determine it. If you set it to a convention that wastes disk space, and if you set it too small, it forces the record file to try to expand continuously, resulting in degraded database performance.

Transaction log files transaction log file is a file that is used to record database updates, with the extension LDF.

In SQL Server 7.0 and SQL Server 2000, if automatic growth is set up, the transaction log files are automatically expanded.
In general, the size of the transaction log is stable when the maximum number of transactions that occur between two transaction log truncation can be accommodated, and transaction log truncation is triggered by checkpoints or transaction log backups.

However, in some cases, the transaction log can become so large that it runs out of space or becomes full. Typically, you receive the following error message when the transaction log files occupy free disk space and cannot be extended:

error:9002, Severity:17, State:2
The log file for database '%.*ls ' are full.

In addition to this error message, SQL Server may mark the database as suspect because of the lack of transaction log expansion space. For additional information about how to recover from this scenario, see the "Low Disk Space" topic in SQL Server online Help.

In addition, transaction log extensions can cause the following situations:

· A very large transaction log file.
· The transaction may fail and may start rolling back.
· Transactions may take a long time to complete.
· A performance problem may occur.
· A blocking behavior may occur.

Reason

The transaction log extension may occur because of the following reasons or circumstances:

· UNCOMMITTED transactions
· Very big business.
· Actions: DBCC Dbreindex and CREATE INDEX
· When restoring from a transaction log backup
· Client application does not process all results
· The query timed out before the transaction log completes the extension, and you receive a false "Log full" error message
· Transactions that are not replicated

Solving method

When the log file is full and the SQL database cannot write to the file, there are two methods available:
　　
One way: Empty the log.

1. Open Query Analyzer, enter command
DUMP TRANSACTION database name with NO_LOG

2. Then open Enterprise Manager-right you want to compress the database-all tasks-shrink the database-shrink the file-Select the log file-in the shrink option to shrink to XXM, this gives you a minimum m number that you want to shrink to, just enter the number and make sure.

Another approach has a certain risk, because SQL Server log files are not immediately written to the database master file, such as improper handling, can result in loss of data.

1: Delete Log
Detach Database Enterprise Manager-> Server-> Database-> Right key-> Detach database

2: Delete log file
Additional database Enterprise Manager-> Server-> Database-> Right key-> attached database

This method generates a new log with a size of more than 500 K.

Note: The first method is recommended.

If you don't want it to get bigger later.

Use under SQL2000:
Right-click on the Database-> properties-> option-> failure recovery-model-select-Simple model.

or with SQL statements:
ALTER DATABASE name set recovery simple

In addition, database properties have two options that are related to the growth of the transaction log:

Truncate Log On Checkpoint
(this option is used in Sql7.0,sql 2000, which is the recovery model selected as the simple model)

Clears the contents of the transaction log file if it exceeds 70% of its size when executing the checkpoint command This option is often set to true when developing the database

Auto Shrink
Periodically check the database when the unused space of the database file or log file exceeds 25% of its size, the system will automatically shrink the file so that its unused space equals 25% When the file size does not exceed its initial size when it is established, the file reduction file must also be greater than or equal to its initial size the reduction of the transaction log file can only be done if it is backed up or when the Truncate log on Checkpoint option is set to true.

Note: Generally established database default properties have been set, but encountered unexpected circumstances so that the database properties have been changed, please empty the log, check the database above properties, in case the transaction log again full.