transaction log for SQL Server

1 Basic Introduction

Each database has a transaction log that records all things and what each thing does to the database.

The logging form needs to be determined based on the recovery model of the database, with three database recovery models:

• Full mode, the full record of the thing log, the need for regular log backups.
• The bulk-logged mode, which is suitable for batch operations of databases, can process logs in a more compressed manner and requires regular log backups.
• Simple mode, there are log files, but the mode can be automatically reused through the checkpoint virtual log file, so the log files will be in the process of repeated use, maintain a certain size, but, if there is a transaction started, long time no commit, Then the transaction log storage space from the start of this transaction to the last commit is not checpoint automatically reused, and you are likely to see a large log file; Note that a log backup is not possible in simple mode .

in the database, any read or write to the database is found in the memory page of the corresponding data, and then modify, if the memory page does not exist in the data page, then loaded from disk such as memory. When a modification occurs, the corresponding data page in the memory page will be modified, and it will be recorded in real time to the future file LDF. So, when will the data be synced to the MDF file, with only the following three scenarios:

• When doing checkpoint, follow-up will specialize in checkpoint the corresponding articles;
• Lazy write runtime, that is, memory pressure, you need to write in-memory data pages to disk, freeing up memory space;
• Eager write occurs when the bulk insert and the select INTO operation occur.

Transaction logging in DB can give us a lot of benefits, and it can support the following operations:

• Recovery of individual transactions.
• Resumes all outstanding transactions at SQL Server startup.
• Rolls the restored database, file, filegroup, or page forward to the point of failure.
• transactional replication is supported.
• Support for high availability and disaster recovery solutions: AlwaysOn availability Groups, database mirroring, and log shipping.

2 impact on Database startupWhen the database restarts or restores to the last time, the database will enter the recovery state, normally, this state lasts a few 10 seconds, but in special cases, it will take a very long time, even a few hours, if this step fails, the database enters into the suspended suspect state, Not available for normal use. so, when the database enters recovery, what is it doing? The SQL Server log records all modification records (modification of data, without SQL statements), including the Begin Transaction and Commit/rollback Transaction operations. Because of the modification of the transaction log, than the data file modification faster, all will appear, the data modification to the log file, but has not yet landed to the data file, then this time the database is in the recovery state, At the same time, all data modification records after the most recent checkpoint point of the transaction log are checked: After all the checks are completed, a checkpoint representation of the database is made and written to the transaction log indicating that the log file has been synchronized with the data file. The recovery process is completed and the database is available for normal use. One thing to note here is that if you have more than one recent checkpoint-to-current modification of your database, it will take a relatively long time to check, and you can see the percent-marked recovery in error log to make progress and avoid aimless waiting. Error log Check, you can view the current log through the graphical interface (see), you can also run the Xp_readerrorlog query.

1 /*2 Xp_readerrorlog3 1. Archive number4 2. Log type (1 is SQL Server log, 2 is SQL Agent log)5 3. Query the included string6 4. Query the included string7 5. Logdate Start Time8 6. Logdate End Time9 7. Sorting results, sorted by logdate (can be descending "Desc" Or Ascending "ASC"), default ascendingTen */ One   A ExecXp_readerrorlog0,1,Null,Null,'2017-02-16 10:53:32.300','2017-02-16 12:53:32.300'

Assuming this happens, because the importance of on-line is far more important than the data loss situation, and you communicate with all departments to confirm can afford to lose the difference between data file and log file, then you can follow the steps below, It is not recommended to do soBecause of the unpredictable data loss situation, If you're on the line, you're going to give up this part of the data, you can follow these steps:

