Some of the more obscure changes in SQL Server 2022, Part 1

By now you've seen that the first public preview of SQL Server 2022 is available (resources here).

You can get the marketing blitz from just about anywhere, and the What's New documentation for the bigger hitters from the technical side.

But what about the changes that aren't on the highlight reel at Build and aren't getting all the attention from the media blitz? I'm a details person, so I get a lot of insight looking around at the little, non-headline-generating things that have changed. I've shown before how to sneak a peek under the hood, and I'm going to do it again today:

CTP 2.0 is build 16.0.600.9. Here's @@VERSION and the internal database version:

SELECT [@@Version] = @@VERSION;
SELECT [Internal DB Version] = DATABASEPROPERTYEX(N'msdb', N'Version');
 
/*
 
@@Version
-----------------------------------------------------
Microsoft SQL Server 2022 (CTP2.0) - 16.0.600.9 (X64) 
	May 20 2022 13:29:42 
	Copyright (C) 2022 Microsoft Corporation
	Developer Edition (64-bit) on Windows ...
 
Internal DB Version
-----------------------------------------------------
950
 
*/

I have another instance here named \SQL2019; so, I created a linked server:

DECLARE @srv sysname = N'.\SQL2019';
 
EXEC [master].dbo.sp_addlinkedserver   
     @server     = @srv, 
     @srvproduct = N'SQL Server';
 
EXEC [master].dbo.sp_addlinkedsrvlogin 
     @rmtsrvname = @srv, 
     @useself    = N'True';
 
EXEC [master].dbo.sp_serveroption
     @server     = @srv,
     @optname    = N'collation compatible', 
     @optvalue   = N'true';
 
EXEC [master].dbo.sp_serveroption
     @server     = @srv,
     @optname    = N'data access', 
     @optvalue   = N'true';

Then I created some synonyms in master:

USE [master];
GO
 
CREATE SYNONYM dbo.old_databases FOR [.\SQL2019].master.sys.databases;
CREATE SYNONYM dbo.old_objects   FOR [.\SQL2019].master.sys.all_objects;
CREATE SYNONYM dbo.old_columns   FOR [.\SQL2019].master.sys.all_columns;
CREATE SYNONYM dbo.old_modules   FOR [.\SQL2019].master.sys.all_sql_modules;
CREATE SYNONYM dbo.old_sessions  FOR [.\SQL2019].master.sys.server_event_sessions;
CREATE SYNONYM dbo.old_xevents   FOR [.\SQL2019].master.sys.server_event_session_events;
CREATE SYNONYM dbo.old_perf      FOR [.\SQL2019].master.sys.dm_os_performance_counters;
CREATE SYNONYM dbo.old_messages  FOR [.\SQL2019].master.sys.messages;

Then I started snooping, though I know some of what I find is going to be specific to non-box versions of SQL Server.

New Objects

This query is "give me all the objects in sys that exist in new but not in old."

SELECT [Object Name] = N'sys.' + o.name, [Type] = o.[type_desc]
FROM sys.all_objects AS o
WHERE o.[schema_id] = 4 -- we only care about sys.
AND o.[type_desc] <> N'PRIMARY_KEY_CONSTRAINT'
AND NOT EXISTS 
(
  SELECT 1 FROM dbo.old_objects AS oo
    WHERE oo.[schema_id] = 4 AND oo.name = o.name
)
ORDER BY o.[type_desc], o.name;

This yielded 200 new objects!

