Sql-server – CheckDB error 9003 SQL server 2014

data integritydbcc-checkdbsql server

Currently I am restoring SAN snapshots to another server nightly and running weekly checkdbs on that test server. The checkdb is throwing the following errors.

The log scan number (105217:402:0) passed to log scan in database
'db_with_error' is not valid. This error may indicate data corruption
or that the log file (.ldf) does not match the data file (.mdf). If
this error occurred during replication, re-create the publication.
Otherwise, restore from backup if the problem results in a failure
during startup.

I have used the following to resolve the checkdb error. 

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'db_with_error',
@CheckCommands = 'CHECKDB'

EXEC sp_resetstatus 'db_with_error';

ALTER DATABASE db_with_error SET EMERGENCY;

dbcc checkdb ('db_with_error')

ALTER DATABASE db_with_error SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db_with_error SET multi_user WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db_with_error SET online;
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'db_with_error',
@CheckCommands = 'CHECKDB'

Why is the above resolving the error?

Things to note that the test server is running 12.0.2495 and the prod server is running 11.0.5548. Also the prod server is apart of an availability group which is in good standing as I understand it.

Best Answer

Currently I am restoring SAN snapshots to another server nightly and running weekly checkdbs on that test server.

Read this article by Denny Cherry - A SAN Snapshot is not a backup !.

I would suggest you to take native SQL Server backup (preferably with compression - to reduce disk footprint) and then restore it on a test server and run below command to see if you get the same error or not.

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS

Related Question