Tuesday 24 September 2013

What are the difference between SIMPLE and FULL recovery Model ?


BASE
SIMPLE Recovery Model
FULL Recovery Model
Incremental Backups
Transaction LOG database backup options are not available in Simple recovery Model.
Transaction LOG database backup options are available in FULL / Bulk Logged recovery Model.
POINT in Time Recovery
Point in Time recovery is not supported.
POINT IN Time recovery can be performed in case of FULL recovery model
DISK Space
No large disk space is required for transaction logs.
LARGE DISK Space might be required in case of FULL Recovery model for heavily utilized OLTP system.
Auto Truncate
Simple recovery, forces the log to truncate (marked area for reuse) when the database writes data to the disk (CHECKPOINT)
DBA need to manually backup and clear the database log file.
Transaction Behavior
When the Transaction completes, relevant portion of the log is marked as inactive and marked for reuse
When the Transaction completes, relevant portion of the log is still kept occupied in log.
PERFORMENCE
Permits high-performance bulk copy operations.
Delay the bulk logged operations as every transactions is written t log file. In case if Log file fills, it grows, which is added delay.
Data LOSS
In case complete disk corruption, Simple recovery might loose some transactions which occurred after full backup
Data can by recovered up to any level.
LOG Management
SQL Server automatically managed transaction log.
DBA assistance is required to manage transaction log, in case of no backup log plan, Log file will keep growing and you might see log files is larger than data files.
Virtual Log File Count
As SQL Server automatically clears transaction log on checkpoint, which keeps transaction log size and control and result higher possibility for not to increase VLF counts
As every transaction is recorded in transaction and log will keep growing in case of high transaction which might result in increase number of VLF, which affect performance.

No comments:

Post a Comment