Views (59)
sys.database_ledger_blocks
sys.database_ledger_digest_locations
sys.database_ledger_transactions
sys.database_query_store_internal_state
sys.dm_change_feed_errors
sys.dm_change_feed_log_scan_sessions
sys.dm_column_encryption_enclave_properties
sys.dm_database_backups
sys.dm_database_external_policy_actions
sys.dm_database_external_policy_principal_assigned_actions
sys.dm_database_external_policy_principals
sys.dm_database_external_policy_role_actions
sys.dm_database_external_policy_role_members
sys.dm_database_external_policy_roles
sys.dm_dist_requests
sys.dm_dw_databases
sys.dm_dw_locks
sys.dm_dw_pit_databases
sys.dm_dw_quality_clustering
sys.dm_dw_quality_delta
sys.dm_dw_quality_index
sys.dm_dw_quality_row_group
sys.dm_dw_resource_manager_abort_cache
sys.dm_dw_resource_manager_active_tran
sys.dm_dw_tran_manager_abort_cache
sys.dm_dw_tran_manager_active_cache
sys.dm_dw_tran_manager_commit_cache
sys.dm_exec_requests_history
sys.dm_external_data_processed
sys.dm_external_policy_cache
sys.dm_os_out_of_memory_events
sys.dm_request_phases
sys.dm_request_phases_exec_task_stats
sys.dm_request_phases_task_group_stats
sys.dm_server_external_policy_actions
sys.dm_server_external_policy_principal_assigned_actions
sys.dm_server_external_policy_principals
sys.dm_server_external_policy_role_actions
sys.dm_server_external_policy_role_members
sys.dm_server_external_policy_roles
sys.dm_server_hardware_offload_config
sys.dm_server_suspend_status
sys.dm_toad_tuning_zones
sys.dm_toad_work_item_handlers
sys.dm_toad_work_items
sys.external_job_streams
sys.external_stream_columns
sys.external_streaming_jobs
sys.external_streams
sys.external_table_partitioning_columns
sys.extgov_attribute_sync_state
sys.extgov_attribute_sync_tables_synchronizing
sys.ledger_column_history
sys.ledger_table_history
sys.query_store_plan_feedback
sys.query_store_plan_forcing_locations
sys.query_store_query_hints
sys.query_store_query_variant
sys.query_store_replicas
Internal Tables (21)
sys.backup_metadata_store
sys.db_ledger_blocks
sys.db_ledger_digest_locations
sys.db_ledger_transactions
sys.external_libraries_installed_table
sys.ledger_columns_history_internal
sys.ledger_columns_history_internal_history
sys.ledger_tables_history_internal
sys.ledger_tables_history_internal_history
sys.plan_persist_plan_feedback
sys.plan_persist_plan_forcing_locations
sys.plan_persist_query_variant
sys.plan_persist_replicas
sys.plan_persist_runtime_stats_v2
sys.plan_persist_wait_stats_v2
sys.polaris_executed_requests_history
sys.polaris_executed_requests_text
sys.polaris_file_cache_entries
sys.polaris_file_cache_streams
sys.polaris_file_statistics
sys.sql_pools_table
Extended Stored Procedures (84)
sys.sp_cdc_set_scheduler_job
sys.sp_cleanup_all_average_column_length_statistics
sys.sp_cleanup_all_openrowset_statistics
sys.sp_cleanup_all_user_data_in_master
sys.sp_collect_backend_plan
sys.sp_copy_data_in_batches
sys.sp_create_format_type
sys.sp_create_format_type_synonym
sys.sp_create_openrowset_statistics
sys.sp_create_parser_version
sys.sp_delete_database_engine_configuration_internal
sys.sp_drop_format_type
sys.sp_drop_openrowset_statistics
sys.sp_drop_parser_version
sys.sp_drop_storage_location
sys.sp_execute_flight_query
sys.sp_executesql_metrics
sys.sp_external_policy_refresh
sys.sp_fido_build_basic_histogram
sys.sp_fido_build_histogram
sys.sp_fido_execute_graph_request
sys.sp_fido_get_CS_rowset_row_count
sys.sp_fido_get_remote_storage_size
sys.sp_fido_glm_server_execute_batch
sys.sp_fido_glms_get_storage_containers
sys.sp_fido_glms_set_storage_containers
sys.sp_fido_glms_unregister_appname
sys.sp_fido_indexstore_update_topology
sys.sp_fido_indexstore_upgrade_node
sys.sp_fido_remove_retention_policy
sys.sp_fido_set_ddl_step
sys.sp_fido_set_retention_policy
sys.sp_fido_setup_endpoints
sys.sp_fido_tran_abort
sys.sp_fido_tran_begin
sys.sp_fido_tran_commit
sys.sp_fido_tran_get_state
sys.sp_fido_tran_set_token
sys.sp_generate_external_table_statistics_description_and_hash
sys.sp_generate_openrowset_statistics_props
sys.sp_generate_trident_table_manifest
sys.sp_get_dmv_collector_views
sys.sp_get_external_table_cardinality
sys.sp_get_fido_lock
sys.sp_get_fido_lock_batch
sys.sp_get_file_splits
sys.sp_get_migration_vlf_state
sys.sp_get_openrowset_statistics_additional_props
sys.sp_get_openrowset_statistics_cardinality
sys.sp_get_total_openrowset_statistics_count
sys.sp_manage_msdtc_transaction
sys.sp_process_memory_leak_record
sys.sp_publish_database_to_syms
sys.sp_query_store_clear_hints
sys.sp_query_store_clear_message_queues
sys.sp_query_store_set_hints
sys.sp_release_all_fido_locks
sys.sp_release_fido_lock
sys.sp_reset_inactive_duration_flag
sys.sp_reset_msdtc_log
sys.sp_set_data_processed_limit
sys.sp_set_database_engine_configuration_internal
sys.sp_set_def_format_type_default_target
sys.sp_set_def_format_type_extractor
sys.sp_set_def_format_type_md_preprocessor
sys.sp_set_distributed_feedback_context
sys.sp_set_format_type_ls_syntax
sys.sp_set_msdtc_network
sys.sp_set_parser_version_default_target
sys.sp_set_parser_version_extractor
sys.sp_set_parser_version_md_preprocessor
sys.sp_show_external_table_average_column_length_statistics
sys.sp_show_openrowset_statistics
sys.sp_shutdown_feedback_client_connection
sys.sp_start_fixed_vlf
sys.sp_start_flight_server
sys.sp_start_glm_server
sys.sp_stop_flight_server
sys.sp_update_logical_pause_deactivation_params
sys.sp_update_logical_pause_flag
sys.sp_upgrade_vdw_configuration_parameters
sys.sp_verify_database_ledger
sys.sp_verify_database_ledger_from_digest_storage
sys.sp_xcs_mark_column_relation
Stored Procedures
sys.sp_change_feed_create_table_group
sys.sp_change_feed_disable_db
sys.sp_change_feed_disable_table
sys.sp_change_feed_drop_table_group
sys.sp_change_feed_enable_db
sys.sp_change_feed_enable_table
sys.sp_change_feed_vupgrade
sys.sp_cleanup_data_retention
sys.sp_create_streaming_job
sys.sp_discover_trident_table
sys.sp_drop_streaming_job
sys.sp_drop_trident_data_location
sys.sp_fido_spaceused
sys.sp_generate_database_ledger_digest
sys.sp_get_streaming_job
sys.sp_get_trident_data_location
sys.sp_help_change_feed
sys.sp_invoke_external_rest_endpoint
sys.sp_ldw_apply_file_updates_for_ext_table
sys.sp_ldw_get_file_updates_for_ext_table
sys.sp_ldw_normalize_ext_tab_name
sys.sp_ldw_update_stats_for_ext_table
sys.sp_memory_leak_detection
sys.sp_metadata_sync_connector_add
sys.sp_metadata_sync_connector_drop
sys.sp_metadata_sync_connectors_status
sys.sp_MSchange_feed_ddl_event
sys.sp_set_trident_data_location
sys.sp_start_streaming_job
sys.sp_stop_streaming_job
sys.sp_update_streaming_job
Inline Table-Valued Functions (4)
sys.dm_xcs_enumerate_blobdirectory
sys.fn_filelog
sys.fn_ledger_retrieve_digests_from_url
sys.fn_xcs_get_file_rowcount
Scalar Functions (1)
sys.fn_cdc_is_ddl_handling_enabled

