Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

DatabaseIntegrityCheck with 'USER_DATABASES' failed with contained master databases #878

Open
wellmetus opened this issue Jan 23, 2025 · 8 comments
Open

DatabaseIntegrityCheck with 'USER_DATABASES' failed with contained master databases #878

wellmetus opened this issue Jan 23, 2025 · 8 comments
Labels
Bug Report DatabaseIntegrityCheck

Comments

@wellmetus
Copy link

Description of the issue
Below tries to run DBCC check on contained master databases as well and it fails

EXECUTE [dbo].[DatabaseIntegrityCheck]
@databases = 'USER_DATABASES',
@LogToTable = 'Y'

SQL Server version and edition
Execute SELECT @@VERSION
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2022 Datacenter 10.0 (Build 20348: ) (Hypervisor)

Version of the script
Check the header of the stored procedure

/*

SQL Server Maintenance Solution - SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, and SQL Server 2022

Backup: https://ola.hallengren.com/sql-server-backup.html
Integrity Check: https://ola.hallengren.com/sql-server-integrity-check.html
Index and Statistics Maintenance: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

License: https://ola.hallengren.com/license.html

GitHub: https://github.com/olahallengren/sql-server-maintenance-solution

Version: 2025-01-18 21:00:27

You can contact me by e-mail at [email protected].

Ola Hallengren
https://ola.hallengren.com

*/

What command are you executing?

EXECUTE [dbo].[DatabaseIntegrityCheck]
@databases = 'USER_DATABASES',
@LogToTable = 'Y'

What output are you getting?

EXECUTE [dbo].[DatabaseIntegrityCheck] @databases = 'AG22_master', @LogToTable = 'Y';

Date and time: 2025-01-23 09:43:25
Server: SQL22A
Version: 16.0.1000.6
Edition: Enterprise Edition: Core-based Licensing (64-bit)
Platform: Windows
Procedure: [master].[dbo].[DatabaseIntegrityCheck]
Parameters: @databases = 'AG22_master', @CheckCommands = 'CHECKDB', @PhysicalOnly = 'N', @DataPurity = 'N', @NoIndex = 'N', @ExtendedLogicalChecks = 'N', @NoInformationalMessages = 'N', @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: 2025-01-18 21:00:27
Source: https://ola.hallengren.com

Date and time: 2025-01-23 09:43:25
Database: [AG22_master]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Availability group: AG22
Availability group role: PRIMARY

