errorlog中的异常信息rolled forward 和rolled back

Question

windows 2008 sp1，SQL SERVER 2008 SP1。cluster

每周六凌晨1点会出现以下信息，服务器及数据库未出现重启，节点未切换，filestream access level =0，请各位高手帮忙解释，是什么原因导致的。

Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

148 transactions rolled forward in database 'XX_DB' (12). This is an informational message only. No user action is required.

1 transactions rolled back in database 'XX_DB' (12). This is an informational message only. No user action is required.

Recovery completed for database XX_DB (database ID 12) in 21 second(s) (analysis 22 ms, redo 15062 ms, undo 3293 ms.) This is an informational message only. No user action is required.

Answer 1

Was cluster failed over or sql restarted?

Answer 2

no           

Answer 3

aboat checkdb?

Answer 4

之前以为是数据库做checkpoint，lazy writer ,eager writer的原因

刚才想了一下，有可能是维护计划，在深夜备份数据库，所以需要回滚未提交事务

或前滚已经提交的事务

请LZ检查一下维护计划，FILESTREAM也是

Answer 5

桦仔别捣乱，备份数据库不会引起回滚和前滚。

楼主检查一下有没有数据库offline, restore之类的操作。

---

想不想时已是想，不如不想都不想。

Answer 6

不好意思，想错了

Answer 7

aboat checkdb?

Nothing to do with 'dbcc checkdb', sql does rollback/roll forward when starting or restoring db as said above.

Answer 8

这个发生的时间确实是有dbcc checkdb的维护计划，这些消息在dbcc checkdb的前几秒在日志里出现。

这个时间段没有备份任务。

Answer 9

没有offline，restore之类的操作。

Answer 10

Possible to post more message in sql log around that time?

Answer 11

Possible to post more message in sql log around that time?

没有其他信息了，这就是全部内容

Answer 12

这个发生的时间确实是有dbcc checkdb的维护计划，这些消息在dbcc checkdb的前几秒在日志里出现。

这个时间段没有备份任务。

查了下，应该就是DBCC CHECKDB造成的，由于DBCC CHECKDB在执行时要先创建一个数据库快照，所以才会有这些提示。

这些提示并不是针对当前数据库，而是针对快照库，所以当前数据库不会有rolled forward和 rolled back。

如果还有伴有其它error信息，才可能是真的遇到问题了。

参考：

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/46e87f6e-5725-4c46-95b6-b458ab993cd7/transactions-being-rolled-back-and-forward-by-dbcc-checkdb-is-this-ok

http://www.sqlservercentral.com/Forums/Topic617175-149-1.aspx#bm617327

http://support.microsoft.com/kb/926070/zh-cn

Answer 13

谢谢nicofer大侠，我在SQLSERVER2005的错误日志里看到，重启SQLSERVER服务，再打开错误日志

2013-11-27 01:00:16.76 spid18s     Starting up database 'msdb'.
2013-11-27 01:00:16.76 spid19s     Starting up database 'DLGPOS'.
2013-11-27 01:00:16.76 spid20s     Starting up database 'GPOSDB'.
2013-11-27 01:00:16.78 spid19s     CHECKDB for database 'DLGPOS' finished without errors on 2013-06-13 23:14:53.543 (local time). This is an informational message only; no user action is required.
2013-11-27 01:00:16.78 spid20s     CHECKDB for database 'GPOSDB' finished without errors on 2013-09-11 19:19:18.310 (local time). This is an informational message only; no user action is required.
2013-11-27 01:00:16.80 spid5s      Recovery is complete. This is an informational message only. No user action is required.
2013-11-27 01:00:19.25 spid51      Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

SQLSERVER会在启动的时候检查每一个数据库，checkdb

我在查询窗口里手工执行dbcc checkdb但是在错误日志里没有看到rolled forward和rolled back

2013-11-27 01:04:18.46 spid53      DBCC CHECKDB (GPOSDB) executed by JOE\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.

我再新建一个维护计划，发现还是没有出现rolled forward和rolled back

