Recently created a P2V with one of our database server. Noticed that there's been a deadlock that is consistently causing issue in the database MSDB. When I look it's either process 15 or 16 and I can't kill it. Seems like an application session or something.
Trying to figure out why this session kept on creating deadlock. When I run the query to pull information from this code, this is what I get.
name AS database_name,
LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type <> 'database'
and name = 'msdb'
Database_name = msdb
Session_Id = 16
Host_name = NULL
Login_Time = 2014-03-13 09:07:55.250
Login = sa
Read = 0
The other deadlock session has similar information, but the host_name list the database sql instance server. Which is SVRDB1, while session 16 is NULL.
Should it be NULL or should it be SVRDB1? Anyone came across this issue with the msdb causing deadlock?