Database suspect mode
One of my database went into suspect mode, it is not a critical one and small sized database. There was no backup 🙂
How to recovery the suspect database?
If you don’t have any backups, then the only way to get into the database is to use EMERGENCY mode. This lets you into the database but you need to be aware that recovery has not completed so the contents of the database are transactionally (and possibly structurally) inconsistent. I’m going to choose to repair the database using EMERGENCY-mode repair. See CHECKDB From Every Angle: EMERGENCY mode repair – the very, very last resort for a detailed description of this tool.
Note that you have to put the database into EMERGENCY and SINGLE_USER modes to do this.
Alter database [DB_name] set emergency DBCC checkdb ('DB_name') with all_errormsgs, no_infomsgs ALTER DATABASE [DB_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('DB_name', REPAIR_ALLOW_DATA_LOSS) with all_errormsgs, no_infomsgs ALTER DATABASE [DB_name] SET MULTI_USER
Error from log:
Recovery of database ‘DB_name’ (16) is 97% complete (approximately 9 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
107 transactions rolled forward in database ‘DB_name’ (16:0). This is an informational message only. No user action is required.
Error: 608, Severity: 16, State: 1.
No catalog entry found for partition ID 72057594106675200 in database 16. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
**Dump thread – spid = 0, EC = 0x0000000D1DD55F10
***Stack Dump being sent to D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0121.txt
* BEGIN STACK DUMP:
* 05/15/20 13:55:14 spid 59
* Location: page.cpp:4366
* Expression: spaceContig < (MAXDATAROW + ONEROWPAGEWASTE)
* SPID: 59
* Process ID: 4936
* Input Buffer 116 bytes –
* DBCC CheckDB (‘DB_name’, REPAIR_ALLOW_DATA_LOSS)
After repair data loss: I could not post all entries, it is a big.
Expression: spaceContig < (MAXDATAROW + ONEROWPAGEWASTE)
Process ID: 4936
DBCC results for ‘DB_name’.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
There are 0 rows in 0 pages for object “sys.queue_messages_1977058079”.
DBCC results for ‘sys.queue_messages_2009058193’.
There are 0 rows in 0 pages for object “sys.queue_messages_2009058193”.
DBCC results for ‘sysdiagrams’.
There are 1 rows in 1 pages for object “sysdiagrams”.
DBCC results for ‘sys.queue_messages_2041058307’.
There are 0 rows in 0 pages for object “sys.queue_messages_2041058307”.
DBCC results for ‘sys.filestream_tombstone_2073058421’.
There are 0 rows in 0 pages for object “sys.filestream_tombstone_2073058421”.
DBCC results for ‘WHOISACTIVE.CAPTURED_CACHE’.
There are 0 rows in 0 pages for object “WHOISACTIVE.CAPTURED_CACHE”.
DBCC results for ‘sys.syscommittab’.
There are 0 rows in 0 pages for object “sys.syscommittab”.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘DB_name’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.