合 MSSQL中的几种Log Reuse Waits介绍

Eight Reasons why your Transaction Log Files keep growing

Introduction

A while back I wrote a short article about the Transaction Log Reuse Wait. Because it turned out to be very popular, I decided to follow up on that article and give a little more detail about the reasons why your database log files keep taking up more and more space.

SQL Server itself actually tells us what is going on with the log files in the log_reuse_wait_desc column of the sys.databases catalog view. So, if you have a particular database that has a log growth problem, you can just run this query to find out more:

[sql] SELECT D.name,
D.log_reuse_wait_desc
FROM sys.databases AS D;
[/sql]

However, this query just returns a keyword. To understand the cause of the different values of the log_reuse_wait_desc, we need to dig a little deeper.

Log Records

SQL Server uses the transaction log to guarantee the ACID properties, particularly the durability requirement. For every transaction, enough information to redo as well as undo that transaction it is written to the log file. Before any transaction can commit, SQL Server waits to get a confirmation from the hard drive that the log record was written successfully.

The transaction log gives SQL Server enough information to redo the operations that made up the transaction, should the actual change not make it into the data files, for example because of a crash. Because SQL Server has this information secured in the log file, it does not need to wait for the changes themselves being written to the data files and can commit the transaction while the data page changes still reside only in memory.

Every once in a while SQL Server will execute a checkpoint operation. During this operation data pages that were altered by previous or current transactions are written back to disk.

As a side note, it is possible for data pages containing changes of open transaction to be written to disk during a checkpoint. However, SQL Server has enough information in the transaction log to undo those changes, should the need arise. To have this flexibility SQL Server stores both the redo and the undo information in the log.

Log Reuse

Once every change to any data page that was executed by a single transaction was successfully saved to disk, the log record for that transaction is not needed anymore and its space in the log file can be reused.

To accommodate for reuse, SQL Server organizes the log files as a ring buffer of several containers called virtual log files. There is no direct way to influence their size and or number. SQL Server manages that automatically.

The virtual log files keep track of all their transaction log records and note if they are still needed, for example in an open transaction. Once all log records within a virtual log file are not used anymore, the virtual log file itself is marked as ready for reuse, and SQL Server will overwrite it with new log records once it gets around to that place in the ring buffer. The process of marking one or more virtual log files as reusable is called log truncation.

Log Reuse Wait

The above section described the general behavior of log reuse. There can however be several reasons for a log record to still be required by SQL Server for (potential) future operations. With that its virtual log file cannot be reused. If that happens for an extended period of time, SQL Server might run out of virtual log files and has to add additional ones. For that the physical file has to grow. If autogrowth is enabled for the log file and there is enough room on the drive this will happen automatically. If automatic growth is not possible, the database becomes effectively read-only, causing all write attempts to fail until the situation has been resolved.

The query shown at the beginning of this article allows us to find the most prevalent reason why virtual log files can't currently be reused. As of SQL Server 2012 it can return 10 different values:

• NOTHING
• CHECKPOINT
• LOG_BACKUP
• ACTIVE_BACKUP_OR_RESTORE
• ACTIVE_TRANSACTION
• DATABASE_MIRRORING
• REPLICATION
• DATABASE_SNAPSHOT_CREATION
• LOG_SCAN
• OTHER_TRANSIENT

The first one, NOTHING, means that there are still free virtual log files available. The last one, OTHER_TRANSIENT, is currently not used. That leaves eight real reasons why your log file might be growing.

Summary

SQL Server transaction log files are organized as a ring buffer of log record containers called virtual log files. These virtual log files are reused as the file pointer loops around the ring buffer. However, there are eight reasons that can prevent the reuse of these virtual log files. If reuse is not possible for one of those reasons, the log file has to grow.

Log Reuse Wait Series

Over the next few days I am going to write in more detail about each of the eight log reuse wait reasons.
Below is a list of links to the posts that are already available.

Log Reuse Waits Explained: REPLICATION

Introduction

There are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: REPLICATION

Replication

Transactional replication works similar to mirroring in high-performance mode. Changes applied and committed in one database can be replicated (hence the name) in another database. The main difference to mirroring is that transactional replication is a lot more flexible. Mirroring always copies the entire database and allows only for one secondary. In transactional replication on the other hand, you can have many subscribers (receivers of changes) and you do not have to send the entire database. You can for example send only a subset of the tables or even a subset of the columns of one table.

Under the covers transactional replication uses the same base technology as mirroring. The Log Reader Agent scans the transaction log of the publication database (the source database) and sends all committed changes that affected published objects over to the subscribers.

The data is actually not directly sent to the subscribers but instead to a distributor. The distributor then distributes the changes to the subscribers. Under-sizing the distributor often is a cause for bottlenecks.

There are two other forms of replication: Snapshot replication and merge replication. Both do not use the transaction log, so they are not relevant for this discussion. For more details on the inner workings of replication and the different replication types see my book Fundamentals of SQL Server 2012 Replication.

Waiting for the Log Reader Agent

The Log Reader Agent is responsible for scanning the transaction log and sending the data on. If that agent cannot keep up with the load, log truncation cannot occur. In this case SQL Server will return a log_reuse_wait_desc value of REPLICATION .

To resolve the REPLICATION wait type, make sure the network connection to the distributor is working and the distributor can handle the current work load. If the publisher and the distributor are on the same SQL Server instance or on the same physical machine, consider moving the distributor to dedicated hardware.

Change Data Capture

Change Data Capture or CDC is a technology designed to record all changes that were applied to a monitored table or set of tables. This information can for example be used in an audit report providing the before and after values for every change as well as who executed the change and when it was applied.

