Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    South Africa
    Posts
    9

    Question Unanswered: Log Backups confusing me

    Please help me out on log backups.


    What happens when 2 log backups of the same db happen simultaneously?

    Hypothetically:
    One Backup Log job run by SQL Server agent(xp_SQLmaint) every 60 minutes & one Log backup run by Backup Exec every 59 minutes.

    Is this a dumb idea and will it cause restore problems based on LSN's contained in the 2 separate backup sets?

    Thanks
    G

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I believe you would get error 3023 (Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.) when the 2nd tran log tries to start while the 1st was running.

  3. #3
    Join Date
    Dec 2003
    Location
    South Africa
    Posts
    9
    Thanks

    So what you're saying is that it's impossible run 2 log backups of the same db at the same time. This implies that one of my jobs must be killed. Which would you use? xp_sqlmaint or backup exec SQL agent(Log backup without truncate)?

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    We create maintenance plans for each user db and group the system dbs into a single plan. All dbs get a nightly full backup (except tempdb of course). The user dbs get tran logs that range from every 15 mins to every 2 hours based on importance and use.

    SQL Agent runs the jobs, so they run under the xp_sqlmaint exec (except for the SQL LiteSpeed xp).

    The final choice is yours. Which way do you feel most comfortable with? You will be the one (I assume) that will have to do the restore if everything goes to heck in a handbasket!

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    And to answer your last question, there is no way to ever run two transaction log backups on the database at the exact same time. The second job will just fail.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •