CheckDB error 9003 SQL server 2014

Question

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.

Answer 1

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

• b_murray14: Ran a native backup/restore/checkdb with no errors.
• Kin Shah: That's your answer. Use native backups and stop relying on SAN snapshots for database backup substitute.
• swasheck: to piggyback on this, clones and snapshots are great if you're wanting to quickly push a copy to a preprod environment - not a DR situation. additionally, if you have any sort of complexity in how your data/log files are arranged, snapshots are a true headache as they tend to snap at the volume level, and not of a database.
• Kin Shah: @swasheck absolutely agree with you. Valid points .. Thanks !