Ca fait un moment que nous avons, de temps en temps, des blocages dont l'origine nous échappe.
Ce matin nous avons eu à nouveau l'évènement : un job planifié avec une récurrence de 1 minute (en permanence) se met en mode SUSPENDED de manière infinie.
Nous avons du "killer" la session.
Bien sûr, nous avons vérifié, le job en question ne consomme aucune ressource réseau du fait de son code.
De plus c'est le seul à être bloqué ce matin parmi une joyeuse collection de jobs.
Et comme on a pris notre temps pour tout ça on a tué la tâche vers 11h alors qu'elle était dans cet état depuis 7h du mat.
La session n'en bloquait pas encore d'autres malgré ses verrous, mais l'expérience nous a montré qu'il fallait ne pas trop tarder quand même
Voici ce que nous avons collecté à propos de cette session :
sys.dm_os_waiting_tasks
waiting_task_address 0x000000CE9B6F24E8 session_id 163 exec_context_id 0 wait_duration_ms 535 wait_type ASYNC_NETWORK_IO resource_address 0x000000BF8AEC8CA0 blocking_task_address NULL blocking_session_id NULL blocking_exec_context_id NULL resource_description
sys.dm_tran_locks
resource_type DATABASE DATABASE DATABASE resource_subtype resource_database_id 9 35 45 resource_description resource_associated_entity_id 0 0 0 resource_lock_partition 6 6 6 request_mode S S S request_type LOCK LOCK LOCK request_status GRANT GRANT GRANT request_reference_count 1 1 1 request_lifetime 0 0 0 request_session_id 163 163 163 request_exec_context_id 0 0 0 request_request_id 0 0 0 request_owner_type SHARED_TRANSACTION_WORKSPACE SHARED_TRANSACTION_WORKSPACE SHARED_TRANSACTION_WORKSPACE request_owner_id 0 0 0 request_owner_guid 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 request_owner_lockspace_id 0x000000C1AB851130:0:0 0x000000C1AB851130:0:0 0x000000C1AB851130:0:0 lock_owner_address 0x00000102FD945500 0x000000B4213A7C80 0x000000CD4D1B54C0
sys.dm_exec_connections
session_id 163 most_recent_session_id 163 connect_time 2018-04-1907:13:30.083 net_transport Sharedmemory protocol_type TSQL protocol_version 1946157060 endpoint_id 2 encrypt_option FALSE auth_scheme NTLM node_affinity 0 num_reads 11 num_writes 70 last_read 2018-04-1907:13:30.087 last_write 2018-04-1907:15:37.340 net_packet_size 8192 client_net_address <localmachine> client_tcp_port NULL local_net_address NULL local_tcp_port NULL connection_id 9F010793-BE3C-42CE-BF2C-027134DA4CD7 parent_connection_id NULL most_recent_sql_handle 0x0300090020AE4364D5E9AC0005A8000001000000000000000000000000000000000000000000000000000000
sys.dm_exec_requests
session_id 163 request_id 0 start_time 2018-04-1907:13:30.090 status suspended command SELECT sql_handle 0x0300090020AE4364D5E9AC0005A8000001000000000000000000000000000000000000000000000000000000 statement_start_offset 46134 statement_end_offset 46284 plan_handle 0x0500090020AE4364501F87631F01000001000000000000000000000000000000000000000000000000000000 database_id 9 user_id 1 connection_id 9F010793-BE3C-42CE-BF2C-027134DA4CD7 blocking_session_id 0 wait_type ASYNC_NETWORK_IO wait_time 533 last_wait_type ASYNC_NETWORK_IO wait_resource open_transaction_count 0 open_resultset_count 1 transaction_id 0 context_info 0x percent_complete 0.0 estimated_completion_time 0 cpu_time 140654 total_elapsed_time 11105321 scheduler_id 6 task_address 0x000000CE9B6F24E8 reads 106971 writes 112 logical_reads 2664242 text_size 1024 language Français date_format dmy date_first 1 quoted_identifier 1 arithabort 0 ansi_null_dflt_on 1 ansi_defaults 0 ansi_warnings 1 ansi_padding 1 ansi_nulls 1 concat_null_yields_null 1 transaction_isolation_level 2 lock_timeout -1 deadlock_priority 0 row_count 1 prev_error 0 nest_level 7 granted_query_memory 0 executing_managed_code 0 group_id 2 query_hash NULL query_plan_hash NULL statement_sql_handle NULL statement_context_id NULL dop 1 parallel_worker_count NULL external_script_request_id NULL
sys.dm_os_workers
worker_address 0x00000113DF40C160 status 0 is_preemptive 0 is_fiber 0 is_sick 0 is_in_cc_exception 0 is_fatal_exception 0 is_inside_catch 0 is_in_polling_io_completion_routine 0 context_switch_count 7164858 pending_io_count 11109 pending_io_byte_count 0 pending_io_byte_average 0 wait_started_ms_ticks 2924418089 wait_resumed_ms_ticks 2924418089 task_bound_ms_ticks 2913313301 worker_created_ms_ticks 2890703855 exception_num 208 exception_severity 16 exception_address 0x00007FF846BBE030 affinity 64 state SUSPENDED start_quantum 2924418585 end_quantum 2924418589 last_wait_type ASYNC_NETWORK_IO return_code 258 quantum_used 478182 max_quantum 72 boost_count 1000 tasks_processed_count 64883 fiber_address NULL task_address 0x000000CE9B6F24E8 memory_object_address 0x00000113DF40C040 thread_address 0x000000E945570FA0 signal_worker_address 0x0000011BC9478160 scheduler_address 0x000000CEA23E0040 processor_group 0
Si vous avez des idées, nous sommes prenneurs.
Partager