Recovery interval in SQL Server

In fact, many of my friends have asked about the recovery interval. I have asked you how to adjust this value. So I wrote a small blog today to make it happen once and for all.

As we all know, SQL Server relies on log to ensure no delay in performance and data persistence. Let's take a look at how SQL server handles our data modification requests.

First, our client submits the data modification command to SQL Server, which reads the data from the physical disk to the memory through a series of processes.

After the data is read to the memory, SQL Server modifies the data in the memory. Of course, you will think, do you want to write the changes back to the disk immediately? If it is written back, it will inevitably affect the performance. If it is not written, it will be lost if the system crashes and the modifications will be lost, just as we are writing documents in word.

SQL Server does not use these two stupid methods. SQL Server knows that in most cases, most data will be accessed again within a period of time after being accessed, therefore, SQL Server decides to keep the data in the memory. But what if the system crashes or the system loses power? SQL server adopts a work und. It writes the modification operation to another file, which is called a log.

So SQL Server is like this. Every time we modify a piece of data, SQL Server will write the data to the log file. If the system crashes, SQL Server only needs to know what operations we have performed from this log file. We only need to perform these operations again to restore the data.

It seems that we haven't talked about recovery interval.

Yes, it's coming. Although the method described above is good, the modified data cannot be stored in the memory all the time. In addition to limited memory, there is another important reason: SQL Server cannot rely solely on logs to ensure data consistency.

SQL Server usually runs for several months without restarting. This is not like the notebook we use. If it crashes after three months of operation, my God! Do we need to repeat the three-month operation ?! Of course this is not the case. Recovery interval will control all this!

The recovery interval tells SQL Server how long it takes to restore data through operations in the log. SQL Server is optimistic about all this. When it finds that the new operations in the log need to be restored after the time of the recovery interval, SQL Server is like forcibly writing the modified data in the memory back to the disk.

In this way, we do not need to re-execute the three-month operation. Generally, it is only 5 minutes, because the default recovery interval is 5 minutes.

How can we adjust this value?

The longer the value of recovery interval, it means that if our system crashes, we will need a longer period of time to recover, however, it means that the modified data can be kept in the memory for a longer time, which means that we can write less data to the disk.

On the contrary, if the value of recovery interval is about short, it means that in case of a system crash, we need to restore it in a shorter time, on the other hand, it means that we can write data to disks more frequently.

The following example sets the system recovery interval to 3 minutes.

Use master0
Exec sp_configure 'recovery interval', '3'
Reconfigure with override