PRB: Error Log Shows One Transaction Rolled Forward on Startup

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

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; therefore, the result are 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.