Date and time: 2025-01-23 09:43:25
Database context: [master]
Command: DBCC CHECKDB ([AG22_master]) WITH ALL_ERRORMSGS
DBCC results for 'AG22_master'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 1625 rows in 21 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 203 rows in 3 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysclones'.
There are 0 rows in 0 pages for object "sys.sysclones".
DBCC results for 'sys.sysallocunits'.
There are 243 rows in 4 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.sysseobjvalues'.
There are 0 rows in 0 pages for object "sys.sysseobjvalues".
DBCC results for 'sys.sysmatrixages'.
There are 0 rows in 0 pages for object "sys.sysmatrixages".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysdbfrag'.
There are 5 rows in 1 pages for object "sys.sysdbfrag".
DBCC results for 'sys.sysfgfrag'.
There are 0 rows in 0 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.syspru'.
There are 5 rows in 1 pages for object "sys.syspru".
DBCC results for 'sys.sysbrickfiles'.
There are 10 rows in 2 pages for object "sys.sysbrickfiles".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 16 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 5 rows in 1 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 2664 rows in 20 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 2780 rows in 40 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscsrowgroups'.
There are 0 rows in 0 pages for object "sys.syscsrowgroups".
DBCC results for 'sys.sysextsources'.
There are 0 rows in 0 pages for object "sys.sysextsources".
DBCC results for 'sys.sysexttables'.
There are 0 rows in 0 pages for object "sys.sysexttables".
DBCC results for 'sys.sysextfileformats'.
There are 0 rows in 0 pages for object "sys.sysextfileformats".
DBCC results for 'sys.syslogshippers'.
There are 0 rows in 0 pages for object "sys.syslogshippers".
DBCC results for 'sys.sysmultiobjvalues'.
There are 0 rows in 0 pages for object "sys.sysmultiobjvalues".
DBCC results for 'sys.syscolpars'.
There are 1368 rows in 27 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 34 rows in 1 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 1 rows in 1 pages for object "sys.sysxsrvs".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.sysusermsgs'.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for 'sys.syscerts'.
There are 2 rows in 3 pages for object "sys.syscerts".
DBCC results for 'sys.sysrmtlgns'.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for 'sys.syslnklgns'.
There are 1 rows in 1 pages for object "sys.syslnklgns".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 274 rows in 5 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 498 rows in 4 pages for object "sys.sysiscols".
DBCC results for 'sys.sysendpts'.
There are 5 rows in 1 pages for object "sys.sysendpts".
DBCC results for 'sys.syswebmethods'.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 721 rows in 40 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysmatrixconfig'.
There are 0 rows in 0 pages for object "sys.sysmatrixconfig".
DBCC results for 'sys.syscscolsegments'.
There are 0 rows in 0 pages for object "sys.syscscolsegments".
DBCC results for 'sys.syscsdictionaries'.
There are 0 rows in 0 pages for object "sys.syscsdictionaries".
DBCC results for 'sys.sysclsobjs'.
There are 34 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysmatrixbricks'.
There are 0 rows in 0 pages for object "sys.sysmatrixbricks".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 231 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 118 rows in 2 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysmatrixmanagers'.
There are 0 rows in 0 pages for object "sys.sysmatrixmanagers".
DBCC results for 'sys.sysguidrefs'.
There are 1 rows in 1 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysfoqueues'.
There are 0 rows in 0 pages for object "sys.sysfoqueues".
DBCC results for 'sys.syschildinsts'.
There are 0 rows in 0 pages for object "sys.syschildinsts".
DBCC results for 'sys.sysextendedrecoveryforks'.
There are 0 rows in 0 pages for object "sys.sysextendedrecoveryforks".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysmatrixageforget'.
There are 0 rows in 0 pages for object "sys.sysmatrixageforget".
DBCC results for 'sys.sysftsemanticsdb'.
There are 0 rows in 0 pages for object "sys.sysftsemanticsdb".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysftproperties'.
There are 0 rows in 0 pages for object "sys.sysftproperties".
DBCC results for 'sys.sysxmitbody'.
There are 0 rows in 0 pages for object "sys.sysxmitbody".
DBCC results for 'sys.sysfos'.
There are 0 rows in 0 pages for object "sys.sysfos".
DBCC results for 'sys.sysqnames'.
There are 109 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 100 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 19 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 9 rows in 1 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 1 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 0 rows in 0 pages for object "sys.syssoftobjrefs".
Msg 8906, Level 16, State 1, Line 1
Page (1:10) in database ID 16375 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object ID 99)' (object ID 99).
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object "spt_fallback_db".
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object "spt_fallback_dev".
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object "spt_fallback_usg".
DBCC results for 'sys.ledger_tables_history_internal'.
There are 0 rows in 0 pages for object "sys.ledger_tables_history_internal".
DBCC results for 'sys.ledger_tables_history_internal_history'.
There are 0 rows in 0 pages for object "sys.ledger_tables_history_internal_history".
DBCC results for 'sys.ledger_columns_history_internal'.
There are 0 rows in 0 pages for object "sys.ledger_columns_history_internal".
DBCC results for 'sys.ledger_columns_history_internal_history'.
There are 0 rows in 0 pages for object "sys.ledger_columns_history_internal_history".
DBCC results for 'sys.db_ledger_digest_locations'.
There are 0 rows in 0 pages for object "sys.db_ledger_digest_locations".
DBCC results for 'sys.plan_persist_replicas'.
There are 0 rows in 0 pages for object "sys.plan_persist_replicas".
DBCC results for 'sys.plan_persist_plan_forcing_locations'.
There are 0 rows in 0 pages for object "sys.plan_persist_plan_forcing_locations".
DBCC results for 'sys.plan_persist_runtime_stats_v2'.
There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats_v2".
DBCC results for 'sys.plan_persist_wait_stats_v2'.
There are 0 rows in 0 pages for object "sys.plan_persist_wait_stats_v2".
DBCC results for 'sys.queue_messages_1003150619'.
There are 0 rows in 0 pages for object "sys.queue_messages_1003150619".
DBCC results for 'sys.queue_messages_1035150733'.
There are 0 rows in 0 pages for object "sys.queue_messages_1035150733".
DBCC results for 'sys.queue_messages_1067150847'.
There are 0 rows in 0 pages for object "sys.queue_messages_1067150847".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'sys.sqlagent_jobs'.
There are 0 rows in 0 pages for object "sys.sqlagent_jobs".
DBCC results for 'sys.sqlagent_jobsteps'.
There are 0 rows in 0 pages for object "sys.sqlagent_jobsteps".
DBCC results for 'sys.sqlagent_job_history'.
There are 0 rows in 0 pages for object "sys.sqlagent_job_history".
DBCC results for 'sys.sqlagent_jobsteps_logs'.
There are 0 rows in 0 pages for object "sys.sqlagent_jobsteps_logs".
DBCC results for 'sys.plan_persist_query_text'.
There are 0 rows in 0 pages for object "sys.plan_persist_query_text".
DBCC results for 'sys.plan_persist_query'.
There are 0 rows in 0 pages for object "sys.plan_persist_query".
DBCC results for 'sys.plan_persist_plan'.
There are 0 rows in 0 pages for object "sys.plan_persist_plan".
DBCC results for 'sys.plan_persist_runtime_stats'.
There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats".
DBCC results for 'sys.plan_persist_runtime_stats_interval'.
There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats_interval".
DBCC results for 'sys.plan_persist_context_settings'.
There are 0 rows in 0 pages for object "sys.plan_persist_context_settings".
DBCC results for 'sys.plan_persist_query_hints'.
There are 0 rows in 0 pages for object "sys.plan_persist_query_hints".
DBCC results for 'sys.plan_persist_query_template_parameterization'.
There are 0 rows in 0 pages for object "sys.plan_persist_query_template_parameterization".
DBCC results for 'sys.plan_persist_wait_stats'.
There are 0 rows in 0 pages for object "sys.plan_persist_wait_stats".
DBCC results for 'sys.persistent_version_store'.
There are 0 rows in 0 pages for object "sys.persistent_version_store".
DBCC results for 'sys.persistent_version_store_long_term'.
There are 0 rows in 0 pages for object "sys.persistent_version_store_long_term".
DBCC results for 'sys._trusted_assemblies'.
There are 0 rows in 0 pages for object "sys._trusted_assemblies".
DBCC results for 'sys.tbl_server_resource_stats'.
There are 0 rows in 0 pages for object "sys.tbl_server_resource_stats".
DBCC results for 'sys.external_library_setup_failures'.
There are 0 rows in 0 pages for object "sys.external_library_setup_failures".
DBCC results for 'sys.external_libraries_installed_table'.
There are 0 rows in 0 pages for object "sys.external_libraries_installed_table".
DBCC results for 'sys.polaris_file_statistics'.
There are 0 rows in 0 pages for object "sys.polaris_file_statistics".
DBCC results for 'sys.db_ledger_blocks'.
There are 0 rows in 0 pages for object "sys.db_ledger_blocks".
DBCC results for 'sys.db_ledger_transactions'.
There are 0 rows in 0 pages for object "sys.db_ledger_transactions".
DBCC results for 'sys.polaris_executed_requests_history'.
There are 0 rows in 0 pages for object "sys.polaris_executed_requests_history".
DBCC results for 'sys.polaris_executed_requests_text'.
There are 0 rows in 0 pages for object "sys.polaris_executed_requests_text".
DBCC results for 'sys.backup_metadata_store'.
There are 0 rows in 0 pages for object "sys.backup_metadata_store".
DBCC results for 'sys.plan_persist_plan_feedback'.
There are 0 rows in 0 pages for object "sys.plan_persist_plan_feedback".
DBCC results for 'sys.plan_persist_query_variant'.
There are 0 rows in 0 pages for object "sys.plan_persist_query_variant".
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object "spt_monitor".
DBCC results for 'MSreplication_options'.
There are 3 rows in 1 pages for object "MSreplication_options".
CHECKDB found 1 allocation errors and 0 consistency errors in database 'AG22_master'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AG22_master).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Outcome: Failed
Duration: 00:00:01
Date and time: 2025-01-23 09:43:26

Date and time: 2025-01-23 09:43:26

Completion time: 2025-01-23T09:43:26.7596990-08:00
@olahallengren
Copy link
Owner

I think that there are two issues here:

Should the contained availability group system databases be included in USER_DATABASES or SYSTEM_DATABASES? Should they be included when you select an availability group? I will look into this.

There seems to be a corruption issue in the database:
CHECKDB found 1 allocation errors and 0 consistency errors in database 'AG22_master'.
That's why the stored procedure and the job reports failure.

@wellmetus
Copy link
Author

I suspect that DatabaseIntegrityCheck needs to run inside the "contained" availability group, ie. via "contained AG listener"?

@olahallengren
Copy link
Owner

Do you have other contained availability groups (on the same server or different servers), or just this one?

@wellmetus
Copy link
Author

wellmetus commented Jan 25, 2025 via email •
edited by olahallengren
Loading

@olahallengren
Copy link
Owner

olahallengren commented Jan 25, 2025 •
edited
Loading

Is it failing on that master database as well?

@olahallengren
Copy link
Owner

@wellmetus, could you share some information about how it is behaving on the other contained availability group?

@wellmetus
Copy link
Author

wellmetus commented Feb 10, 2025 via email

@olahallengren
Copy link
Owner

Thank you, @wellmetus.

Same error on both databases. This looks like a bug in SQL Server. Would it be possible for you to open a support case with Microsoft?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug Report DatabaseIntegrityCheck
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@olahallengren @wellmetus