locked
Simple recovery mode RRS feed

  • Question

  • If I change a DB from Full to simple recovery mode is it going to write any transactions to LDF file. Can I delete the ldf file and the DB would still be active.
    Rama DBA
    Tuesday, December 13, 2011 9:42 PM

Answers

  • Hi

    SIMPLE recovery model means committed transactions will be marked as reusable by 
    CHECKPOINT which occurs on interval period or when 70% of the log file is full

    NEVER DELETE .LDF FILE Your Database will goto Inactive you may have to do a complete Restore. If you need to Reduce then t-log size

    use DBCC SHRINKFile.

     For Better understand about File types. Please have a look on

    http://msdn.microsoft.com/en-us/library/ms189563.aspx

     


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::
    • Edited by Naga_78 Tuesday, December 13, 2011 10:38 PM
    • Marked as answer by Ramshna Tuesday, December 13, 2011 11:08 PM
    Tuesday, December 13, 2011 10:37 PM
  • Hi,

    SQL uses write ahead logging no matter which recovery mode is set. Therefore SQL must be able to write to the transaction log or the DB will fail. The TLOG is circular in nature and in simple mode the transactions that are committed and checkpointed are flagged to allow them to be overwritten in the log. Therefore when in Simple mode when SQL reaches the end of the logfile if the entries at the beginning of the TLOG have been checkpointed SQL switches back to the beginning of the log and overwrites those entries.

    If the transactions at the beginning of the log have not been checkpointed and SQL reaches the end of the log even in Simple mode the TLOG will autogrow.

    You should not need to shrink the TLOG regularly. For best performance the TLOG should be statically set to the correct size for your requirements and allow autogrow in decent sized increments to cope for emergency or exceptional operations so the log does not fill and stall the DB.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog or Twitter.
    Please click the Mark as Answer button if a post solves your problem!


    • Edited by Sean Massey Tuesday, December 13, 2011 10:45 PM
    • Marked as answer by Ramshna Tuesday, December 13, 2011 11:07 PM
    Tuesday, December 13, 2011 10:43 PM

All replies

  • Hi,

    Have a look at the following thread; -

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1e1333a3-b0c3-4f15-a4ae-e5aa4dd3cf3e

    and the following MSDN link; -

    http://msdn.microsoft.com/en-us/library/ms191164.aspx

    You can right click your mouse on your database to shrink your log file as follows; -

     


    then select options similar to the following and click on the "OK" button; -

    The database would remain active after you deleted the log file.

    In short use Full Recovery model for transactional systems where you would need to perform a disaster recovery to a particular point in time, e.g. 10:30 am on Wednesday (e.g. a retail Point of Sale System)

    Use Simple Recovery where you just need to recover to the most recent backup of the database, e.g. a none real time Business Intelligence System (e.g. a Retail Back Office Dashboard tool).

    I hope this helps.

    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood


    Tuesday, December 13, 2011 9:49 PM
  • Thanks Kieran. Just want to reiterate what you said ,

    I understand the use of Full recovery mode and simple. In the above forum link Dan says "transaction log just needs to be sized to accomodate your largest transaction", I guess that means you do need LDF files for any transaction to occur, just that in simple they are truncated by the SQL server itself after every transactions. Correct?


    Rama DBA
    Tuesday, December 13, 2011 10:35 PM
  • Hi

    SIMPLE recovery model means committed transactions will be marked as reusable by 
    CHECKPOINT which occurs on interval period or when 70% of the log file is full

    NEVER DELETE .LDF FILE Your Database will goto Inactive you may have to do a complete Restore. If you need to Reduce then t-log size

    use DBCC SHRINKFile.

     For Better understand about File types. Please have a look on

    http://msdn.microsoft.com/en-us/library/ms189563.aspx

     


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::
    • Edited by Naga_78 Tuesday, December 13, 2011 10:38 PM
    • Marked as answer by Ramshna Tuesday, December 13, 2011 11:08 PM
    Tuesday, December 13, 2011 10:37 PM
  • Hi,

    SQL uses write ahead logging no matter which recovery mode is set. Therefore SQL must be able to write to the transaction log or the DB will fail. The TLOG is circular in nature and in simple mode the transactions that are committed and checkpointed are flagged to allow them to be overwritten in the log. Therefore when in Simple mode when SQL reaches the end of the logfile if the entries at the beginning of the TLOG have been checkpointed SQL switches back to the beginning of the log and overwrites those entries.

    If the transactions at the beginning of the log have not been checkpointed and SQL reaches the end of the log even in Simple mode the TLOG will autogrow.

    You should not need to shrink the TLOG regularly. For best performance the TLOG should be statically set to the correct size for your requirements and allow autogrow in decent sized increments to cope for emergency or exceptional operations so the log does not fill and stall the DB.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog or Twitter.
    Please click the Mark as Answer button if a post solves your problem!


    • Edited by Sean Massey Tuesday, December 13, 2011 10:45 PM
    • Marked as answer by Ramshna Tuesday, December 13, 2011 11:07 PM
    Tuesday, December 13, 2011 10:43 PM
  • Hi Ramshna !

    You may also need to look at below article from Pinal Dave;

    http://blog.sqlauthority.com/2007/06/13/sql-server-recovery-models-and-selection/

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    Tuesday, December 13, 2011 11:13 PM
    Answerer
  • If the database is using simple recovery model and the log file runs out of space (disk is full) is it possible for committed transactions to be rolled back? Here is why I ask.

    My MSDB database is using SIMPLE recovery model.

    I never set up a maintenance job to purge backup/restore history and the database grew quite large.

    I started deleted a few days at a time with sp_delete_backuphistory. I did this perhaps 10-12 times thinking that committed transactions would be durable.

    During the final execution the disk filled and it rolled back EVERYTHING to point where I first started. IOW my committed transactions were gone.

    Did I encounter some sort of bug or is this possible and expected?

    I am using SQL 2008 R2 SP1.


    Chuck

    Monday, May 14, 2012 2:41 PM
  • Hi Chuck,

    What file filled the disk? Was it the LDF for MSDB?

    It is possible that in Simple you could have a rollback event. Even in Simple mode SQL still uses write ahead logging and therefore it writes the log information to the log file. As you probably know in Simple the log is truncated when a checkpoint occurs. Now this normally happens explicitly, when the log is 70% full or when the recovery interval is reached.

    However even in Simple the log can only be truncated as far back as the MinLSN which is the start of the active portion of the log. Therefore if there was a long running transaction that the checkpoint in Simple mode could not truncate then all the log records were available in the log for rollback and roll forward during recovery.

    It is impossible for me to say for sure in your specific case but this is a best guess based on how the log works. You could replicate the scenario and use DBCC LOG or fn_dblog to see the contents of the MSDB LDF file.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Monday, May 14, 2012 10:33 PM