This very powerful technology makes use of the replication technology. That means if CDC gets behind for some reason, e.g. because of a degrading raid array causing slow performance of the drive it is writing to, and if this is preventing log truncation a log_reuse_wait_desc value of REPLICATION is reported. So if you see this value and do not use replication or can't find anything wrong with it, check your CDC setup.

Summary

Transactional replication uses a log reader agent to read and process all committed transactions in the publication database. Virtual log files containing log records that have not been processed yet cannot be reused. If the log reader agent falls behind, SQL Server will eventually run out of virtual log files. In this case it will return a log_reuse_wait_desc value of REPLICATION. Problems with change data capture can also lead to this wait type as CDC is using the replication technology stack.

Log Reuse Wait Series

This post is part of a short series about circumstances that prevent transaction log truncation.
Below is a list of links to the posts that are already available.

Log Reuse Waits Explained: LOG_SCAN

Introduction

There are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: LOG_SCAN

LOG_SCAN

This one is shy. You will rarely see one on your servers.

A log_reuse_wait_desc value of LOG_SCAN indicates exactly what it sounds like. For some reason SQL Server is executing a scan of the transaction log. While that scan is going on, log truncation cannot happen, potentially leading to this wait type.

So what will cause a log scan? There are a few situations in which SQL Server has to scan the transaction log. The use of the undocumented fn_dblog function will for example cause a log scan. Another example is a checkpoint operation: During a checkpoint SQL Server does a log scan to synchronize log sequence numbers.

A log scan is usually a very brief operation. Therefore this log reuse wait is very transient. If you encounter this wait on you system it will probably be gone by the next time you look.

Summary

SQL Server will return a log_reuse_wait_desc value of LOG_SCAN if it runs out of virtual log files during an active scan of the transaction log. Those scans are usually short and therefore you should not see this wait type for an extended period of time, if ever.

Log Reuse Wait Series

This post is part of a short series about circumstances that prevent transaction log truncation.
Below is a list of links to the posts that are already available.

Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION

Introduction

There are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: DATABASE_SNAPSHOT_CREATION

Database Snapshot

A database snapshot is a point in time image of a database. It is implemented as a shadow file. That means that only the pages that have changed since that snapshot was created are actually stored in the snapshot file; the original unchanged version of those pages that is. All the other pages are read directly from the database files. From the creation on, every time a page in the database changes the original version is first moved over to the snapshot. That way SQL Server can provide that point in time image of the database.

The creation of the snapshot is a not very resource intensive and usually takes only a few seconds. However, during this time SQL Server needs to keep an eye on concurrent changes. Not surprising, it uses the transaction log for that.

Waiting for the Snapshot

Because SQL Server must ensure that the snapshot in itself is transactionally consistent, it uses the database transaction log at the end of the snapshot creation process to apply changes that were committed since and undo changes that happened during the snapshot creation but where not committed at the time the snapshot creation finished. (All those actions are only applied to the snapshot file; the transactions in the actual database are not affected.)

During the creation process, SQL Server can therefore not truncate the log. If it runs out of log space because of that it will return a log_reuse_wait_desc value of DATABASE_SNAPSHOT_CREATION.

The process of creating a snapshot is usually very quick and you should not see this wait for a long time. If you are not using snapshots yourself however you should be aware that SQL Server is making use of this technology for internal purposes. A prominent example is the DBCC CHECKDB command. It will create a database snapshot to run its checks against. So you might see the above mentioned wait when executing your database consistency checks. But again, the actual wait time should always be fairly short.

Summary

SQL Server will return a log_reuse_wait_desc value of DATABASE_SNAPSHOT_CREATION if it runs out of virtual log files during the creation of a database snapshot. As that is usually a quick process you should not see this wait for a long time. Be aware, that SQL Server is using this feature internally, so you might see this log reuse wait type even if you are not using database snapshots yourself.

Log Reuse Wait Series

This post is part of a short series about circumstances that prevent transaction log truncation.
Below is a list of links to the posts that are already available.

Log Reuse Waits Explained: DATABASE_MIRRORING

Introduction

There are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: DATABASE_MIRRORING

Database Mirroring

Database mirroring is deprecated since SQL Server 2012. So this wait type will go away at some point. But for now there are still quite a few installations out there. Therefore I decided to cover it anyway.

Database mirroring allows you to have a second exact copy of your database on a different server. There are two operating modes: high performance and high-safety. In high-safety mode a transaction can only be committed if it has been applied to both databases. This can cause higher transaction latency but you will not lose any data if one of the two servers suddenly dies.

In high-performance mode on the other hand, transactions are committed on the primary server first and then transferred over to the secondary server. That reduces the latency impact on the primary server, but you might run the risk of some data loss if the secondary is behind at the time the primary server goes down.

Waiting for the Secondary

If you set database mirroring up in high-performance mode, a background process reads records for committed transaction from the transaction log and then sends enough information over to the secondary to repeat the action that transaction executed.

If the secondary falls behind, for example because of an interrupted network connection, the log reading process will wait until it can continue to transmit transaction information. During that time, transactions in the log that have not been processed by the mirroring agent cannot be purged and the virtual log files containing these records can't be reused. SQL Server will return a log_reuse_wait_desc value of DATABASE MIRRORING if it runs out of virtual log files because of this.

To solve this issue make sure the server housing the database mirror is adequately sized to keep up with the transaction load and the network connection between the two is stable. Also make sure there is enough room for the transaction log of the source database to grow in case of a mirror delay.

Summary

SQL Server database mirroring is a deprecated high availability solution. It allows us to have copy of a database on a secondary server that is automatically kept in synch, either synchronously or asynchronously. In high-performance mode, the transaction log information of committed transactions is used to apply the changes asynchronously to the secondary. A log_reuse_wait_desc value of DATABASE MIRRORING indicates that this process is falling behind and can't process log records quickly enough.

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。