New Columns

This query is "for all the objects common to old version and new version, give me all the columns that are in the new but not in the old."

;WITH obj AS
(
  SELECT o.name, o.[object_id]
  FROM sys.all_objects AS o WHERE [schema_id] = 4
  INTERSECT
  SELECT o.name, o.[object_id]
  FROM dbo.old_objects AS o WHERE [schema_id] = 4
),
col(obj,col) AS
(
  SELECT obj.name, c.name 
  FROM obj INNER JOIN sys.all_columns AS c
    ON obj.[object_id] = c.[object_id]
  EXCEPT 
  SELECT obj.name, c.name 
  FROM obj INNER JOIN dbo.old_columns AS c
    ON obj.[object_id] = c.[object_id]
)
SELECT obj = N'sys.' + obj, col 
  FROM col ORDER BY obj, col;

This yielded 128 new columns!

Object Name
sys.all_columns
sys.all_columns
sys.all_columns
sys.all_columns
sys.all_views
sys.all_views
sys.all_views
sys.assembly_files
sys.assembly_files
sys.column_store_segments
sys.column_store_segments
sys.column_store_segments
sys.columns
sys.columns
sys.columns
sys.columns
sys.computed_columns
sys.computed_columns
sys.computed_columns
sys.computed_columns
sys.database_audit_specifications
sys.database_audit_specifications
sys.database_principals
sys.databases
sys.databases
sys.databases
sys.dm_db_xtp_checkpoint_stats
sys.dm_db_xtp_checkpoint_stats
sys.dm_exec_requests
sys.dm_exec_requests
sys.dm_hadr_cached_replica_states
sys.dm_hadr_cached_replica_states
sys.dm_hadr_cluster_members
sys.dm_io_virtual_file_stats
sys.dm_io_virtual_file_stats
sys.dm_os_buffer_descriptors
sys.dm_os_buffer_descriptors
sys.dm_os_buffer_descriptors
sys.dm_os_loaded_modules
sys.dm_os_nodes
sys.dm_os_nodes
sys.dm_os_nodes
sys.dm_os_schedulers
sys.dm_os_sublatches
sys.dm_os_sublatches
sys.dm_os_tasks
sys.dm_os_worker_local_storage
sys.dm_tran_persistent_version_store_stats
sys.dm_xe_session_events
sys.dm_xe_session_events
sys.dm_xe_session_events
sys.dm_xe_session_events
sys.dm_xe_sessions
sys.external_file_formats
sys.external_tables
sys.external_tables
sys.external_tables
sys.fn_get_audit_file
sys.fn_get_audit_file
sys.fn_get_audit_file
sys.fn_get_audit_file
sys.fn_get_audit_file
sys.fn_get_audit_file
sys.identity_columns
sys.identity_columns
sys.identity_columns
sys.identity_columns
sys.internal_partitions
sys.internal_partitions
sys.masked_columns
sys.masked_columns
sys.masked_columns
sys.masked_columns
sys.partitions
sys.partitions
sys.query_store_plan
sys.query_store_plan
sys.query_store_plan
sys.query_store_plan
sys.query_store_runtime_stats
sys.query_store_runtime_stats
sys.query_store_runtime_stats
sys.query_store_runtime_stats
sys.query_store_runtime_stats
sys.query_store_runtime_stats
sys.query_store_wait_stats
sys.server_audit_specifications
sys.server_audit_specifications
sys.server_audits
sys.server_principals
sys.stats
sys.syscscolsegments
sys.syscscolsegments
sys.syscscolsegments
sys.sysextfileformats
sys.syslogins
sys.syslogins
sys.syslogins
sys.syslogins
sys.syslogins
sys.syslogins
sys.syslogins
sys.sysowners
sys.sysrscols
sys.system_columns
sys.system_columns
sys.system_columns
sys.system_columns
sys.system_views
sys.system_views
sys.system_views
sys.sysxlgns
sys.tables
sys.tables
sys.tables
sys.tables
sys.tables
sys.tables
sys.tables
sys.views
sys.views
sys.views
Column Name
is_data_deletion_filter_column
is_dropped_ledger_column
ledger_view_column_type
ledger_view_column_type_desc
is_dropped_ledger_view
ledger_view_type
ledger_view_type_desc
sha2_256
sha2_512
collation_id
max_deep_data
min_deep_data
is_data_deletion_filter_column
is_dropped_ledger_column
ledger_view_column_type
ledger_view_column_type_desc
is_data_deletion_filter_column
is_dropped_ledger_column
ledger_view_column_type
ledger_view_column_type_desc
is_session_context_enabled
session_context_keys
tenant_id
is_change_feed_enabled
is_data_retention_enabled
is_ledger_on
closed_checkpoint_epoch_value
db_in_checkpoint_only_mode
dist_statement_id
label
secondary_role_allow_connections
secondary_role_allow_connections_desc
number_of_current_votes
num_of_pushed_bytes_returned
num_of_pushed_reads
buffer_address
latch_address
latch_desc
target
cached_tasks
cached_tasks_removed
cached_tasks_reused
queued_disk_io_count
class_desc
latch_desc
task_local_storage
performance_counters_address
pvs_off_row_page_skipped_oldest_aborted_xdesid
event_fire_average_time
event_fire_count
event_fire_max_time
event_fire_min_time
total_target_memory
parser_version
partition_desc
partition_type
table_options
client_tls_version
client_tls_version_name
database_transaction_id
external_policy_permissions_checked
ledger_start_sequence_number
session_context
is_data_deletion_filter_column
is_dropped_ledger_column
ledger_view_column_type
ledger_view_column_type_desc
xml_compression
xml_compression_desc
is_data_deletion_filter_column
is_dropped_ledger_column
ledger_view_column_type
ledger_view_column_type_desc
xml_compression
xml_compression_desc
has_compile_replay_script
is_optimized_plan_forcing_disabled
plan_type
plan_type_desc
avg_page_server_io_reads
last_page_server_io_reads
max_page_server_io_reads
min_page_server_io_reads
replica_group_id
stdev_page_server_io_reads
replica_group_id
is_session_context_enabled
session_context_keys
is_operator_audit
tenant_id
auto_drop
collation_id
max_deep_data
min_deep_data
parser_version
##MS_DatabaseConnector##
##MS_DatabaseManager##
##MS_DefinitionReader##
##MS_LoginManager##
##MS_SecurityDefinitionReader##
##MS_ServerStateManager##
##MS_ServerStateReader##
tenantid
ordlock
is_data_deletion_filter_column
is_dropped_ledger_column
ledger_view_column_type
ledger_view_column_type_desc
is_dropped_ledger_view
ledger_view_type
ledger_view_type_desc
tenantid
data_retention_period
data_retention_period_unit
data_retention_period_unit_desc
is_dropped_ledger_table
ledger_type
ledger_type_desc
ledger_view_id
is_dropped_ledger_view
ledger_view_type
ledger_view_type_desc

