Solved

MSDB - Deadlock by process 15 and can't kill

Posted on 2014-03-13
4
366 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 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 250 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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