Recovery Models -- Recovery model defines how to maintain log file in SQL Server
There are 3 recovery models
1. Simple 2. Bulk – Logged 3. FULL
Simple Recovery Model
Every DML & DDL operation will record into T-log file (to support basic recovery ability when SQL Server shutdown or a long operation is being cancelled)
Using this recovery model we can’t achieve point in time recovery
Log file gets truncated after the transactions being committed or checkpoint occurred.
No log backups can be generated in this mode
Not able to configure Logshipping or mirroring using this mode
In simple recovery model we can generate FULL, Differential backups (except TLog backups)
It is suitable for Test / DEV environments where point in recovery is not required.
Bulk Logged Recovery Model
Every DML & DDL operation will record into T-Log file except Bulk Logged operations such as SELECT INTO, BULK INSERT, CREATE INDEX etc.,
The advantage of using this recovery model is it doesn’t occupy large space of transaction log while doing bulk operations
If there are no Bulk operations then it acts like a FULL recovery model.
Logshipping can be configured using this recovery model, Mirroring cannot be configured.
Point in time recovery is possible however we need to re-run the Bulk operations.
In this we can generate all kinds of backups (full, diff, tlog etc)
It is suitable where normal data is critical whereas bulk operations are not critical.
FULL Recovery model
Every DML & DDL operation will record into T-Log file
Using this recovery model Point in time recovery is possible
Logshipping and Mirroring can be configured in this recovery model
Log file grows very faster
Checkpoint Process
It is a program executed by SQL Server to transfer all committed transactions from Transaction log to data file
It takes all committed transactions PageIds, LSN from buffer pool and writes into respective pages in the data file
Lazy writer is a process which takes pages from data cache and writes into data file
Checkpoint truncates TLog file, If recovery model is set to Simple
Checkpoint occurs periodically (based on recovery interval) or can be issued manually.
It occurs automatically when a backup start.
It occurs if size of Tlog file is 70% full.
When a new data file or log file was added to database.
No comments: