SQL2017 CU21 CHECKDB WITH EXTENDED_LOGICAL_CHECKS

Question

Post reply

SQL2017 CU21 CHECKDB WITH EXTENDED_LOGICAL_CHECKS

Ken McKelvey

SSCoach

Points: 18768
More actions
July 20, 2020 at 11:57 am

#3771285
I have a problem running CHECKDB WITH EXTENDED_LOGICAL_CHECKS using dynamic SQL since upgrading SQL2017 standard edition to CU21.
The problem occurs with msdb on all seven instances and on two out of 63 user databases. The problem does not occur with CU20.
If I run the following static SQL from SSMS:
```
DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS;
```
I just get:
Commands completed successfully.
If I run the following dynamic SQL:
```
DECLARE @SQL nvarchar(MAX)
,@RetVal int;

SET @SQL = 'DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS;'
EXEC @RetVal = sp_executesql @SQL;
IF @RetVal <> 0
PRINT 'Database [msdb] failed to CheckDB with return value of ' + CAST(@RetVal AS varchar(20)) + '.';
```
I get:
Database [msdb] failed to CheckDB with return value of 2706.
If I remove NO_INFOMSGS, in the dynamic SQL, I get all the information with the following at the end:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'msdb'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
followed by:
Database [msdb] failed to CheckDB with return value of 2706.
I get similar results using Ola Hallengren's DatabaseIntegrityCheck:
```
EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'msdb', @ExtendedLogicalChecks = 'N';
```
gives an outcome of Succeeded while:
```
EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'msdb', @ExtendedLogicalChecks = 'Y';
```
gives an outcome of failed.
Does this just look like a bug in CU21? If it is a bug, what is the best way to report it?
- This topic was modified 3 years, 9 months ago by Ken McKelvey.
- This topic was modified 3 years, 9 months ago by Ken McKelvey.
April 20, 2021 at 3:34 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 2

Is that the only output? Isn't there something from DBCC besides what you are returning?

Ken McKelvey SSCoach Points: 18768 More actions · Answer 3

There are no error messages. If I just run:

DECLARE @SQL nvarchar(MAX)
,@RetVal int;

SET @SQL = 'DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS;'
EXEC @RetVal = sp_executesql @SQL;

then I just get:- Commands completed successfully.

If I remove NO_INFOMSGS there are no allocation or consistency errors. Also there are no problems if I remove EXTENDED_LOGICAL_CHECKS.

I only use EXTENDED_LOGICAL_CHECKS as part of my weekly routine as I have some databases which I do not know a great deal about.

I have just decided to ignore the 2706 return values as DATA_PURITY is fine, there are no error messages and it seems unlikely that installing CU21 alone would corrupt msdb.

When I tried to report this to Microsoft it looked as though money is required for support. As the university requires a few committees to pay for anything I gave up.

Thanks for your help.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 4

I'll post a note to Microsoft and see if I can get a response.

Ken McKelvey SSCoach Points: 18768 More actions · Answer 5

Thanks.

Incidentally I just tried running the following:

DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS;
PRINT @@ERROR;

which gave a result of 2706.

ie When EXTEND_LOGICAL_CHECKS are included in CHECKDB then error 2706 is returned even thought no errors are reported.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 6

Thanks. Posted in the MVP list. Hopefully someone responds.

Ken McKelvey SSCoach Points: 18768 More actions · Answer 7

If anyone else is affected by this, it still occurs with SQL2017 CU22 on a test VM.

jdaigle Grasshopper Points: 13 More actions · Answer 8

I'm having this same problem on CU21.

But only on our production server. All of our test servers are running the same CU21 and are not having this issue. For us it is the MSDB database, and one of our user databases only.

This reply was modified 3 years, 7 months ago by jdaigle.

jdaigle Grasshopper Points: 13 More actions · Answer 9

We are also still having the issue, with CU 22 and it is also only happening on our production server as well.

Has there been any progress with Microsoft on this?

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 10

I bumped my post. I had one response where someone was using this with AGs and hadn't had this error.

Ken McKelvey SSCoach Points: 18768 More actions · Answer 11

I am still getting the error mainly on msdb. As I do not think anything is actually wrong I am ignoring it.

We use standard edition on Windows 2016 with VMWare.

Albert Hamidullin Newbie Points: 1 More actions · Answer 12

SQL SERVER 2019 CU9

dbcc checkdb(N'msdb') with all_errormsgs, extended_logical_checks;

I caught the error, it appears twice with this message "Table 'fn_available_backups' does not exist."

Two objects - two messages

It turns out that dbcc does not always correctly understand whether an object is a table or not.

I caught the same errors on other databases, where table types were the culprit

Dex Grasshopper Points: 11 More actions · Answer 13

Same for SQL Server 2019 CU13. Ola's script still reports the error for msdb:

screenshot_20211113-2

Messages from the script:

Date and time: 2021-11-13 11:15:49
Database: [msdb]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: SIMPLE
Is accessible: Yes

Date and time: 2021-11-13 11:15:49
Database context: [master]
Command: DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS
Outcome: Failed
Duration: 00:00:01
Date and time: 2021-11-13 11:15:50

However, I could not find any noticeable output in the results of DBCC CHECKDB:

DBCC CHECKDB ([msdb]) WITH  ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS;

Regards,

Dex

This reply was modified 2 years, 5 months ago by Dex. Reason: addendum
This reply was modified 2 years, 5 months ago by Dex. Reason: addendum
This reply was modified 2 years, 5 months ago by Dex. Reason: Update to CU13, new testing