Changed Modules

This query is "give me all the definitions for all modules that are different between old and new."

SELECT o.[type_desc], N'sys.' + o.name,
  delta = LEN(m.[definition]) - LEN(rm.[definition]), 
  new_definition = m.[definition],
  old_definition = rm.[definition]
FROM sys.all_sql_modules AS m
INNER JOIN sys.all_objects AS o
  ON m.[object_id] = o.[object_id]
INNER JOIN dbo.old_objects AS ro
  ON o.name = ro.name
INNER JOIN dbo.old_modules AS rm
  ON ro.[object_id] = rm.[object_id]
WHERE  o.[schema_id] = 4
  AND ro.[schema_id] = 4
  AND m.[definition] <> rm.[definition]
ORDER BY o.[type_desc], o.name;

I'm not going to output the actual definitions here for space.

This yielded 117 changed modules!

Inline Table-Valued Functions (8)
sys.dm_db_page_info
sys.dm_io_virtual_file_stats
sys.dm_os_enumerate_filesystem
sys.fn_dblog
sys.fn_dbslog
sys.fn_dump_dblog
sys.fn_full_dblog
sys.fn_get_audit_file
Change
-1
+58
-1
+5
+5
+5
+14
+172

 

Scalar Functions (2)
sys.fn_GetRowsetIdFromRowDump
sys.fn_MSrepl_map_resolver_clsid
Change
-2
+2,098

 

