Solved

MSDB - Deadlock by process 15 and can't kill

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL to Update Table Dynamically 2 61
Pivot Query Problem 9 49
CREATE DATABASE ENCRYPTION KEY 1 79
kill process lock Sql server 9 74
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

740 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