1 #设置数据库单用户2 Alter DatabaseBackupDBSetSingle_user with rollbackImmediate3 4 #设置数据库紧急状态5 Alter DatabaseBackupDBSetEmergency with rollbackImmediate6 7 #获取事务日志的物理名和逻辑名后, rebuild the log file8 SelectName,physical_name fromSys.master_fielswheredatabase_id=db_id('backupdb')9 Alter DatabaseBACKUPDB rebuildLog  on(Name='logical name of the transaction log', filename='the physical noun of the transaction log')Ten  One #设置数据库online A Alter DatabaseBackupDBSetOnline with rollbackImmediate -  - #设置数据库为多用户 the Alter DatabaseBackupDBSetMulti_user with rollbackImmediate

3rd How to add a log fileLogging in a file with a suffix called LDF, allowing for multiple log files, but not logging separately, but instead using filling up a log file, then turning to a log file, linear operation log file. You can add an LDF file below, and you need to be aware of several places:

• Initial Size , it is recommended to set directly to truncate the maximum value of the log during the period, for example, a DB recovery mode is full mode, every half an hour to do a transaction log backup and truncation log, then set the log file size, take the business peak every half-hour log growth maximum value is 5G, The initial size can be set between 5g-7g;
• growth size , whether proportional or MB growth, do not set too small, it is recommended to grow at around 100Mb each time, reduce the use to auto-growth, initially set the initial size to meet their growth needs, if the initial size is set to start small, not satisfied, Can pick a business low peak period, modify the size of the initial large. Each time the file automatically grows, the log will be blocked, although the time is very short, but if the growth is frequent, it will affect the database operations;
• automatic growth , it is recommended to automatically grow, but the premise of regular monitoring of the growth of the log, to avoid the lack of disk space, and if the recovery mode is full mode or large capacity mode, you also need to do regular log backup truncation log, to avoid the transaction log full 9002 error;
• path Selection , it is recommended that the MDF file on a different disk, decentralized IO, if the disk read-write bottleneck is not large, it can be placed on a disk;

There are 2 ways to add them, as follows:

1  Use [Master]2 GO3 ALTER DATABASE [backupdb]4 ADD LOG FILE (5NAME=N'backupdb_log_1',6FILENAME=N'D:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\backupdb_log_1.ldf' ,7SIZE=524288KB,8MAXSIZE=1048576KB,9FileGrowth=10240KBTen                       ) One GO

4 Physical Structure The transaction log of a database is mapped on one or more physical files, conceptually, the log file is a series of log records, and the log record sequence is physically stored in the physical file that implements the transaction log. The SQL Server database engine internally divides each physical log file into multiple virtual log files, namely, VLF (virtual log file), which has no fixed size and the number of virtual log files contained in the physical log file is not fixed. When a log file is created or extended by the database engine DynamicSelect the size of the virtual log file. The database engine tries to maintain a small number of virtual files.    After the log file is extended, the size of the virtual file is the sum of the existing log size and the new file increment size. Virtual log files can affect system performance only if the log files are defined with a smaller size and growth_increment value. If these log files grow to a large number of small increments, they will have many virtual log files. This reduces the speed of database startup and log backup and restore operations. We recommend that you assign a size value that is close to the final desired size for the log file, and also assign a relatively large growth_increment value. The administrator cannot configure or set the size or number of virtual log files, but in the case of VLF affecting system performance, You can try to zoom out by shrinking the log file by the way . DBCC LOGINFO (dbname) returns the number of rows, that is, the number of VLF files, when status is 0 o'clock, that is, the file is not used, but also write to the log,2 expression has been used, and can not be reused, this time, through the form of backup Log, Back up and truncate the log file, you can reclaim space from the last 0 to the most recent 2 rows. Shrinking the log file or reducing the VLF file as follows, you need to back up the log files before you can effectively shrink, in the absence of backup log files in the case of contraction, the effect is small. See the following code:

