How to Find Blocking and Deadlock in SQL Server - SQL Server DBA Tutorial

In this video you will learn:

1. What is deadlock in SQL Server?
2. What is Blocking in SQL Server?
3. What is difference between deadlock and blocking in SQL Server?
4. How to create deliberate deadlock for learning purposes?
5. What are the deadlock traces in SQL Server?
6. How to turn on deadlock traces on and off in SQL Server?
7. How and where to check deadlock information in SQL Server?

Video also recommends to watch demo of "Understanding Locking in SQL Server" to learn locking and blocking in greater detail.

--Scripts: How to check blocking in SQL Server 
Exec sp_who;
GO
--How to turn on and off traces and check Traces on SQL Server
DBCC TRACESTATUS();
--How to check If Deadlock traces are enabled or disabled
DBCC TRACESTATUS(1204,1222, -1)
--How to Turn Deadlock Traces on SQL Server
DBCC TRACEON(1204,1222, -1)
--How to Turn deadlock Traces Off 
DBCC TRACEOFF(1204,1222, -1)


--How to create deliberate deadlock

-- Tran1

CREATE TABLE DemoDeadLock1 (SessionNumber INT)
INSERT DemoDeadLock1 SELECT 1

CREATE TABLE DemoDeadLock2 (SessionNumber INT)
INSERT DemoDeadLock2 SELECT 1

--Tran2 in new session
BEGIN TRAN
UPDATE  DemoDeadLock1 SET  SessionNumber= 1

--Tran3
BEGIN TRAN
UPDATE  DemoDeadLock2 SET SessionNumber = 1
UPDATE  DemoDeadLock1 SET SessionNumber = 1

--Let's update Demodeadlock2 table in our Tran2 session
UPDATE DemoDeadLock2 SET SessionNumber = 1

How to Find Blocking and Deadlock in SQL Server - SQL Server DBA Tutorial