Sql error 8651

sql error 8651

Memory related errors (Errors 17803, 701, 802, 8645, 8651, 8657 and 8902). Non-yielding scheduler problems (Error 17883). Deadlocks. Sessions. Event ID, 8651. Event Source, MSSQLSERVER. Component, SQLEngine. Symbolic Name, MEMGRANT_ERR. Message Text, Could not perform the requested. The following list outlines general steps that will help in troubleshooting memory errors: Verify whether other applications or services are consuming memory on.

watch the video

حل النهائي في خطأ اثناء تثبيت Errors during installing Sql Server2014 ..Extraction Failed

Sql error 8651 - with

New Event Added to SSMS XEvent Profiler Sessions

December 3, 2019

SSMS XEvent Profiler was made available on SQL Server Management Studio (SSMS) v17.3 or later. Make sure you are using the latest version. You can find the latest version of SSMS here.

What is the XEvent Profiler?

The XEvent Profiler is a SQL Server Management Studio (SSMS) feature that displays a live viewer window of extended events. XEvent Profiler is directly integrated into SSMS and is built on top of the scalable Extended Events technology in the SQL engine. This feature enables quick access to a live streaming view of diagnostics events on the SQL server. You can read more about SSMS XEvent Profiler here.

In SSMS 18.4 a new event  ‘error_reported’  was added to SSMS XEvent Profiler.  This event gets fired every time an error happens in the server.  This is the exact tsql that was added in for this event:

ADD EVENT sqlserver.error_reported(ACTION(package0.callstack, sqlserver.database_id, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack) WHERE ( [severity] >= (20) OR ([error_number] = (17803) OR [error_number] = (701) OR [error_number] = (802) OR [error_number] = (8645) OR [error_number] = (8651) OR [error_number] = (8657) OR [error_number] = (8902) OR [error_number] = (41354) OR [error_number] = (41355) OR [error_number] = (41367) OR [error_number] = (41384) OR [error_number] = (41336) OR [error_number] = (41309) OR [error_number] = (41312) OR [error_number] = (41313) ) ))

Severity 20 or more means:

Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect.

Error messages in this range can affect all of the processes accessing data in the same database and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 24 are written to the error log.

More details about each severity number are documented here.

You can find out what each of the error numbers means here.

   Related: More > Extended Events


Problem

In my previous tips SQL Server 2016 extended events detailed backup information and SQL Server restore steps with extended events we have seen how useful Extended Events are in SQL Server 2016. While exploring more on Extended Events I observed some new interesting events and in this tip we will explore these and see how they can be helpful.

Solution

Extended Events play an important role for troubleshooting issues and will replace Profiler probably in an upcoming version of SQL Server. There are new Extended Events called telemetry_xevents in the Sessions folder in the Extended Events folder and it is enabled by default in SQL Server 2016.

As per Microsoft, most applications will include diagnostic features that generate custom monitoring and debugging information, especially when an error occurs. This is referred to as instrumentation and is usually implemented by adding event and error handling code to the application. The process of gathering remote information that is collected by instrumentation is usually referred to as telemetry.

SQL Server Extended Events telemetry_xevents in Management Studio

So let's explore these Extended Events.

SQL Server telemetry_xevents Extended Events

Microsoft has provided this new Extended Events Session to capture details around important events, errors that occurred and could help us to view what's happening with SQL Server at a high level in case of an issue.

So to see which events are being captured in this telemetry_xevents session, right click on the session and select Script Session As > Create To > New Query Editor Window.  The following are the events that are part of this session:

CREATE EVENT SESSION [telemetry_xevents] ON SERVER ADD EVENT qds.query_store_db_diagnostics, ADD EVENT sqlserver.always_encrypted_query_count, ADD EVENT sqlserver.auto_stats, ADD EVENT sqlserver.column_store_index_build_low_memory, ADD EVENT sqlserver.column_store_index_build_throttle, ADD EVENT sqlserver.columnstore_delete_buffer_flush_failed, ADD EVENT sqlserver.columnstore_delta_rowgroup_closed, ADD EVENT sqlserver.columnstore_index_reorg_failed, ADD EVENT sqlserver.columnstore_log_exception, ADD EVENT sqlserver.columnstore_rowgroup_merge_failed, ADD EVENT sqlserver.columnstore_tuple_mover_delete_buffer_truncate_timed_out, ADD EVENT sqlserver.columnstore_tuple_mover_end_compress, ADD EVENT sqlserver.data_masking_ddl_column_definition, ADD EVENT sqlserver.data_masking_traffic, ADD EVENT sqlserver.data_masking_traffic_masked_only, ADD EVENT sqlserver.database_cmptlevel_change, ADD EVENT sqlserver.database_created, ADD EVENT sqlserver.database_dropped, ADD EVENT sqlserver.error_reported( WHERE ([severity]>=(20) OR ([error_number]=(18456) OR [error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902) OR [error_number]=(41354) OR [error_number]=(41355) OR [error_number]=(41367) OR [error_number]=(41384) OR [error_number]=(41336) OR [error_number]=(41309) OR [error_number]=(41312) OR [error_number]=(41313)))), ADD EVENT sqlserver.json_function_compiled( ACTION(sqlserver.database_id)), ADD EVENT sqlserver.missing_column_statistics, ADD EVENT sqlserver.missing_join_predicate, ADD EVENT sqlserver.query_memory_grant_blocking, ADD EVENT sqlserver.rls_query_count, ADD EVENT sqlserver.sequence_function_used( ACTION(sqlserver.database_id)), ADD EVENT sqlserver.server_memory_change, ADD EVENT sqlserver.server_start_stop, ADD EVENT sqlserver.stretch_database_disable_completed, ADD EVENT sqlserver.stretch_database_enable_completed, ADD EVENT sqlserver.stretch_database_reauthorize_completed, ADD EVENT sqlserver.stretch_index_reconciliation_codegen_completed, ADD EVENT sqlserver.stretch_remote_column_execution_completed, ADD EVENT sqlserver.stretch_remote_column_reconciliation_codegen_completed, ADD EVENT sqlserver.stretch_remote_index_execution_completed, ADD EVENT sqlserver.stretch_table_alter_ddl, ADD EVENT sqlserver.stretch_table_codegen_completed, ADD EVENT sqlserver.stretch_table_create_ddl, ADD EVENT sqlserver.stretch_table_data_reconciliation_results_event, ADD EVENT sqlserver.stretch_table_hinted_admin_delete_event, ADD EVENT sqlserver.stretch_table_hinted_admin_update_event, ADD EVENT sqlserver.stretch_table_predicate_not_specified, ADD EVENT sqlserver.stretch_table_predicate_specified, ADD EVENT sqlserver.stretch_table_query_error, ADD EVENT sqlserver.stretch_table_remote_creation_completed, ADD EVENT sqlserver.stretch_table_row_migration_results_event, ADD EVENT sqlserver.stretch_table_row_unmigration_results_event, ADD EVENT sqlserver.stretch_table_unprovision_completed, ADD EVENT sqlserver.stretch_table_validation_error, ADD EVENT sqlserver.string_escape_compiled( ACTION(sqlserver.database_id)), ADD EVENT sqlserver.temporal_ddl_period_add, ADD EVENT sqlserver.temporal_ddl_period_drop, ADD EVENT sqlserver.temporal_ddl_schema_check_fail, ADD EVENT sqlserver.temporal_ddl_system_versioning, ADD EVENT sqlserver.temporal_dml_transaction_fail, ADD EVENT sqlserver.window_function_used( ACTION(sqlserver.database_id)) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) GO

As we can see this Extended Event Session has START_STATE= ON that means even if you restart SQL Server, this Extended Event Session will automatically start up.