1 #其行数及为VLF个数, a status of 0 means that the file is not available, 2 indicates that it is already in use, and cannot be reused2 DBCCLoginfo3 4 #备份日志5 BACKUP LOG [backupdb]6  to  DISK =N'D:\data\20170215_backupdb_log.trn'  withNoformat, Noinit, NAME=N'backupdb-transaction Log Backups', SKIP, Norewind, Nounload, STATS= Ten7 GO8 9 #收缩日志文件, according to the log file name to shrink 500Mb, the recommended shrinkage size is reasonable size, reference to the initial size of the above judgementTen  Use [backupdb] One GO A SELECTName fromSys.database_filesWHEREType_desc='Log' - DBCCShrinkfile (N'Jiankong_db_log', -) - GO the  - #其行数及为VLF个数, VLF file reduction - DBCCLoginfo

The transaction log is a wrapped file. Suppose that the database backupdb has only one LDF file, and is just divided into 5 virtual logs, when we start to use the database, the logical log from the beginning of the physical log to the end of the record, such as.

When checkpoint occurs, the minimum recovery log sequence number MinLSN is annotated, and "MinLSN" is the log sequence number of the oldest log record required for a successful database-wide rollback. Such as.

All virtual log files before MinLSN can be truncated, and the database truncates the log itself after the following two events:

• In the simple recovery mode, occurs after the checkpoint.

• Under the full recovery model or bulk-logged recovery model, if a checkpoint has been generated since the last backup, it is truncated after the log backup (unless it is a copy-only log backup).

When the log is truncated, these VLF can be released for recycling, and the beginning of the logical log is moved to the end of the last phase of the VLF file.      Assuming that at this time, after a minlsn position, a transaction has been made and there has been no commit, causing Vlf3,vlf4,vlf5 to be used, the previously recovered VLF file will be reused.    Under normal circumstances, if the old log records are often truncated, keeping the end of the logical log from reaching the beginning of the logical log, and all new log records for the ship before the next checkpoint have enough space to store, then the log files will never be filled, maintaining a certain size that can be achieved by regularly backing up the transaction log. However, if the end of the logical log is met at the beginning, then when the disk space is sufficient, the log file is increased by the number specified in the auto-increase size, and multiple VLF files are added to the physical log file, and if there is less disk space than the specified increment size, an error is made, and 9002 errors occur. Causes the database to fail all write operations. 5 reason for delayed log truncationLog truncation can be delayed due to several factors. You can query the sys.databases catalog view log_reuse_waitAnd Log_reuse_wait_descThe column discovers what, if any, prevents truncation of the log. The values for these columns are described in the following table. 6 managing transaction LogsRegularly monitor log file size and actual usage size, in case the log grows abnormally and fills up disk space, you can view log file usage in the following two ways.

1 #查看日志使用情况, file size and actual usage size 2 DBCC sqlperf (logspace) 3 4 #查看文件相关信息 5 Select name,physical_name,size*8.0/1024x768 size_mb,*   from sys.database_files

Regular log backups, the interval of two backups is the time span to run the lost data, and do not backup too often, which can have a certain impact on database IO.

1 BACKUP LOG [backupdb]2  to  DISK =N'D:\data\20170215_backupdb_log.trn'  withNoformat, Noinit, NAME=N'backupdb-transaction Log Backups', SKIP, Norewind, Nounload, STATS= Ten3 GO

The transaction is as short as possible, avoid opening the transaction for a long time, or forget commit/roll back; Resolve transaction log full issue (9002 error)

• If you limit the file maximum, increase the size of the log file if there is any remaining disk space.
• Free up disk space so that the log can grow automatically.
• Add a log file on another disk.
• After you back up the log, shrink the log.
• Move the log files to a disk drive that has sufficient space.

Reference Document: https://msdn.microsoft.com/zh-cn/library/ms189275.aspxhttps://msdn.microsoft.com/zh-cn/library/ms190925 (v =sql.110). aspxhttps://msdn.microsoft.com/zh-cn/library/ms190422 (v=sql.110). aspxhttps:// blogs.msdn.microsoft.com/apgcdsd/2011/12/29/943/https://msdn.microsoft.com/zh-cn/library/ms365418 (v=sql.110). aspxhttps://msdn.microsoft.com/zh-cn/library/ms175495 (v=sql.110). aspx

Transaction log for SQL Server