Shulou(Shulou.com)06/01 Report--

How to solve the problem of SQL SERVER Always on production failure, many novices are not very clear, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Recently, I have been extremely busy with all kinds of optimizations, database systems, and all kinds of problems. I wish I had eight hands to do these jobs, but the house happened to be raining all night, and SQL SERVER ALWAYS ON had another problem. SQL SERVER currently uses a cluster of WINDOWS 2016 ENTERPRISE + SQL SERVER 2016 ENTERPRISE ALWAYS ON to support part of the company's business.

SQL SERVER ALWAYS ON as a mature SQL SERVER cluster solution has been applied in many enterprises, but any system is not perfect, and there are faults. In the past two days, my colleagues in operation and maintenance said to me that the log of SQL SERVER ALWAYS ON is not intercepted, it is growing crazily. In fact, I was not nervous when I heard the news, because the same problem was found in SQL SERVER 2012 used by a company I used to work for. In the end, although the root cause was not found, the problem was solved.

OK, let's first look at SQL SERVER's log. SQL SERVER's log file is LDF, which to the layman is a file, but in fact, to DBA, it is a recyclable capped collection (this concept is a concept of MONGODB, which is not exactly the same, but this is what it means).

The following is a LDF file structure, each file has multiple VLF blocks, and these blocks can be reused, that is, when CHECK POINT will dirty data FLUSH to the data file, in fact, these LOG on some levels do not have much meaning to the database, can be dropped by DUMP.

But why sometimes the log cannot be truncated and the log cannot be reuse

If there is no CHECK POINT log in the 1 VLF block, that is, the activity log, such as a large transaction, the VLF file block will not be overwritten until the data is brushed into the data file before it can be CHECK POINT, and the VLF can be reused.

2 the tail of a VLF must be FREE VLF. If you happen to have four VLF and TAIL and HEAD happen to be in a VLF, such a log cannot be shrunk unless the data is written to VLF1. For example, if there is a gecko, if you let him free his space, do you think he is willing to cut down from the head or from the tail? So the following situation is not to shrink the log.

In addition, we also need to understand whether it is necessary to shrink the log, in my opinion, it is okay, because if the log rises to 800G (not because of errors, or a variety of bad DML), then it can not be released, because it will REUSE space, after you SHRINK him, sooner or later you still have to take up space, and there is also a loss of exchange space with the system, what are you doing?

If you want to see what the status of your log file is, just type dbcc loginfo to know. A file with a status of 2 is active and cannot be SHRINK.

Let's go back to why the log cannot be intercepted. In fact, this statement is not accurate. It should be why the log has been activated all the time and cannot be released. In our failure, it is obvious that the log of ALWAYS ON has not been applied to the slave database. The problems caused.

The query database has always been in the state of AVALIABILITY_REPLCA, which is usually caused by problems with the slave database, such as downtime from the slave database, query from the slave database (usually large queries from the database, OLAP requirements), resulting in the log can not be applied, or some strange problems.

Here's the experience we need to restart from the library, and I found in the error log from the library

The following error log:

Error: 19432, Severity: 16, State: 0. Always On Availability Groups transport has detected a missing log block for availability database "database_name". LSN of last applied log block is (xxxx:xxxxxxx:x). Log scan will be restarted to fix the issue. This is an informational message only. No user action is required.

According to Microsoft's official FIX (Microsoft's official explanation and resolution of errors)

We need to patch SQL SERVER 2016 SP1 SP2 to solve the problem.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.