Solved

MSDB - Deadlock by process 15 and can't kill

Posted on 2014-03-13
4
358 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 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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 39

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now