Transactions Rolled Back in Database

Download PDF

IF you are browsing your error log and come across an error message stating that “1 transactions rolled forward in database ‘msdb'”, or “X transactions rolled back in database ‘msdb'” you might be a bit alarmed. You might also notice similar error messages for master, tempdb or user databases.

msdb transactions rolled back in database

 

How can this be a good thing? Why are transactions being rolled back or rolled forward?

The interesting thing here is the database ID for the database with the transactions being rolled back or forward. In this case (shown in the screenshot), msdb has an database id of 10. But how can that be, msdb normally has a database id of 4, and it always has and id of 4.

You can verify this by querying sys.databases.


SELECT *
FROM sys.databases;

sys.databases transactions rolled back in database

 

What is actually happening is that a DBCC CheckDB is being run against msdb. When CheckDB is run, a snapshot is taken, and the id of that database snapshot is 10. The transactions are being rolled back, or rolled forward on the snapshot to get it to a clean point to start the CheckDB process. This is nothing to worry about, just a normal part of the DBCC CheckDB process.

Related Links:

Posted in SQL Server Tagged with:
One comment on “Transactions Rolled Back in Database
  1. Hermann Lotter says:

    Very nice to know. Cool website Steve, I’m enjoying your content.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.