Post reply

What are the implications of using the simple recovery model?

  • December 1, 2010 at 7:54 pm

    #231769

    Hi all,

    I work for a software company whose software uses SQL. We use full back-ups to back-up the data. One issue I run into a lot is massive transaction log growth. I suppose this is as a result of the use of our software and more importantly, the results of some administrative work I do on the databases. The servers also host high-resolution images so space usually becomes scarce eventually.

    The transaction log size is also a huge issue when we bring back databases to our office for error analysis. I would like to do away with using the transaction logs altogether, since we restore from full backups when necessary and never use point-in-time restore. I also wonder if the absence of disk I/O to the server involved in the maintenance of the transaction log would decrease the demands on server resources substantially.

    The integrity of our clients' data is paramount, of course. I've read some stuff about the simple recovery mode, and my understanding is that the only thing it precludes is point-in-time recovery.

    My question is: are there any other implications of switching the database to simple recovery mode? Assuming that a couple of uncorrupted databases exist, do transaction logs have any other benefits to the integrity or performance of the database? I would appreciate any advice anyone can give. Thanks.

  • December 1, 2010 at 10:36 pm

    #1257722

    Please go through this[/url] useful article on SSC.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • December 2, 2010 at 12:43 am

    #1257737

    And also look at this

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

  • December 2, 2010 at 2:27 am

    #1257757

    Switching to simple does not remove logging. It just means that SQL will automatically mark the log space as reusable and you won't need log backups.

    Simple recovery means:

    Restore only possible to full/diff backup. That means if you take a backup a day and the database fails 10 min before the next full backup, you've lost a day's data.

    No piecemeal restores, so if you're using or planning to use file/filegroup backups, you won't be able to restore from them.

    No database mirroring

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply