In MsSql server the recovery model defines
- how transactions are logged in the transaction log
- if required (and allowed) to back up the transaction log
- what types of restore are available.
In other words, the recovery model dictates what kind of backup and restore is available.
The recovery model of a database is set by using the Properties dialog in SSMS. There are three recovery models:
Simple
Each transaction is logged in the transaction log, but when the transaction is completed and data is committed to the data file (in .mdf) then that space in the transaction log, for that, now committed, transaction, is considered available for reuse by subsequent transactions.
You can not do point-in-time recovery.
You can not have Always On or Mirroring.
Full
Each transaction is recorded in the transaction log and remains there. So, the transaction log continues to grow indefinitely. Unless we backup the transaction log. Then the transaction log is truncated to the time of that backup. [In order to perform a transaction log backup, the database recovery model must be set to Full. When recovery model is set to Full then the “Backup type” combo-box of the Backup dialog box, displays all three options: Full, Differential and Transaction log. Simple recovery model only shows the first two]
You can do point-in-time recovery.
You can have Always On or Mirroring.
Bulk-logged
It is a special sub-category of the Full recovery model. It works like Full except that transactions relating to bulk operations, such as CREATE INDEX or BULK INSERT, are NOT fully recorded. That is, the transactions that make MASSIVE changes to the database are NOT fully recorded.
Conclusions
- The recovery model is not something that we are changing all the time. What recovery model a database should use, it is a serious decision. It should be taken by someone who knows and understands the matter.
- In most cases the Simple recovery model is enough. Since we are not interested in point-in-time recovery, and we get a backup a couple of times per day, then we can withstand a recovery with a backup that is some hours away from the disaster.
- If we have Always On then the Full recovery model is our only choice.
- To have a Full recovery model without getting a transaction log backup too, does not make much sense. And the transaction log will grow forever.
- The
DBCC SHRINKFILE (LogFileName, ToFileSizeInMB)will shrink the transaction log depending on 1) the recovery model and 2) the empty space in the transaction log. So, if the recovery model is Full, the shrinking can be negligible. That’s because the Full recovery model MUST necessarily keep in transaction log ALL records of transactions since the last transaction log backup. - So, various “killer scripts” that circulate around, as well as the dangerous example of Microsoft docs from where most of them are taken, are really dangerous, because THEY CHANGE THE RECOVERY MODEL without taking anything further into account. Such scripts should be used only in panic situations.
