Suite à du traitement d'un job on a constaté un blocage du rollback qui nous a amené à arrêter la production pour relancer le serveur. Autrement dit, pas cool.
La bonne nouvelle c'est que l'opération a été salutaire et n'a pas engendré de délais de récupération. Faut dire aussi que c'était sur une table temporaire et qu'en cas de redemmarrage normalement ça fait le ménage.

Ma demande est mutliple :
1- Pourquoi est-ce apparu / Comment éviter que ça se reproduise ?
2- Quels sont les bons réflexes à avoir, les trucs à faire en telle situation ?

Maintenant le détail:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
/* detection de l'anomalie */
select * from sys.dm_exec_requests as der
where command IN ('killed/rollback','rollback')
 
/* vérification delà où on est*/
kill 259 with statusonly
	/* pas capturé la réponse qui donnait :0% de progression */
 
/* détail sur le verrou */
SELECT * FROM  sys.dm_tran_locks where request_session_id = 259
/*
resource_type	resource_subtype	resource_database_id	resource_description	resource_associated_entity_id	resource_lock_partition	request_mode	request_type	request_status	request_reference_count	request_lifetime	request_session_id	request_exec_context_id	request_request_id	request_owner_type	request_owner_id	request_owner_guid	request_owner_lockspace_id	lock_owner_address
DATABASE		45	                                                                                                                                                                                                                                                                	0	9	S	LOCK	GRANT	1	0	259	0	0	SHARED_TRANSACTION_WORKSPACE	0	00000000-0000-0000-0000-000000000000	0x000000E91C689650:0:0	0x0000012085ADBC00
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	0	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000ECCD1195C0
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	1	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000011F334A88C0
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	2	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000012EF46C3940
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	3	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000F02A91CC00
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	4	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000001292AD4D140
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	5	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000FE30448A40
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	6	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000011E1DD52680
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	7	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000F837FB0B80
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	8	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000F4D8FB4A80
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	9	X	LOCK	GRANT	1	33554432	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000F02E6290C0
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	10	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000E958CD7740
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	11	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000F8E06874C0
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	12	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x00000127FB0D3940
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	13	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x00000124D3666940
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	14	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000F6FDAFFFC0
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	15	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000E99E5FE500
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	7	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000F4D8FB40C0
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	6	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000012A9582E200
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	5	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x00000122B5E4DC40
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	4	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000EA4DDB7F80
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	3	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000F74920B840
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	2	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000001533BC6B200
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	1	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000EBB539E700
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	0	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000011E0C628E00
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	0	Sch-M	LOCK	WAIT	1	536870912	259	0	0	TRANSACTION	122218003151	00000000-0000-0000-0000-000000000000	0x000000E91C689650:4:1	0x000000E943497700
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	15	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x00000136EFE69640
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	14	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000001247BD6D240
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	13	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000012CA6455C80
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	12	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000015292362F40
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	11	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000012ED7B71500
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	10	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x00000151FF3E8A80
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	9	Sch-M	LOCK	GRANT	1	33554432	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000015445C27DC0
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	8	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000015455305BC0
 
/* détail sur l'attente en elle même */
SELECT * FROM  sys.dm_tran_locks  DTL
left JOIN sys.dm_os_waiting_tasks DOWT 
	on DTL.lock_owner_address = dowt.resource_address
where dtl.request_session_id = 259
and DTL.resource_lock_partition = 0
 
/*
resource_type	resource_subtype	resource_database_id	resource_description	resource_associated_entity_id	resource_lock_partition	request_mode	request_type	request_status	request_reference_count	request_lifetime	request_session_id	request_exec_context_id	request_request_id	request_owner_type	request_owner_id	request_owner_guid	request_owner_lockspace_id	lock_owner_address	waiting_task_address	session_id	exec_context_id	wait_duration_ms	wait_type	resource_address	blocking_task_address	blocking_session_id	blocking_exec_context_id	resource_description
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	0	Sch-M	LOCK	WAIT	1	536870912	259	0	0	TRANSACTION	122218003151	00000000-0000-0000-0000-000000000000	0x000000E91C689650:4:1	0x000000E943497700	0x00000102CD6FACA8	259	0	119337939	LCK_M_SCH_M	0x000000E943497700	0x00000102CD6FACA8	259	NULL	objectlock lockPartition=0 objid=-1180941362 subresource=FULL dbid=2 id=lock10084fdd400 mode=Sch-M associatedObjectId=-1180941362
OBJECT		2	                                                                                                                                                                                                                                                                	-1340941932	0	X	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x000000ECCD1195C0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
OBJECT		2	                                                                                                                                                                                                                                                                	-1180941362	0	Sch-M	LOCK	GRANT	0	536870912	259	0	0	TRANSACTION	122217983458	00000000-0000-0000-0000-000000000000	0x000000E91C689650:3:1	0x0000011E0C628E00	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
*/
 
 
/*
analyse de ce qu'on peut déduire de :
objectlock lockPartition=0 objid=-1180941362 subresource=FULL dbid=2 id=lock10084fdd400 mode=Sch-M associatedObjectId=-1180941362
*/
select * from sys.objects as O
where O.object_id = -1180941362
/* l'objet n'existe pas dans cette table */
 
SELECT OBJECT_NAME([object_id]), p.*
    FROM sys.partitions P
    WHERE partition_id = -1180941362
/* rien là non plus*/
Merci de vos lumières