Stored Procedures (62)
sys.sp_add_log_shipping_primary_database
sys.sp_adddistributiondb
sys.sp_adddistributor
sys.sp_addlinkedserver
sys.sp_addlinkedsrvlogin
sys.sp_addmergearticle
sys.sp_addpullsubscription_agent
sys.sp_addscriptexec
sys.sp_bindrule
sys.sp_can_tlog_be_applied
sys.sp_cdc_add_job
sys.sp_cdc_disable_db
sys.sp_cdc_enable_db
sys.sp_cdc_enable_table
sys.sp_cdc_scan
sys.sp_cdc_vupgrade
sys.sp_cdc_vupgrade_databases
sys.sp_change_log_shipping_primary_database
sys.sp_changemergesubscription
sys.sp_dbcmptlevel
sys.sp_delete_log_shipping_primary_database
sys.sp_dropdistributiondb
sys.sp_dropserver
sys.sp_estimate_data_compression_savings
sys.sp_flush_commit_table_on_demand
sys.sp_flush_CT_internal_table_on_demand
sys.sp_fulltext_service
sys.sp_help_log_shipping_primary_database
sys.sp_helpdistpublisher
sys.sp_helpsrvrolemember
sys.sp_is_columnstore_column_dictionary_enabled
sys.sp_ldw_insert_container_and_partition_for_ext_table
sys.sp_ldw_internal_tables_clean_up
sys.sp_ldw_refresh_internal_table_on_distribution
sys.sp_ldw_select_entries_from_internal_table
sys.sp_logshippinginstallmetadata
sys.sp_MSadd_distribution_agent
sys.sp_MSadd_logreader_agent
sys.sp_MSadd_publication
sys.sp_MSadd_subscription
sys.sp_MScdc_db_ddl_event
sys.sp_MScdc_ddl_event
sys.sp_MScdc_logddl
sys.sp_MSdistpublisher_cleanup
sys.sp_MSdrop_publication
sys.sp_MSget_last_transaction
sys.sp_MSget_repl_commands
sys.sp_MSrepl_testadminconnection
sys.sp_MSsetupnosyncsubwithlsnatdist
sys.sp_peerconflictdetection_tableaug
sys.sp_pkeys
sys.sp_remove_columnstore_column_dictionary
sys.sp_rename
sys.sp_replicationdboption
sys.sp_replmonitorhelppublisher
sys.sp_serveroption
sys.sp_spaceused
sys.sp_srvrolepermission
sys.sp_upgrade_log_shipping
sys.sp_vupgrade_mergetables
sys.sp_vupgrade_replication
sys.sp_vupgrade_replsecurity_metadata
Change
+1,140
+462
+618
+176
+338
+290
+643
+263
+688
+157
+7
+141
+75
+355
+4
-779
+3
+403
+97
+129
+136
+264
-27
+1,673
+143
+189
0
+135
-57
+22
-2
+327
+8
+1,196
+5
+380
+1,086
+1,422
+361
+342
+145
+307
+229
+684
+351
+952
+3
+506
0
+44
-12
-2
+2,315
+569
-48
+318
+1,131
+22
+18
0
+21
+1

 

