10 Difference between SIMPLE vs FULL recovery model

Leave a comment (0) Go to comments

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. Check my article http://goo.gl/8SNuH for more details
EOF - 10 Difference between SIMPLE vs FULL recovery model, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.