none
Recovery model and transaction log size RRS feed

  • Question

  • Hi,
    What is the relationship between recovery model and transaction log? How does recovery model affect txn log file size?
    How to decide which model should I use?

    Thank you
    Sunday, December 23, 2007 12:47 AM

Answers

  • Pls refer the link for the types of Recovery Models in Sql server, http://msdn2.microsoft.com/en-us/library/ms189275.aspx
    http://msdn2.microsoft.com/en-us/library/ms175987.aspx  - Choosing Recovery Model
    http://msdn2.microsoft.com/en-us/library/ms189085.aspx  - Truncate Transaction log

    In Simple Recovery model you cannot take Transaction log backups and hence if your server is down you can restore your db from only the last full backup. All the data changes made since the last full backup will be lost if you don't have transaction log backup. The log file growth will be curtailed in Simple Recovery model as the log is truncated after checkpoint.

    The full recovery and bulk-logged recovery models provide greater protection for data than the simple recovery model.
    You can take Transaction log backup and hence Point in time recovery is possible and so you can restore your db and minimise the data loss. If you are using Full or Bulk logged ensure that you take regular Tran log backup else the log file will grow and consume the disk space...

    Under Bulk logged recovery model, most bulk operations are only minimally logged. For certain large-scale bulk operations such as bulk import or index creation, switching temporarily to the bulk-logged recovery model increases performance and reduces log space consumption. Log backups are still required. Like the full recovery model, the bulk-logged recovery model retains transaction log records until after they are backed up.

    Depending on your bussiness needs you need to choose one among them..

    - Deepak
    Sunday, December 23, 2007 12:59 AM
    Moderator

All replies

  • Pls refer the link for the types of Recovery Models in Sql server, http://msdn2.microsoft.com/en-us/library/ms189275.aspx
    http://msdn2.microsoft.com/en-us/library/ms175987.aspx  - Choosing Recovery Model
    http://msdn2.microsoft.com/en-us/library/ms189085.aspx  - Truncate Transaction log

    In Simple Recovery model you cannot take Transaction log backups and hence if your server is down you can restore your db from only the last full backup. All the data changes made since the last full backup will be lost if you don't have transaction log backup. The log file growth will be curtailed in Simple Recovery model as the log is truncated after checkpoint.

    The full recovery and bulk-logged recovery models provide greater protection for data than the simple recovery model.
    You can take Transaction log backup and hence Point in time recovery is possible and so you can restore your db and minimise the data loss. If you are using Full or Bulk logged ensure that you take regular Tran log backup else the log file will grow and consume the disk space...

    Under Bulk logged recovery model, most bulk operations are only minimally logged. For certain large-scale bulk operations such as bulk import or index creation, switching temporarily to the bulk-logged recovery model increases performance and reduces log space consumption. Log backups are still required. Like the full recovery model, the bulk-logged recovery model retains transaction log records until after they are backed up.

    Depending on your bussiness needs you need to choose one among them..

    - Deepak
    Sunday, December 23, 2007 12:59 AM
    Moderator
  • Recovery model is related to TLOG File

    If Recovery model is in SIMPLE

    We can't take TLOG Backup because in that model tlog file contails only active transactions


    LeelaK
    Tuesday, January 20, 2009 10:56 AM