INF: Why Syslogs Has Exclusive Table Lock During Dumps

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

Issuing an "sp_lock" while a database or transaction log is being dumped will always show that there is an "Ex_table" (exclusive table) lock on the object with table_id of 8. Object 8 refers to the syslogs table, which is the transaction log and is present in every database. The exclusive table lock that is seen on syslogs is a special case, and unlike exclusive table locks on all other tables, it allows updates to be made to the log.

MORE INFORMATION

When a table has been locked with an exclusive table lock, no users (except for the user holding the lock) are allowed to select, insert, delete, or update any row in that table until the exclusive lock is dropped. The syslogs table is a special case and does not follow this behavior.

The only time an exclusive table lock is placed on the syslogs table is when the database or transaction log is being dumped. The sole purpose of this exclusive table lock is to ensure that only one user at a time can dump the database or log. When a DUMP DATABASE or DUMP TRANSACTION command is issued, the syslogs table is checked to see if there is currently an exclusive table lock on it. If there is, SQL Server assumes that another user is currently dumping that database or log, and the user attempting to dump will be blocked until the current dump is completed.

Despite the fact that syslogs has an exclusive table lock on it during dumps, this in no way prevents new transactions from being logged. This is accomplished through SQL Server's Dynamic Dump feature, which allows users to continue using a database while it or its log is being dumped.