I run a nightly Integrity Check (set up via Maintenance Plans) on all of my databases. For the last two nights, the final CHECKDBCC has failed… at least according to the SQL Server Agent Job.
Progress: 2019-01-25 03:51:01.15 Source: Check Database Integrity Executing query "USE [CellTestData_QC] ".: 50% complete End Progress
Error: 2019-01-25 05:14:43.79 Code: 0xC002F210 Source: Check Database Integrity Execute SQL Task Description: Executing the query "DBCC CHECKDB(N'CellTestData_QC') WITH NO_INFOMSGS…" failed with the following error: "A severe error occurred on the current command. The results if any should be disca… The package execution fa… The step failed.
But if I check the SQL Server Logs, I get two conflicting messages.
First I get:
01/25/2019 05:00:39,spid105,Unknown,DBCC CHECKDB (CellTestData_QC) WITH no_infomsgs executed by sa terminated abnormally due to error state 6. Elapsed time: 1 hours 9 minutes 38 seconds.
But then I get:
01/25/2019 05:32:06,spid110,Unknown,CHECKDB for database 'CellTestData_QC' finished without errors on 2019-01-24 23:01:46.507 (local time). This is an informational message only; no user action is required.
So what exactly is going on here? Is my process completing? Or erroring out?
For a bit more context…
- We restore [CellTestData_QC] every morning at 5AM (likely why I'm getting the SQL Server Log message of 'terminated abnormally at 5AM).
- Additionally, my backups of [CellTestData] and integrity checks of [CellTestData] and [CellTestData_QC] have been taking longer over the last few days – possibly because of issues with our SAN, which is why we're getting the IntegrityCheck of [CellTestData_QC] beginning to run into the restore of [CellTestData_QC]
- Integrity checks run every night at 11PM
- DB backups (full and partial) run nightly
- Transaction Log backups run every 15min
- Currently working with 8 databases, that range in size from 4MB to 100GB, with a total of 330GB of databases
- I'm running SQL Server Standard 2012 on a 64-bit Windows Server VM (using vSphere) with 64GB RAM, 1TB SAN storage, 175GB NAS storage, 4CPU, 4.9GHz
Any ideas what might be going on here?
Best Answer
There is no mystery, the 2 messagges for
CellTestData_QC
reference 2 different databasesCellTestData_QC
.The first one is your database
CellTestData_QC
on whichDBCC CHECKDB
did not complete because you began arestore
.The second message refers to the restored database
CellTestData
. It's notDBCC CHECKDB
that was running on 25/01, it's just an info that is stored withinCellTestData
, and it reports the last date whenDBCC CHECKDB
completed without errors on this database.Every time the database is opened (goes
online
), the last known gooddbcc checkdb
date is reported ineroor log
. So the second message tells you that DBCC CHECKDB completed successfully on your databaseCellTestData
on2019-01-24 23:01:46.507
. This message appears inerror log
as soon asrestore
is completed.You can see this on every server restart.
SQL Server
opens every database that should goonline
and within every database it reads the date calledlast known good time
and reports it inerrorlog
. This does not mean thatcheckdb
runs on every restart, in fact, the date reported is always earlier than the current date.For example, if you run
checkdb
every Sunday, and the restart occurs on Saturday, you'll see these messages reporting previous Sunday date inerrorlog
as it was the last date whendbcc checkdb
completed successfully.Here CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully? you can read more on
last-known good time
.