SQL: Recovery and Recovery Interval

Usually under recovery many people understand the process of restoring a database after a disaster, so you take an available backup, restore it and the time when the database is being restored is called recovery. This is not the correct definition of recovery in the pure database naming convention...

Recovery is quite a normal process in a database live and and is performed quite often, not only after database failure. Actually after each server restart, when databases are attached to the server, it needs to verify if the content of a database file is consistent with the content of the transaction log. This process is named recovery.

Yes. Don't be surprised, that after server restart some databases may be found in recovery status! Just sit and wait, looking from time to time into the errorlog to see the progress. After the recovery is done, the database will be available in normal state again!

What kind of situation can be met during recovery?

• in the transaction log, a transaction is found that was committed, but still not saved in the database
• in the transaction log, a transaction is found that was not committed.

It is easy to guess, what a server should do with those transactions:

• in the case of a committed transaction, it should be ROLLED FORWARD what means it should be copied into the database file. Normally this would happen during a checkpoint
• in the case of a non committed transaction, it should be ROLLED BACK, what means that the transaction will not be copied into the database.

When the process is finished, we can say that all pages are clean again, so basically the recovery process is very like to a normal checkpoint with the difference, that checkpoint never rollbacks any unfinished transaction, while recovery rolls back any uncommitted transactions found in the transaction log.

Of course process of recovery can take some time during server startup procedure and administrators would like not to wait too long. If in the transaction log, there would be millions of committed but not check pointed transactions, than the recovery would need to update many pages in the database and the process will last and last...

It would be great to have the possibility to define how often the normal checkpoint should fire.Often performed checkpoint will cause the database work a bit slower as the content of buffer will need to be synchronized with the database and the IO will be high, but in case of some server failure, the recovery process should be quick. Seldom performed checkpoint, in opposite will not cause bigger delays during normal database functioning, but in case of failure, longer time will be taken to do the recovery of the database.

There is such a parameter! It is called "recovery interval in minutes". The idea is, that if that parameter is set to a value of 1 minute, the recovery performed at startup should take no more than one minute. (the same can be said about a normal checkpoint, it also should not take more than that one minute). This indicates that checkpoints will be fired frequently enough to support such a recovery time. 

The value of the parameter can be displayed using sp_configure command:

dbo.sp_configure 'recovery interval in minutes'

And this is the default output of the command:

To change the value to 2 minutes issue following command:

dbo.sp_configure 'recovery interval in minutes',2

The output should look like:

As you see, the new value has been applied at once, no restart was required, indeed, this is information displayed on the messages tab:

BTW, all the configuration done by sp_configure was indeed done in the configuration file located in:
/opt/sap/ASE-16_0/SAP01.cfg, Whenever a change is introduced to the configuration a backup of that file is created, so it is easy to find what has been changed:

diff SAP01.bak SAP01.cfg