ID Number: Q81340
1.10 1.11 4.20
OS/2
Summary:
SYMPTOMS
Every time SQL Server is started, the error log shows that one
transaction was rolled forward in certain databases. This happens
even if the SHUTDOWN command is used, which according to the
documentation, performs a CHECKPOINT, thereby resulting in no
transactions needing to be rolled forward when SQL Server starts
up.
CAUSE
The documentation correctly states that if the SHUTDOWN command is
issued to stop SQL Server, or if a CHECKPOINT is issued prior to
stopping SQL Server, no transactions will need to be rolled forward
on the next startup.
The likely cause of always having one transaction being rolled
forward every time SQL Server is started is having a database that
has the "trunc. log on chkpt." database option enabled. If this
option is set on, the transaction log for that database will be
truncated each time the checkpoint checking process issues a
CHECKPOINT; in essence, it automatically performs a DUMP
TRANSACTION WITH TRUNCATE_ONLY for that database.
Because a SHUTDOWN performs a CHECKPOINT prior to stopping SQL
Server, it also truncates the log at that time. Since a CHECKPOINT
keeps track of all uncommitted transactions, the CHECKPOINT record
in the log shows the dump process from the truncation of the log as
being an open transaction at the time the CHECKPOINT was issued.
When SQL Server is restarted, it scans the transaction log of each
database, rolling back any uncommitted transactions, and rolling
forward those that had completed but not written to disk during the
last CHECKPOINT. For the CHECKPOINT record that shows the dump
process as being open, SQL Server rolls forward that transaction to
show that it had completed.