Share via

Sql server telemetry_xevents hits server audit log alter session events rapidly

  • Article
  • 12/14/2016

Question

Wednesday, December 14, 2016 11:59 AM

Hi,

we are facing an issue in our server audit audit log hits with below events unexpectedly its around 20000 per day, only change we did in our server was adding a columnstore index, and  we cannot delete or stop telemetry_xevents it creates and starts automatically from server and starts logging this events please help us to resolve this issue

our audit spec contains

(DATABASE_ROLE_MEMBER_CHANGE_GROUP),
(FAILED_DATABASE_AUTHENTICATION_GROUP),
(DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
(SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
(SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
(SERVER_PERMISSION_CHANGE_GROUP),
(DATABASE_CHANGE_GROUP),
(SERVER_OBJECT_CHANGE_GROUP),
(DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
(SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
(SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)

Audit log taken from sys.fn_get_audit_file

object_name                    statement
telemetry_xevents    drop event session telemetry_xevents on server    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[error_reported]  (  WHERE severity >= 16 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)  )    
    create event session telemetry_xevents on server  ADD EVENT [sqlserver].[server_start_stop]  add target package0.ring_buffer  (set occurrence_number = 100)  with  (      MAX_DISPATCH_LATENCY = 120 SECONDS,      MAX_MEMORY = 4 MB,      startup_state = on  )    

telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[missing_column_statistics]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[missing_join_predicate]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[server_memory_change]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_database_disable_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_database_enable_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_database_reauthorize_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_index_reconciliation_codegen_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_remote_column_execution_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_remote_column_reconciliation_codegen_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_remote_index_execution_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_codegen_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_alter_ddl]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_create_ddl]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_predicate_not_specified]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_predicate_specified]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_remote_creation_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_row_migration_results_event]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_row_unmigration_results_event]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_data_reconciliation_results_event]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_unprovision_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_validation_error]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_hinted_admin_update_event]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_hinted_admin_delete_event]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_query_error]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[temporal_ddl_system_versioning]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[temporal_dml_transaction_fail]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[temporal_ddl_period_add]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[temporal_ddl_period_drop]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[temporal_ddl_schema_check_fail]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[data_masking_ddl_column_definition]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[data_masking_traffic]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[data_masking_traffic_masked_only]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[always_encrypted_query_count]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[rls_query_count]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[auto_stats]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[database_cmptlevel_change]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[database_created]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[database_dropped]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[reason_many_foreign_keys_operator_not_used]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[column_store_index_build_low_memory]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[column_store_index_build_throttle]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[columnstore_delete_buffer_flush_failed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[columnstore_delta_rowgroup_closed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[columnstore_index_reorg_failed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[columnstore_log_exception]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[columnstore_rowgroup_merge_failed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[columnstore_tuple_mover_delete_buffer_truncate_timed_out]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[columnstore_tuple_mover_end_compress]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[query_memory_grant_blocking]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[natively_compiled_module_inefficiency_detected]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[natively_compiled_proc_slow_parameter_passing]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[xtp_alter_table]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[xtp_db_delete_only_mode_updatedhktrimlsn]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[xtp_stgif_container_added]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[xtp_stgif_container_deleted]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [xtpcompile].[cl_duration]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [xtpengine].[xtp_physical_db_restarted]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [xtpengine].[xtp_db_delete_only_mode_enter]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [xtpengine].[xtp_db_delete_only_mode_update]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [xtpengine].[xtp_db_delete_only_mode_exit]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [xtpengine].[parallel_alter_stats]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [xtpengine].[serial_alter_stats]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[json_function_compiled]  (  ACTION ([database_id])  )    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[string_escape_compiled]  (  ACTION ([database_id])  )    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[window_function_used]  (  ACTION ([database_id])  )    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[sequence_function_used]  (  ACTION ([database_id])  )    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [qds].[query_store_db_diagnostics]      
telemetry_xevents    alter event session telemetry_xevents on server state=start  
telemetry_xevents    drop event session telemetry_xevents on server    
telemetry_xevents    drop event session telemetry_xevents on server    
    create event session telemetry_xevents on server  ADD EVENT [sqlserver].[server_start_stop]  add target package0.ring_buffer  (set occurrence_number = 100)  with  (      MAX_DISPATCH_LATENCY = 120 SECONDS,      MAX_MEMORY = 4 MB,      startup_state = on  )    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[error_reported]  (  WHERE severity >= 16 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)  )    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[missing_column_statistics]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[missing_join_predicate]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[server_memory_change]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_database_disable_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_database_enable_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_database_reauthorize_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_index_reconciliation_codegen_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_remote_column_execution_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_remote_column_reconciliation_codegen_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_remote_index_execution_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_codegen_completed]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_alter_ddl]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_create_ddl]    
telemetry_xevents    ALTER EVENT SESSION [telemetry_xevents] ON SERVER  ADD EVENT [sqlserver].[stretch_table_predicate_not_specified]   

All replies (3)

Wednesday, January 4, 2017 4:02 PM

From SSMS Connect to server and drill down to Management  / Extended Events / Sessions

There you will see a list of active and inactive sessions, right click, properties .....

Bob

Thursday, January 5, 2017 3:04 AM

Hi

Thanks for the reply ,

as i have informed above, we cannot remove or stop the sessions (telemetry_xevents), it's automatically recreates from the server itself, even after deleting is there any way to stop this.

Heshand

Thursday, January 5, 2017 2:25 PM | 1 vote

Sorry I realized that same thing after my post, but after a little more digging I found this

http://windowsitpro.com/windows-10/how-turn-telemetry-windows-7-8-and-windows-10

Which helped me to find that there is a service running for each SSIS, SSAS, and the SQL engine, after disabling and stopping those that solved my problem.

Hope this helps

Bob