Post reply

Prod DB on Simple Recovery Model

  • April 8, 2016 at 4:37 am

    #317946

    Hi Team,

    We run re-indexing and update statistics on daily basis during which we change recovery model to Simple and after completion back to full.

    Now in new DB environment, we are told to switch to Simple mode permanently as it would effect the transaction log backup jobs which runs every 15 mins.

    Since we are changing to SIMPLE mode during re-indexing, so we are told change to Simple permanently.

    My Questions:

    > What are the disadvantages of making it to Simple mode, is it recommended.I know it would vary on application design wise, but I need a holistic view on it.

    Thanks.

  • April 8, 2016 at 4:55 am

    #1870745

    No point in time recovery. If you're doing full backups once a day, then in simple recovery you're risking up to 24 hours of data loss in a disaster, where at the moment your exposure is 15 minutes max.

    Can the business tolerate that?

    And there's no need to switch to simple for index maintenance. Bulk-logged if you don't want the logs to grow too large, but not simple. Again, it means you're risking excessive data loss.

    For example, if you switch to simple after the full backup and don't take a full/diff after switching back to full, then log backups will fail and you'll be risking excessive data loss.

    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

  • April 8, 2016 at 8:26 am

    #1870815

    GonnaCatchIT (4/8/2016)

    We run re-indexing and update statistics on daily basis

    Is that really necessary? Do you re-index all tables or just those that really need it? Hopefully you don't update stats on indexes that have just been re-built, as that's not only a waste of resources, but you can get less accurate stats for that index than what was already there.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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