Post reply

Error: 3023, Severity: 16, State: 2

  • June 8, 2009 at 5:10 pm

    #134850

    Hi,

    I got this below error in the error log, when the scheduled backup job is running. This occurred only one time.

    Error: 3023, Severity: 16, State: 2

    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.

    But I did not do any ALTER DATABASE ADD FILE at that time..

    what exactly this error means? Is it a critical error to consider?

  • June 8, 2009 at 9:55 pm

    #1006409

    This means some other backup or job running which is using the same file.

    Following are the few things which are not supposed to be running while the database backup is starting:

    1.ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options; INSERT, UPDATE, or DELETE statements are allowed during a backup operation.

    2.Shrink database or shrink file. This includes autoshrink operations.

    3.CREATE INDEX. This restriction applies to database backup, not log backup.

    4.Nonlogged operations such as bulk load, SELECT INTO, WRITETEXT, and UPDATETEXT. The database option select into/bulkcopy must be enabled for these operations to be nonlogged

    "More Green More Oxygen !! Plant a tree today"

  • June 11, 2009 at 9:48 am

    #1008111

    I got the same error second time today(after 5 days) and investigated all the databases. I noticed that for one database auto shrink is enabled.

    If this is the reason, the error should come daily but it not coming up daily. No other jobs are running at the time backup job is running

    please advice me what will be the exact reason.

    thanks

  • June 11, 2009 at 9:52 am

    #1008112

    Are there 2 or more jobs running at the same time, which might affect the same database, like while the backup is running the transaction log can also kick in or some other optimization Job...

    Maninder
    www.dbanation.com

  • June 11, 2009 at 10:16 am

    #1008130

    I thoroughly checked, NO other jobs are running.

    But in SQL Server 2005, Full, diff and log backups can run at the same time as I understood.

    thanks

  • June 11, 2009 at 10:36 am

    #1008146

    Klnsuddu,

    Here is your explanation and the link:

    If a shrink operation tries to truncate a file while a backup is running, the shrink stops without truncating the file, however data pages have been relocated. If a backup is started just as a file is being truncated, backup normally waits long enough for the file truncation to complete.

    http://doc.ddart.net/mssql/sql2000/html/trblsql/tr_reslsyserr_2_7j8z.htm

    Maninder
    www.dbanation.com

Viewing 6 posts - 1 through 5 (of 5 total)

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