Shulou(Shulou.com)05/31 Report--

This article shows you how to deal with log files in SQL Server, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Summary of SQLServer log files and how to deal with flooding

Transaction log

Transaction log (Transactionlogs) is an extremely important but often neglected part of database construction. Because it is not as lively as the schema in the database, few people care about the transaction log.

The transaction log is a registration for database changes, which can register any monopoly against the database and keep the registration harvest in a self-reliant file. For every transaction process, the transaction log has extremely comprehensive registrations, according to which the data files can be restored to pre-transaction events. From the beginning of the transaction action, the transaction log is in the registration state, and any monopoly on the database in the transaction process is within the registration scope until the user clicks submit or retreats to finish the registration. Each database has at least one transaction log and one data file.

For functional reasons, SQLServer caches the user's changes, which are immediately written to the transaction log, but not to the data file. The transaction log traverses a symbol point to determine whether a transaction has written the data in the cache to the data file. When SQLServer restarts, it looks at the latest symbol point in the log and erases the transaction registrations after that symbol point, because these transaction registrations do not sincerely write the cached data to the data file. This can deter those intermittent trading correction data files.

Summary of SQLServer log files and how to deal with flooding

Maintain transaction log

Because many people often omit the transaction log, it will also bring some problems to the system. As the system runs from time to time, the contents of log registrations will become more and more, and the volume of log files will become larger and larger, resulting in insufficient available disk space. Unless the log is cleaned frequently during normal work, the log file will eventually occupy the full available space in the partition. The default configuration of the log is unlimited capacity, and if it works with this collocation, it will expand from time to time and will eventually be separated from the full available space. Both cases can cause the database to work statically.

The daily backup of the transaction log ji.jieliya3518.comwww.new3go.info can be used to curb the excessive consumption of disk space of log files. The backup process truncates parts of the log that are no longer necessary. The measure of interception is to first take the old registration symbol as an inactive state, and then hide the new log to the location of the old log, so as to curb the expansion of the transaction log from time to time. In case the log cannot be backed up regularly, it is good to set the database to "easy to restore form". In this form, the system will force the transaction log to actively intercept the monopoly every time the symbol point is registered, and cover the old log with a new log.

The truncation process occurs when the old symbol is backed up or marked as an inactive state, which allows the old transaction registration to be obscured, but this does not shrink the disk space actually borrowed from the transaction log. Even if you no longer use the log, it will still cut off the fixed space. Therefore, during maintenance, it is also necessary to shrink the transaction log. The measure to shrink the transaction log is to eliminate the non-action registration, thereby shrinking the physical hard disk space borrowed by the log file.

Traversing can shrink the transaction log file of the current database by using the DBCCSHRINKDATABASE statement, and the DBCCSHRINKFILE statement is used to shrink the specified transaction log file. In addition, it can also activate the active contraction monopoly in the database. When shrinking the log, the old registration symbol will be first regarded as a non-action state, and then the registration with non-action symbol will be completely removed. Depending on the contraction measures used, you may not see the harvest immediately. In voluntary situations, shrinking work should be held during periods when the system is not extremely busy, otherwise database functions may be affected.

Such as database

Transaction registration backup can be used to restore the database to a specified situation, but the transaction registration backup itself is not enough to finish the task of replicating the database, and the data files that must be backed up must be added to the restore work. When it comes to a database, the best thing to do is to restore the data files. Do not set the full data file restore as a final state of affairs until the full data file restore is completed, otherwise the transaction log will not be restored. When the data file restore is finished, the system will go through the backup of the transaction log to restore the database to the state of affairs desired by the user. In case there is a backup of multiple log files after the last sequential backup of the database, the backup process will restore them in sequence according to the time that occurred.

Another process, called logshipping, provides stronger database backup power. When the logshipping is matched, it can fully copy the database to another server. In this case, the transaction log is also regularly sent to the backup server for the use of repeat data. This keeps the server in a hot backup state, and it updates when the data is changed. Another server, called a monitor server, can be used to monitor shipping signals sent at legal time intervals. In case a signal is not received within the legal time, the monitoring server registers the event in the event log. This mechanism makes logshipping often used in catastrophe recovery programs. When we write code, we will reveal some small myths.

The above is how to deal with log files in SQL Server. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.