SQL maintenance full backup woes on SQL 2005

Home Forums Other Microsoft Servers and SaaS SQL Server 2005 / 2008 / 2008 R2 / 2012 / 2016 SQL maintenance full backup woes on SQL 2005

This topic contains 2 replies, has 2 voices, and was last updated by  universal 9 months, 3 weeks ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts

  • ranjb
    Member
    #167406

    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

    Message
    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.

    Thanks


    universal
    Member
    #388841
    ranjb;n515969 wrote:
    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)?


    ranjb
    Member
    #352444

    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.

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

You must be logged in to reply to this topic.