corruption error

Question

Hi All,

Today we are seeing 824 errors on one of the database.
When I ran checkdb I am seeing below errors.
Note: The database hosted on a 3rd part storage appliance called Actifio.

Use master
Go
DBCC CHECKDB (CDP) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

Msg 1823, Level 16, State 6, Line 3A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 7, Line 3A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 8, Line 3A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 3The database snapshot for online checks could not be created.
Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 3The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 3Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked.
See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 824, Level 24, State 2, Line 3SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 3:193245696; actual 0:0). It occurred during a read of page (3:193245696) in database ID 21 at offset 0x00017096400000 in file 'G:\SQLDUMP\DATA\CDP\Actifio\CDP_dat.mdf_MSSQL_DBCC21'. 
Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Questions

1. Why SQL was not able to generate the db snapshot? How to make sql to generate snapshot?
2. Seeing some entries in the msdb..suspect_pages? Now is there a way to know what objects/tables are residing in those list of corrupted pages?

Regards,
Sam

Answer 1

Hi @Sam ,

Msg 1823, Level 16, State 6, Line 3A database snapshot cannot be created because it failed to start.

This issue may be due to insufficient disk space.
DBCC commands use internal read-only database snapshots. These database snapshots are created on the same drive where the corresponding database data files are located. Database that do not have sufficient disk space can't run the DBCC CheckDB command. Please make sure that there is sufficient disk space available on the drive that hosts the data file.

Seeing some entries in the msdb..suspect_pages? Now is there a way to know what objects/tables are residing in those list of corrupted pages?

You can run SELECT * FROM msdb.dbo.suspect_pages to find out the corrupt pages including database_id, file_id, page_id. Then use them to DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]) as Erland mentioned.
Please refer to Finding a table name from a page ID for more information.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To find what the page is about, you can run as in this example:

DBCC TRACEON (3604)
DBCC PAGE(5,1,2)
DBCC TRACEOFF (3604)

5 = Database_id, 1 = file_id, 2 page_number. If the page is related to an object, there is m_objid on top as I recall.

Given that error message, I would start looking at here the backups are located...

Answer 3

The 824 error message usually indicates that there's a problem with underlying storage system or the hardware or a driver that is in the path of the I/O request. You can encounter this error when there are inconsistencies in the file system or if the database file is damaged.

Greetings,
Patrick

Answer 4

During DBCC CHECKDB, If the database being checked has a heavy update workload, more and more pages are pushed into the database snapshot, causing it to grow. In a situation where the volumes hosting the database don’t have much space, this can mean the hidden database snapshot runs out of space and DBCC CHECKDB stops with an error

Refer these:

http://www.sqlserverbox.com/dbcc-checkdb-a-database-snapshot-cannot-be-created-because-it-failed-to-start.html

http://sqlism.blogspot.com/2014/10/a-database-snapshot-cannot-be-created.html

https://www.stellarinfo.com/blog/troubleshoot-sql-database-error-824/