Considerations for Using enforce dump tran sequence

enforce dump tran sequence prevents operations that disallow a subsequent dump transaction.

  • false – (the default) does not affect operations that interfere with dump transactions.

  • true – disallows operations that prevent a dump transaction.

You can set this option to true, only if the database:
  • Is a dedicated log database.

  • Is not an archive database.

  • Is not a local or global temporary database.

  • Is not read-only.

  • Was not brought online for standby access.

  • Has full durability. Databases with at_shutdown and no_recovery durability are not allowed.

  • Has select into/bulk copy/pllsort or trunc log on chkpt set to false. If any of these options are true, they automatically reset to false.

  • Does not need a dump database due to one of the following reasons. Perform a dump database before setting this database option to true.
    • A partially logged update has been done, for example, select into, alter table modify, reorg rebuild, fast bcp, and writetext.

    • The transaction log was truncated.

    • It is a newly created or upgraded database.

    If the database option enforce dump tran sequence is true, you cannot:
    • Set select into/bulk copy/pllsort to true. Commands with partial logging are not allowed.

    • Set trunc log on chkpt to true. The log cannot be truncated by the checkpoint process.

    • Execute dump tran with truncate_only or dump tran with no_log. The log cannot be truncated without dumping it to an archive device.

    • Mark the database as read-only.

    • Change durability from full to at_shutdown or no_recovery.

    • Change to be a mixed-log-and-data database. In cases like load database and dbcc findstranded where the database may be changed to mixed log and data.