No announcement yet.

SQL maintenance full backup woes on SQL 2005

  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL maintenance full backup woes on SQL 2005

    Hi Fellow Techs

    I was wondering if I could get some support/advice from some SQL Guru's as I been having a problem with a legacy SQL server being backed up and have tried a number of steps however I not been able to get this issue resolved. This legacy server unfortunately runs 2005 ver 9.0.5069.

    So I have setup some SQL maintenance backup plans to schedule a full backup of a Database every night. What I have found is sometimes the backup works as expected but most of the time its just runs and takes days for the job to finish.

    When looking at the error logs I am seeing codes related to the following as well as one message which consistently comes up.

    Error: 3041, Severity: 16, State: 1.
    Error: 3023, Severity: 16, State: 2.
    Source spid20s

    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.

    I attach the full recent log with this.

    What I have tried:
    • Created a maintenance task to check database integrity and rebuild the index, update the statistics. This job runs successfully once a week.
    • Rebooted the server and SQL related services.
    • Tried an alternative 3rd party backup software Redgate SQL Backup and have the same problem with the 3 party tool.

    It saying the database must be serialized. What exactly does this mean?

    Any help on this would be appreciated.

    Attached Files

  • #2
    Originally posted by ranjb View Post
    It saying the database must be serialized. What exactly does this mean?
    No, it says that certain operations, like ALTER TABLE, must be serialized, as in multiple such operations cannot be performed in parallel. It seems you're trying to run two or more jobs concurrently, or the dreaded Something Else is locking your database.

    It's clear from the "I/O is frozen" and "I/O is resumed" messages that Shadow Copy is involved. How exactly are you backing up the database(s)?


    • #3
      Thanks, in the end I change the backup job and this has sort of solved but the limitation is it now creates one DB backup file, and overwrites this file on each backup schedule. Not the greatest solution but it works so I am happy.