We recently had a database go into suspect mode. Unfortunately our backups were not reliable so we ran the following query:

 EXEC sp_resetstatus epo
ALTER DATABASE epo SET EMERGENCY
DBCC checkdb(epo)
ALTER DATABASE epo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (epo, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE epo SET MULTI_USER

So far it has been running for 1 day and the message log keeps looping (around 40,000 times) the same message:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Is this normal. it is a large database (around 200GB) 

http://www.sqlservercentral.com/articles/Administration/unmarksuspect/137/

Hello,

Please run the following query and for the session running check the percent of progress of the process by looking at the percent_complete column.

SELECT * FROM sys.dm_exec_requests

Did you put the database in emergency mode first?

ALTER DATABASE [YourDB] SET EMERGENCY;

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Edited by Alberto MorilloMVP, Moderator 10 hours 5 minutes ago

Hello,



There is evidence of CHECKDB running for 4 days as you can read in the following thread:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/170cf1c4-c4c3-40f5-805d-51769c0b2afa/dbcc-checkdb-running-for-4days?forum=sqldatabaseengine

Try to not allow other user processes or activity on that server that makes use of storage or CPU. The size of the database counts too.

Please try to see if enabling service broker allows the CHECKDB to progress as explained on the following thread:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7dd5d7f2-de6a-4bac-ad78-5fa57bbdbcc1/dbcc-checkdb-goes-suspended-with-wait-type-of-pageiolatchex

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Hello,

Please run the following query and for the session running check the percent of progress of the process by looking at the percent_complete column.

SELECT * FROM sys.dm_exec_requests

Did you put the database in emergency mode first?

ALTER DATABASE [YourDB] SET EMERGENCY;

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Edited by Alberto MorilloMVP, Moderator Friday, July 10, 2015 9:00 PM

Hello,

Please run the following query and for the session running check the percent of progress of the process by looking at the percent_complete column.

SELECT * FROM sys.dm_exec_requests

Did you put the database in emergency mode first?

ALTER DATABASE [YourDB] SET EMERGENCY;

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Edited by Alberto MorilloMVP, Moderator Friday, July 10, 2015 9:00 PM

The database in question is in emergency mode. The result of the query shows 1 session with 0% complete and a start time of today just a few seconds ago. I think that session is something else since the query in question started yesterday. 

• Marked as answer by phystech 9 hours 52 minutes ago