DBCC CHECKDB (GPOSDB) executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.

Answer 14

桦仔，你在做DBCC CHECKDB时，数据库本身没有正在交易的事务，所以在创建快照库时也就不会有rolled forward/back，你可以写一个while，不停的向这个库里INSERT数据或者UPDATE，那么在DBCC CHECKDB时，应该会有rolled forward/back的信息。

Answer 15

好的，我试一下

Answer 16

还是没有

--你可以写一个while，不停的向这个库里INSERT数据或者UPDATE，

USE [GPOSDB]
GO
WHILE 1 = 1

    BEGIN
        INSERT  INTO [dbo].[SystemPara]
                SELECT  '22' ,
                        '22' ,
                        '33'

        UPDATE  [dbo].[SystemPara]
        SET     [ParaValue] = '989'
        WHERE   [RecordNo] = 23
        WAITFOR DELAY '00:00:02'
    END

2013-11-27 09:30:40.15 spid56      DBCC CHECKDB (GPOSDB) executed by JOE\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.

Answer 17

去掉：

WAITFOR DELAY '00:00:02'

Answer 18

看到了

013-11-27 09:50:22.51 spid56      DBCC CHECKDB (GPOSDB) executed by JOE\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2013-11-27 09:50:25.89 spid56      268 transactions rolled forward in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:25.89 spid56      0 transactions rolled back in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:26.10 spid56      DBCC CHECKDB (GPOSDB) executed by JOE\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2013-11-27 09:50:26.87 spid56      638 transactions rolled forward in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:26.87 spid56      0 transactions rolled back in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:27.07 spid56      DBCC CHECKDB (GPOSDB) executed by JOE\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2013-11-27 09:50:27.62 spid56      445 transactions rolled forward in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:27.63 spid56      0 transactions rolled back in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:27.85 spid56      DBCC CHECKDB (GPOSDB) executed by JOE\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2013-11-27 09:50:30.33 spid56      500 transactions rolled forward in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:30.33 spid56      1 transactions rolled back in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:30.57 spid56      DBCC CHECKDB (GPOSDB) executed by JOE\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2013-11-27 09:50:30.86 spid56      459 transactions rolled forward in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:30.87 spid56      1 transactions rolled back in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:31.10 spid56      DBCC CHECKDB (GPOSDB) executed by JOE\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2013-11-27 09:50:31.90 spid56      254 transactions rolled forward in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:31.90 spid56      1 transactions rolled back in database 'GPOSDB' (7). This is an informational message only. No user action is required.
2013-11-27 09:50:32.13 spid56      DBCC CHECKDB (GPOSDB) executed by JOE\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.

Answer 19

这个发生的时间确实是有dbcc checkdb的维护计划，这些消息在dbcc checkdb的前几秒在日志里出现。

这个时间段没有备份任务。

查了下，应该就是DBCC CHECKDB造成的，由于DBCC CHECKDB在执行时要先创建一个数据库快照，所以才会有这些提示。

这些提示并不是针对当前数据库，而是针对快照库，所以当前数据库不会有rolled forward和 rolled back。

如果还有伴有其它error信息，才可能是真的遇到问题了。

参考：

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/46e87f6e-5725-4c46-95b6-b458ab993cd7/transactions-being-rolled-back-and-forward-by-dbcc-checkdb-is-this-ok

http://www.sqlservercentral.com/Forums/Topic617175-149-1.aspx#bm617327

http://support.microsoft.com/kb/926070/zh-cn

非常感谢，感谢nicofer，感谢 桦仔<abbr class="affil"></abbr>                        

Answer 20

对于

onfiguration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

可以看一下这个链接：RECONFIGURE statement causes non-useful entries to be written to the SQL Server log 

应该是运行了reconfigure的原因

有人在SQL2008 R2上测试过，确实有这个冗余信息，而我在SQL2012 SP1上测试没有

应该是SQL2012已经吧这个冗余信息去掉了

Answer 21