This contains a lot of Extended Events, so let's see what these are:

  • sqlserver.auto_stats: Occurs when index and column statistics are automatically updated. This event can be generated multiple times per statistics collection when the update is asynchronous.
  • column_store_index_build_low_memory: Storage Engine detected a low memory condition and the rowgroup size was reduced.
  • column_store_index_build_throttle: Shows the statistics of columnstore index build parallelism throttling.
  • columnstore_delete_buffer_flush_failed: Columnstore delete buffer flush failed.
  • columnstore_delta_rowgroup_closed: A delta rowgroup was closed.
  • columnstore_index_reorg_failed: An Index Reorganize operation failed.
  • columnstore_log_exception: Occurs when an exception occurs during processing for a columnstore operation.
  • columnstore_rowgroup_merge_failed: A MERGE operation started merging columnstore rowgroups but failed in the process.
  • columnstore_tuple_mover_delete_buffer_truncate_timed_out: Columnstore tuple mover truncate operation timed out due to an active snapshot transaction.
  • columnstore_tuple_mover_end_compress: Columnstore tuple mover completed compressing a deltastore.
  • data_masking_ddl_column_definition: Occurs when a data masking function is modified on a table column definition (ADD, CHANGE or DROP)
  • data_masking_traffic: Occurs when data masking is applied to a query.
  • data_masking_traffic_masked_only: Occurs when data masking is applied to a query and the query is actually masked.
  • database_cmptlevel_change: Database compatibility level is changed.
  • database_created
  • database_dropped
  • error_reported: Occurs when an error is reported.
  • json_function_compiled: Indicates that JSON function is compiled.
  • missing_column_statistics: Occurs when a query accesses a column that does not have statistics available that could have been useful for query optimization. Use this event to identify queries that may be using sub-optimal plans. This event is only generated when the Auto Create Statistics option is turned off.
  • missing_join_predicate: Occurs when an executed query is missing a join predicate. Use this event to identify a query that may perform slowly because of the missing predicate. This event only occurs if both sides of the join return more than one row.
  • query_memory_grant_blocking: Occurs when a query is blocking other queries while waiting for memory grant
  • query_store_db_diagnostics: Periodically fired with Query Store diagnostics on database level.
  • rls_query_count: Query using RLS.
  • sequence_function_used: Occurs when a sequence function is used.
  • server_memory_change: Occurs when the server memory usage increases or decreases by more than either 1 megabyte (MB) or 5% of the maximum server memory, whichever is larger. Use this event to identify periods of increased memory usage on the server.
  • server_start_stop: Occurs when the Server has been started or stopped.
  • stretch_database_disable_completed: Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE OFF command.
  • stretch_database_enable_completed: Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE ON command.
  • stretch_database_reauthorize_completed: Reports the completion of a sp_rda_reauthorize_db spec proc.
  • stretch_index_reconciliation_codegen_completed: Reports the completion of code generation for stretch remote index operation.
  • stretch_remote_column_execution_completed: Reports the completion of remote execution for the generated code for a stretched column.
  • stretch_remote_column_reconciliation_codegen_completed: Reports the completion of code generation for stretch remote column reconciliation.
  • stretch_remote_index_execution_completed: Reports the completion of remote execution for the generated code for a stretched index.
  • stretch_table_alter_ddl: Occurs when the stretch is enabled from ALTER TABLE DDL.
  • stretch_table_codegen_completed: Reports the completion of code generation for a stretched table.
  • stretch_table_create_ddl: Occurs when the stretch is enabled from CREATE TABLE DDL.
  • stretch_table_data_reconciliation_results_event: Reports an error or completion of a successful data reconciliation of a number of batches of rows.
  • stretch_table_hinted_admin_delete_event: Reports the execution of a Stretch delete DML operation that uses an admin hint.
  • stretch_table_hinted_admin_update_event: Reports the execution of a Stretch update DML operation that uses an admin hint.
  • stretch_table_predicate_not_specified: Occurs when predicate is not specified during stretching of table.
  • stretch_table_predicate_specified: Occurs when predicate is specified during stretching of table.
  • stretch_table_query_error: Reports an error thrown during Stretch query rewrite.
  • stretch_table_remote_creation_completed: Reports the completion of remote execution for the generated code for a stretched table.
  • stretch_table_row_migration_results_event: Reports an error or completion of a successful migration of a number of batches of rows.
  • stretch_table_row_unmigration_results_event: Reports an error or completion of a successful unmigration of a number of batches of rows
  • stretch_table_unprovision_completed: Reports the completion removal of local resources for a table that was unstretched.
  • stretch_table_validation_error: Reports the completion of validation for a table when the user enables stretch.
  • string_escape_compiled: Indicates that STRING_ESCAPE function is compiled.
  • temporal_ddl_period_add: Occurs when temporal period is being added.
  • temporal_ddl_period_drop: Occurs when temporal period is being dropped.
  • temporal_ddl_schema_check_fail: Occurs when temporal ddl data consistency check fails.
  • temporal_ddl_system_versioning: Occurs when system versioning option for a table is being changed.
  • temporal_dml_transaction_fail: Occurs when start time of the record being inserted into history table is greater than its end time.
  • window_function_used: Occurs when a window function is used.

Now let's see how this works.  I performed some operations like database creation, compatibility level change, database drop, etc. to see how this is getting captured with this Extended Events Session using Watch Live Data. To do this go to Management > Extended Events > Sessions and right click on telemetry_xevents and select Watch Live Data.

Watch Live Data interface for Extended Events in Management Studio

As we can see it captures detailed information about the events like a database create, compatibility level change, missing column statistics, etc.

If we look at current active sessions while the Extended Events data is being collected we can see this session is using a table valued function (TVF) sys.fn_MSxe_read_event_stream to pull back the data.  It also shows wait type XE_LIVE_TARGET_TVF. This wait type occurs when a thread is waiting for an event session to have events fire while watching the session output in the live data viewer, but this doesn't seem to be causing any issues, however it would be good to monitor if it is a significant wait type in your environment.

SELECT type, data FROM sys.fn_MSxe_read_event_stream (@source, @sourceopt)

This is the query that is being executed:

SELECT type, data FROM sys.fn_MSxe_read_event_stream (@source, @sourceopt)

More specifically it is running the below code to capture the data:

DECLARE @source NVARCHAR(256) = 'telemetry_xevents' ,@sourceopt INT = 0; -- bit 0 specifies live data, if 1 it will take source as .xel file. SELECT type, data FROM sys.fn_MSxe_read_event_stream(@source, @sourceopt);

Stop or Start a SQL Server Extended Events Session

To stop or start the data collection we can run the following commands:

--Command to stop the xevent session ALTER EVENT SESSION [telemetry_xevents] ON SERVER STATE = stop; --Command to start the xevent session ALTER EVENT SESSION [telemetry_xevents] ON SERVER STATE = start;

It is a good idea to explore this telemetry_xevents session and benefit from the new features in SQL Server 2016.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Rajendra GuptaRajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips


Article Last Updated: 2016-08-19