olahallengren
DatabaseIntegrityCheck script on msdb failing - dbcc checkdb does not
Description of the issue DatabaseIntegrityCheck script outcome failed for msdb database and some user databases when @ExtendedLogicalChecks = 'Y'. DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS succeeds.
SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 (X64) Nov 3 2021 19:19:51 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64) May 27 2021 17:34:14 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
Version of the script Version: 2020-12-31 18:58:56
What command are you executing? EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'SYSTEM_DATABASES', @CheckCommands = 'CHECKDB', @ExtendedLogicalChecks = 'Y', @LogToTable = 'Y'
What output are you getting? Date and time: 2021-12-20 09:59:44 Server: SERVER_NAME Version: 15.0.4188.2 Edition: Enterprise Edition: Core-based Licensing (64-bit) Platform: Windows Procedure: [DBATools].[dbo].[DatabaseIntegrityCheck] Parameters: @Databases = 'SYSTEM_DATABASES', @CheckCommands = 'CHECKDB', @PhysicalOnly = 'N', @DataPurity = 'N', @NoIndex = 'N', @ExtendedLogicalChecks = 'Y', @TabLock = 'N', @FileGroups = NULL, @Objects = NULL, @MaxDOP = NULL, @AvailabilityGroups = NULL, @AvailabilityGroupReplicas = 'ALL', @Updateability = 'ALL', @TimeLimit = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'Y', @Execute = 'Y' Version: 2020-12-31 18:58:56 Source: https://ola.hallengren.com
Date and time: 2021-12-20 09:59:44 Database: [master] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: SIMPLE Is accessible: Yes
Date and time: 2021-12-20 09:59:44 Database context: [master] Command: DBCC CHECKDB ([master]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS Outcome: Succeeded Duration: 00:00:01 Date and time: 2021-12-20 09:59:45
Date and time: 2021-12-20 09:59:45 Database: [model] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: FULL Is accessible: Yes
Date and time: 2021-12-20 09:59:45 Database context: [master] Command: DBCC CHECKDB ([model]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS Outcome: Succeeded Duration: 00:00:01 Date and time: 2021-12-20 09:59:46
Date and time: 2021-12-20 09:59:46 Database: [msdb] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: SIMPLE Is accessible: Yes
Date and time: 2021-12-20 09:59:46 Database context: [master] Command: DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS Outcome: Failed Duration: 00:00:04 Date and time: 2021-12-20 09:59:50
Date and time: 2021-12-20 09:59:50
Completion time: 2021-12-20T09:59:50.9454773-06:00
This issue should have been fixed by VSTS bug 14173307https://support.microsoft.com/en-us/topic/kb5005679-cumulative-update-13-for-sql-server-2019-5c1be850-460a-4be4-a569-fe11f0adc535#bkmk_14173307 in SQL Server 2019 CU13 [https://support.microsoft.com/en-us/topic/kb5005679-cumulative-update-13-for-sql-server-2019-5c1be850-460a-4be4-a569-fe11f0adc535]
From: e11ameno @.> Sent: Monday, December 20, 2021 4:01:33 PM To: olahallengren/sql-server-maintenance-solution @.> Cc: Subscribed @.***> Subject: [olahallengren/sql-server-maintenance-solution] DatabaseIntegrityCheck script on msdb failing - dbcc checkdb does not (Issue #604)
Description of the issue DatabaseIntegrityCheck script outcome failed for msdb database and some user databases when @ExtendedLogicalChecks = 'Y'. DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS succeeds.
SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 (X64) Nov 3 2021 19:19:51 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64) May 27 2021 17:34:14 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)
Version of the script Version: 2020-12-31 18:58:56
What command are you executing? EXECUTE [dbo].[DatabaseIntegrityCheck] @databaseshttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdatabases&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952090027%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=3SKSqi4eWXJzdWSaGNvKS56UExg%2BiLlTY%2B11IN39jqQ%3D&reserved=0 = 'SYSTEM_DATABASES', @CheckCommands = 'CHECKDB', @ExtendedLogicalChecks = 'Y', @LogToTable = 'Y'
What output are you getting? Date and time: 2021-12-20 09:59:44 Server: SERVER_NAME Version: 15.0.4188.2 Edition: Enterprise Edition: Core-based Licensing (64-bit) Platform: Windows Procedure: [DBATools].[dbo].[DatabaseIntegrityCheck] Parameters: @databaseshttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdatabases&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952090027%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=3SKSqi4eWXJzdWSaGNvKS56UExg%2BiLlTY%2B11IN39jqQ%3D&reserved=0 = 'SYSTEM_DATABASES', @CheckCommands = 'CHECKDB', @PhysicalOnly = 'N', @DataPurity = 'N', @NoIndexhttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FNoIndex&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952090027%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=JeUgcBWFJkip%2BNlJFlcqBdL7VkUBMCnNfbwC%2B0XqCu4%3D&reserved=0 = 'N', @ExtendedLogicalChecks = 'Y', @tablockhttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Ftablock&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952246266%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=52OYrD04frtVqt3JjOsUwE0gUyujwfBB8ubclUOvrbk%3D&reserved=0 = 'N', @FileGroups = NULL, @objectshttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fobjects&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952246266%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=IjI2i0M047TFakJVH1aKKNSYUZF4AWxGBCl226Pj93Q%3D&reserved=0 = NULL, @MaxDOP = NULL, @AvailabilityGroups = NULL, @AvailabilityGroupReplicas = 'ALL', @Updateability = 'ALL', @Timelimithttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FTimelimit&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952246266%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=5yoVD53pYT5CE9iMt%2BUQx8smeNZpEf%2Fq9acc937MAhQ%3D&reserved=0 = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'Y', @executehttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fexecute&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952246266%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=d%2FkxQtiyBP%2FW7Q%2BvumWXx4HG5oXrrtt9uhPeC%2BHGYNg%3D&reserved=0 = 'Y' Version: 2020-12-31 18:58:56 Source: https://ola.hallengren.comhttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fola.hallengren.com%2F&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952246266%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=okYlXXRX5c263gqvcUnTc7T8vYLL6hXzXUJPEr4xcVo%3D&reserved=0
Date and time: 2021-12-20 09:59:44 Database: [master] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: SIMPLE Is accessible: Yes
Date and time: 2021-12-20 09:59:44 Database context: [master] Command: DBCC CHECKDB ([master]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS Outcome: Succeeded Duration: 00:00:01 Date and time: 2021-12-20 09:59:45
Date and time: 2021-12-20 09:59:45 Database: [model] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: FULL Is accessible: Yes
Date and time: 2021-12-20 09:59:45 Database context: [master] Command: DBCC CHECKDB ([model]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS Outcome: Succeeded Duration: 00:00:01 Date and time: 2021-12-20 09:59:46
Date and time: 2021-12-20 09:59:46 Database: [msdb] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: SIMPLE Is accessible: Yes
Date and time: 2021-12-20 09:59:46 Database context: [master] Command: DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS Outcome: Failed Duration: 00:00:04 Date and time: 2021-12-20 09:59:50
Date and time: 2021-12-20 09:59:50
Completion time: 2021-12-20T09:59:50.9454773-06:00
— Reply to this email directly, view it on GitHubhttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Folahallengren%2Fsql-server-maintenance-solution%2Fissues%2F604&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952246266%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=BPMY8dZnFoCUucjmoxn8jb7L3iTdc9ObFKE3NK%2FTo%2FY%3D&reserved=0, or unsubscribehttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAE3Y4CQEZ2VA2NORBKSPVI3UR5HN3ANCNFSM5KOBOVHA&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952246266%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=EjqFYXyTQ0mibpE31Ijz271x3bOf8l0Ge%2B0AMpWgpXs%3D&reserved=0. Triage notifications on the go with GitHub Mobile for iOShttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapps.apple.com%2Fapp%2Fapple-store%2Fid1477376905%3Fct%3Dnotification-email%26mt%3D8%26pt%3D524675&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952246266%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=WgNR%2F4k5HRE579kTMO7g559a0p43364QYv3GeljGY6I%3D&reserved=0 or Androidhttps://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fplay.google.com%2Fstore%2Fapps%2Fdetails%3Fid%3Dcom.github.android%26referrer%3Dutm_campaign%253Dnotification-email%2526utm_medium%253Demail%2526utm_source%253Dgithub&data=04%7C01%7C%7C8d1dbf001ba44179ea5308d9c3d1fee7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637756128952246266%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=byS6Ni5uiLGfXjQ3KE6HnnhcAm4bjz4asFCMKku8vPM%3D&reserved=0. You are receiving this because you are subscribed to this thread.Message ID: @.***>
VSTS 14173307 - "When statistics are corrupted, a very generic message may be generated without information about the statistics corruption. Additionally, CHECKDB may not report corrupted statistics. This improvement can detect corrupted statistics by using extended_logical_checks as part of DBCC CHECKDB"
It sounds like they have added additional corruption detection. So I should get a failed message when I run the stored procedure and DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS. But I do not.
The issue I have found is that when running the DatabaseIntegrityCheck stored procedure, I get a "failed" message.
EXECUTE [dbo].[DatabaseIntegrityCheck] @databases = 'SYSTEM_DATABASES', @CheckCommands = 'CHECKDB', @ExtendedLogicalChecks = 'Y', @LogToTable = 'Y'
When running the CHECKDB command directly, everything succeeds: DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS
They are supposed to be running the same commands. Also, one of the servers I have this issue on has CU14 installed.
Do you have a server where you can reproduce this issue consistently?
It looks like I can repro in Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 server.
The method of execution of the CHECKDB impacts the outcome. If you just run the DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS in SSMS then there are no issues, but if you run it like the below then it generates an error:
DECLARE @SQL nvarchar(MAX)
,@RetVal int;
SET @SQL = 'DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, 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)) + '.';
GO
The result is:
Database [msdb] failed to CheckDB with return value of 2706.
The error 2706 is Table '%.*ls' does not exist.
@e11ameno - if you raise a support case with Microsoft this can be investigated.
Thank you @pbarryuk. This is very good to know.
@e11ameno, would it be possible for you to open a support case?
@e11ameno What did MS say? I'm having this same issue with serveral 13.0.6300.2 servers.
When I got to the part of the support ticket where they required I buy a support plan, I closed out. I'm not going to pay them to tell them about their bug.
We have this issue on our SQL Server 2017 servers, but not the older SQL Server 2012 ones. There appears to be no difference in the output between running the command (except you don't get to see the return code) and running it with the sp_executesql SP where you can get the return code. Opened MS support case #2204020030000528.
declare @rc int; execute @rc = sp_executesql @statement = N'DBCC CHECKDB ([msdb]) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS;'; if @rc <> 0 print 'CHECKDB ([msdb]) failed with return code ' + CAST(@rc as nvarchar(9)) + '.'; go
Based on responses in the MS case:
- There is a workaround to enable Trace Flag 2452 that will skip the check for corrupt statistics as part of the DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS.
- A fix is estimated to be released in SQL Server 2017 Cumulative Update 30 (CU30), although this is subject to change as development progresses.
On SQL 2019 CU16, OLA's script:
EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'msdb', @CheckCommands = 'CHECKDB', @ExtendedLogicalChecks ='Y',@LogToTable = 'Y'
returns the following with error number 2706:
Command: DBCC CHECKDB ([msdb]) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS Outcome: Failed
but running the actual command returns:
Commands completed successfully. Completion time: 2022-06-27T14:12:01.9580414-04:00
I'm getting this on user databases, too. I updated all the stats, then I dropped all the stats, then I dropped all of the indexes, including clustered indexes, then I dropped all those stats. That trace flag listed causes OLA's script to pass and not log the error number, but are we sure the trace flag does what we think it does? Why is OLAs script behaving differently than the command itself? I looked through the code but it wasn't apparent to me.
I’m very sure the TF does as described - it skips the stats check code that is getting incorrectly called for certain object types that it should not, such as TVFs.
Regarding the difference between Ola’s script and running the command it is due to the way the script calls the check. If you see further up I’ve put an example with msdb that will also fail (without the TF) that is not using Ola’s script.
Yes, I did see and test your example. So the trace flag skips all stats checks in order to bypass the TVFs. Ola's script fails because of the way it's returning the error messages. Got it. Thanks!
The fix has been released for SQL Server 2017 in CU30 - https://support.microsoft.com/en-us/topic/kb5013756-cumulative-update-30-for-sql-server-2017-274943fa-8dde-4844-90ed-d3b587fa0c7c#bkmk_14673411
This has now also been fixed in SQL Server 2019 - https://support.microsoft.com/en-us/topic/kb5016394-cumulative-update-17-for-sql-server-2019-3033f654-b09d-41aa-8e49-e9d0c353c5f7#bkmk_14673410
I would suggest this bug can be closed @olahallengren