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