[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSDB - Deadlock by process 15 and can't kill

Posted on 2014-03-13
4
Medium Priority
?
373 Views
Last Modified: 2014-04-04
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
Comment
Question by:holemania
  • 2
4 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 39927986
Any session id below 51 is a SQL internal process, so you never want to even try to kill it yourself.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 1000 total points
ID: 39935452
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
 

Author Comment

by:holemania
ID: 39937109
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
 

Author Closing Comment

by:holemania
ID: 39978203
Thanks.  Found out the issue is with SQL agent.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question