Recovery Interval in SQL Server

In fact, a lot of friends have asked recovery Interval, have asked this is why, have asked how to adjust this value, so write a small blog today, once and for all.

As we all know, SQL Server relies on log to ensure performance and data persistence without delay. So let's look at how SQL Server handles our data modification requests.

First, our client submits the data modification instructions to the SQL Server,sql Server and reads the data from the physical disk into memory through a series of procedures.

After the data is read into memory, SQL Server modifies the data in memory. Of course, everyone will think that the modified is not immediately to write back to the disk it? If you write back, then it will affect performance, if not, then if the system crashes the changes will be lost, as we are in Word writing document.

SQL Server does not use both of these stupid methods, and SQL Server knows that most of the data will be accessed again for some time after it is accessed, so SQL Server decides to keep the data in memory. But what if the system crashes at this time, or does the system lose power? SQL Server uses a workaround that writes the modification to a different file called a log.

So SQL Server is like this, every change in one data, SQL Server will write this data to the log file. If the system crashes, SQL Server can tell from this log file what we have done, we just have to do it again to recover the data.

It's like we didn't talk about recovery interval.

Yes, it's coming. Although the above method is good, but those modified data can not be kept in memory. In addition to the limited memory, there is an important reason that SQL Server cannot rely solely on logs to ensure data consistency.

SQL Server usually runs for months without restarting, which is not like the notebooks we use. So if it's been running for three months and suddenly crashes, oh, God! Do we have to redo the three-month operation again? Of course not, Recovery interval will control it all!

Recovery interval tells SQL Server that it can't take more than a few minutes to recover from the operation in the log. SQL Server would look at all this, and when it found that the new operations in the log needed to be recovered more than recovery interval time, SQL Server was like forcing the memory of those modified data back into the disk.

So we don't have to rerun the three-month operation, which is usually a mere 5 minutes, because the default recovery interval is 5 minutes.

So how do we adjust this value?

The longer the value of the Recovery interval, it also means that if our system crashes, we need to recover more time, but we think that the modified data can stay in memory for longer, which means we can write a little bit less on the disk.

Conversely, if the value of recovery interval is about short, it means that if our system crashes, we need to recover less time and, on the other hand, we can write more frequently on the disk.