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?
Paul Randal’s post https://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-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.
Location: page.cpp:4366
Expression: spaceContig < (MAXDATAROW + ONEROWPAGEWASTE)
SPID: 59
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.