Views (45)
sys.all_views
sys.assembly_files
sys.change_tracking_databases
sys.column_store_segments
sys.columns
sys.computed_columns
sys.database_audit_specifications
sys.database_files
sys.database_filestream_options
sys.database_mirroring
sys.database_principals
sys.database_recovery_status
sys.databases
sys.dm_db_partition_stats
sys.dm_exec_requests
sys.dm_hadr_cached_replica_states
sys.dm_os_buffer_descriptors
sys.dm_os_buffer_pool_extension_configuration
sys.dm_os_memory_cache_entries
sys.dm_tran_persistent_version_store
sys.external_file_formats
sys.external_tables
sys.identity_columns
sys.internal_partitions
sys.masked_columns
sys.master_files
sys.partitions
sys.query_context_settings
sys.query_store_plan
sys.query_store_query_text
sys.query_store_runtime_stats
sys.query_store_runtime_stats_interval
sys.query_store_wait_stats
sys.server_audit_specifications
sys.server_audits
sys.server_principals
sys.server_role_members
sys.stats
sys.sysaltfiles
sys.syscolumns
sys.syslogins
sys.system_columns
sys.system_views
sys.tables
sys.views
Change
+476
+298
+65
+86
+1,040
+674
+307
+25
+66
+67
+28
+75
+219
-3
+26
+178
+52
-5
+375
-1
+265
+982
+511
+317
+490
+25
+613
+29
+236
+394
+528
-82
+22
+314
+22
+26
+413
+975
+25
+281
+1,395
+125
+438
+1,903
+449

