Recovery model
A recovery model is a database property that controls how transactions are logged. Backup and restore operations occur within the context of the recovery model of the database.
Why log transactions?
To guarantee ACID properties over crashes or hardware failures every action is logged (to disk). In case of system failure, it would read the log and redo/undo the logged actions to bring the database to a consistent state.
Where it is logged?
MS SQL Server maintains mainly two kinds of files. Data file and log file. Log file can map to one or more physical files. Also note that transactional log is different from other logs like setup log, error log, profiler log, agent log, event log etc.
Types of recovery model
Depending on how extensively MS SQL Server logs there are 3 types of recovery models. They are:
- Simple recovery model
- Full recovery model
- Bulked logged recovery model
Simple recovery model
Suppose every day you take a backup of the database. If there is a failure, you at most lose the data for one day and you can afford that. In that case, you can choose simple recovery model. Here, every transaction is logged. However, as soon as those transactions are written to disk (checkpoint), the log is discarded.
Here you don’t have the provision to take back up for log. You only backup data. And as said before, in case of failure, you can recover the data that you backed up last time. You loose everything else, done since the data backup.
You get rid of the administrative task of taking the log backup. You get rid of running into the risk of managing very large log files.
However you lose the following features:
Log shipping – This is a disaster recovery technique where the transactional log is copied from primary (production) database on a primary server (the production MS SQL Server instance) to one or more secondary database (warm backup of primary database) on separate secondary server instance (where warm standby copy/copies is/are stored). This feature works with the help of full recovery model that will be discussed a bit later.
AlwaysOn or Database mirroring – These features support/create fail-over environment using redundancy. If the primary database fails, the secondary (not backups) database takes over. These features also work with the help full recovery model.
Media recovery without data loss – If media (tape/disk) fails recovery without data loss not possible. It needs full recovery model.
Point-in-time restores – You cannot restore the database to any arbitrary point defined by a date and time. For this to work, you need full recover model.
Full recovery model
By this time, it is clear that for the features above to work you need full recovery model. Well, it will log all the transactions from the beginning even if those transactions are written from memory to disk. So it is obvious that the log size will be too big. It means there is provision to take log backups. And that you have to take log backup regularly else you might run out of production server disk space. Yes, if you backup log then the backed-up up log will be removed from the database log file.
Armed with full recovery model (with the pain of administrating transactional log) you won’t lose any data due to a lost or damaged data file. You can restore database to any point of time.
Unless the tail of transactional log file is damaged all is good. Else you lose the data since last time you took a log backup.
Bulk recovery model
Same as full recovery model just that for the bulk operations (to be precise, a set of operations those can be minimally logged) it does not log everything. This model allows bulk copy to work with high performance by not logging all transactions.
Point-in-time recovery not supported. Recovery possible to the end of any (data and log) backup.
If log is damaged or bulk logged operations occurred since the most recent log backup, changes since the last backup will be lost. Else no data is lost.
Why log size too big?
This is a typical problem people face.
You can check log_reuse_wait_desc column in sys.databases. It will tell you why?
However, most likely the database is using the default full recovery model. You are unaware of it: especially, if the database is created by SharePoint. And you forgot to take regular log backup.
Sharing Experiences 