<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

HOW TO: Fix an SQL Server Deadlock?

Published on
3,274 Points
274 Views
Last Modified:
Avis Moonsamy
Technical Expert and Passionate Nature Photographer
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.

What is a deadlock?


Basically, it is a  conflict in which one process is waiting for another to release a resource. This is also known as a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn’t release it.


This can be a serious condition, not just for SQL Server as processes become suspended, but for the applications which rely on SQL Server as well.


How to Fix it?


To fix this issue, we will use the Activity Monitor to identify the blocking process and then if possible kill it.

Activity Monitor is a tool in SQL Server Management Studio that gives you a view of current connections, processes, and locks held on SQL Server resources.


To open Activity Monitor in SQL Server Management Studio, right-click the SQL Server instance name in Object Explorer and then select Activity Monitor.


To find a blocked process with Activity Monitor, expand Processes in Activity Monitor to view more details about the processes.


Next step is to identify the process that is waiting. To do this, click on the Blocked By tab. This will sort by the column and show you all the blocked processes.


To stop the blocking process, right-click it and choose Kill Process.


If the blocking process is not eliminated by using the Kill Process, your last resort will be to restart the server. A restart of the SQL Server will clear all blocking processes.

0
0 Comments

Featured Post

Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month