• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

MSDB - Deadlock by process 15 and can't kill

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.

SELECT DISTINCT

        name AS database_name, 

        session_id,

        host_name,

        login_time,

        login_name,

        reads,

        writes

FROM    sys.dm_exec_sessions

        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'

Open in new window


Database_name = msdb
Session_Id = 16
Host_name = NULL
Login_Time = 2014-03-13 09:07:55.250
Login = sa
Read = 0
Writes =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?
0
holemania
Asked:
holemania
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
Any session id below 51 is a SQL internal process, so you never want to even try to kill it yourself.
0
 
lcohanDatabase AnalystCommented:
What SQL Service Pack are you using?
Can you try add WITH (NOLOCK) to the tables and views above - I know, they are system DM_ views yet still give it a try... something like:


SELECT DISTINCT * FROM
(SELECT

        name AS database_name,

        session_id,

        host_name,

        login_time,

        login_name,

        reads,

        writes

FROM    sys.dm_exec_sessions WITH (NOLOCK)

        LEFT OUTER JOIN sys.dm_tran_locks WITH (NOLOCK) ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id

        INNER JOIN sys.databases WITH (NOLOCK) ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id

WHERE   resource_type <> 'database'
            and name = 'msdb'
            
) Q;
0
 
holemaniaAuthor Commented:
Turns out it's something going on with the SQL Agent.  There are a few backup jobs on there, but other than that, that's it.  Once I shut down SQL Agent, CPU usage drop.  Will have to figure why.
0
 
holemaniaAuthor Commented:
Thanks.  Found out the issue is with SQL agent.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now