Changed XEvents

This query is "give me all the package/event combinations in the system health session that didn't exist in the old":

SELECT [Event] = e.package + '.' + e.name, e.event_id
  FROM sys.server_event_sessions AS s
  INNER JOIN sys.server_event_session_events AS e
  ON s.event_session_id = e.event_session_id
 WHERE s.name = N'system_health'
EXCEPT
SELECT e.package + '.' + e.name, e.event_id
  FROM dbo.old_sessions AS s
  INNER JOIN dbo.old_xevents AS e
  ON s.event_session_id = e.event_session_id
 WHERE s.name = N'system_health'
ORDER BY e.package + '.' + e.name, e.event_id;

This yielded 6 rows:

Package / Event
sqlos.process_killed
sqlserver.connectivity_ring_buffer_recorded
sqlserver.job_object_ring_buffer_stats
sqlserver.nonyield_copiedstack_ring_buffer_recorded
sqlserver.sp_server_diagnostics_component_result
sqlserver.sql_exit_invoked
event_id
51
37
42
43
40
59

New Error Messages

This query is "show me all the English error messages that are in the new but weren't in the old":

SELECT m.message_id, m.[text]
FROM sys.messages AS m
WHERE m.language_id = 1033 -- us_english
AND NOT EXISTS 
(
  SELECT 1 FROM dbo.old_messages AS om
    WHERE om.message_id = m.message_id
);

This produced a lot of rows (1,394, to be exact). Here are a few that matched the pattern '%ledger%':

22725
Enabling Change Feed for a ledger history table '%ls' is not allowed.

37340
Failed to generate the Ledger Blocks in the database with ID %d due to error %d. Check the errorlog for more information.

37354
LEDGER = ON cannot be specified with system versioning retention period.

37378
Ledger tables cannot be created on system databases.

37385
LEDGER = ON is not allowed for table variables.

In a future post…

…I'll take a look at some other metadata.

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at SQLPerformance and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him.

2 Responses

  1. Neeraj Mittal says:

    Awesome to get new 2022 details at granule level.

  1. May 30th, 2022

    […] Aaron Bertrand has a three-parter on obscure changes in SQL Server 2022. First up we have some new information: […]