SQL SERVER – Blocking, Deadlocks and troubleshooting

Different between LOCKING BLOCKING and DEADLOCK

Locking: is the act by the SQL Server engine of temporarily "claiming" some resource (row, page, table, etc.) The degree to which that resource is claimed or locked depends on the action being performed. Selects typically issue a "shared lock", which will let other shared locks be issued on that resource but will prevent "exclusive locks" from being issued.

Blocking: is simply when one process wants to use a resource that another process is already using and the in-place lock is incompatible with the desired lock (e.g., process A is updating a row and has an exclusive lock on it and process B wants to read that row... B cannot put a shared lock on the row due to A's exclusive lock, so B is "blocked" and must wait for A to finish).

Deadlocking: is when 2 processes engage in a 'deadly embrace'. That happens when each process has a lock on a resource that the other wants, so that they're waiting on each other (that is, they're "blocking" each other). Since neither can proceed until the other releases its lock on some resource, the SQL Engine will choose one of the processes to kill (known as a 'deadlock victim').

There are many different ways in SQL Server to identify a blocks and blocking process that are listed as follow:

- Activity Monitor
- SQLServer:Locks Performance Object
- DMVs
  - - sys.dm_exec_requests
    - sys.dm_tran_locks
    - sys.dm_os_waiting_tasks
- SQL Server Profiler Locks Event Category

SQL SERVER –Identify Blocking, deadlocks and troubleshooting.

How deadlocks work you can run the following code in the Northwind database.

SolutionThe only solution for handling deadlocks is to find the problem in your code and then modify your processing to avoid deadlock situations. The first thing you need to do is find the deadlock situations and then investigate the problemHere is the script we used to identify the blocking query.Check table leave blocking script :select object_name(P.object_id) as TableName,resource_type, resource_description from sys.dm_tran_locks L join sys.partitions P on L.resource_associated_entity_id = p.hobt_id
First Script USE [master]GOSELECT session_id ,blocking_session_id ,wait_time ,wait_type ,last_wait_type ,wait_resource ,transaction_isolation_level ,lock_timeout FROM sys.dm_exec_requestsWHERE blocking_session_id <> 0GO
Second Script:USE [MSSQLTIPS]SELECT db.name DBName,tl.request_session_id,wt.blocking_session_id,OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,tl.resource_type,h1.TEXT AS RequestingText,h2.TEXT AS BlockingTest,tl.request_mode FROM sys.dm_tran_locks AS tlINNER JOIN sys.databases db ON db.database_id = tl.resource_database_idINNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressINNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_idINNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_idINNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_idCROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2GO

In our case, we killed the blocking_session_id after carefully looking at the BlockingText; it was found to be not necessary at all. We killed the session using the following command:

KILL 66

There are a couple of ways of doing this. The first approach is to turn on the trace flag to find the deadlocks. This can be done with the following statement run in Query Analyzer.

DBCC TRACEON (1204)

When a deadlock occurs the information like the following will be captured in the SQL Server Error Log.

SQL Server Profiler

You use the SQL Server Profiler Locks event category to create a trace of events related to locks and deadlocks. You can choose one or more of these event classes:

1. Deadlock_Graph_Event_Class — Creates an XML description of deadlocks.
2. Lock:Acquired — Use in conjunction with Lock:Released to determine the types of locks being requested and the length of time they are retained.
3. Lock:Cancel — Use to determine which locks are cancelled.
4. Lock:Deadlock Chain — Use to determine the objects involved in a deadlock.
5. Lock:Deadlock — Use to determine the objects and applications involved in a deadlock.
6. Lock:Escalation — Reports information about locks that have been escalated to cover a larger resource. For example, when a row lock becomes a table lock.
7. Lock:Released — Use in conjunction with Lock:Acquired.
8. Lock:Timeout(timeout>0) — Provides information about locks that have timed out due to blocking issues.
9. Lock:Timeout — Provides the same information as Lock:Timeout (timeout>0), but includes timeouts where the duration was 0.

To do this using SQL Profiler, you will need to capture the Lock Events Lock:Deadlock and Lock:Deadlock Chain.

And also capture the ObjectId data column.

Server Side Trace

For a Server Side Trace the following additional information will need to collected to capture the deadlock information.

In addition, you will also need to capture this additional column to see what objects are part of the deadlock chain.

ColumnNumber Column Description

22 ObjectID System-assigned ID of the object.

The output from our trace would show the following information:

When you have a lot of information to go through it is easier to load the data into a SQL Server table and then query the data for the particular timeframe and SPIDs

DECLARE @lowDate AS datetime, @highDate AS datetime

SET @lowDate = '2012-07-11 20:19:17.000'

SET @highDate = '2012-07-11 20:21:18.999'

SELECT TextData, StartTime, EndTime, SPID, Duration, Reads, Writes, EventClass FROM TraceFile WHERE -- SPID IN (66) AND (StartTime BETWEEN @lowDate AND @highDate OR EndTime BETWEEN @lowDate AND @highDate OR StartTime < @lowDate AND EndTime > @lowDate) ORDER BY

StartTime

*************************************************************************************************************************************************************

Troubleshooting Log File Query

Command : Shrinks the Tempdb database

dbcc shrinkdatabase (tempdb, 'target percent')

Determine the desired size for the primary data file (tempdb.mdf), the log file (templog.ldf)

use tempdb

go

-- this command shrinks the primary data file

dbcc shrinkfile (tempdev, 'target size in MB')

go

-- this command shrinks the log file, look at the last paragraph.

dbcc shrinkfile (templog, 'target size in MB')

go

Note :(Below run the query take a full backup )

USE xrz

GO

DBCC SHRINKFILE(xrz_db_log, 1)

BACKUP LOG xrz_db WITH TRUNCATE_ONLY

DBCC SHRINKFILE(xrz_db_log, 1)

GO

(Shrink Tempdb logs)

use tempdb

go

DBCC SHRINKFILE (tempdev,5000)

go

DBCC SHRINKFILE (tempdev,truncateonly)

go

DBCC SHRINKDATABASE (tempdb,5000)

The following should give you some clues as to which table(s) consume most of the space in the data file(s) --

this will help you narrow down any transactions that are either taking a long time or repeatedly being left in limbo:

USE tempdb SELECT name FROM tempdb..sysobjects

SELECT OBJECT_NAME(id), rowcnt FROM tempdb..sysindexes WHERE OBJECT_NAME(id) LIKE '#%' ORDER BY rowcnt DESC

If you can't shrink the log, it might be due to an uncommitted transaction.

See if you have any long-running transactions with the following command

DBCC OPENTRAN -- or DBCC OPENTRAN('tempdb')

DBCC INPUTBUFFER(<SPID >)

DMV returns space allocation information for the files associated with tempdb--

SELECT SUM (user_object_reserved_page_count)*8 as usr_obj_kb,SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,SUM (version_store_reserved_page_count)*8 as version_store_kb,SUM (unallocated_extent_page_count)*8 as freespace_kb,SUM (mixed_extent_page_count)*8 as mixedextent_kb FROM sys.dm_db_file_space_usage

DMV tracks the allocation/deallocation of tempdb pages by the currently executing tasks (also called batches).

This is extremely useful when you are running out of space in tempdb.--

SELECT top 5 * FROM sys.dm_db_session_space_usage ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC

If the version store is not shrinking, it is likely that a long-running transaction is preventing

version store cleanup.

SELECT top 5 transaction_id, transaction_sequence_num,elapsed_time_seconds FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC

********************************************************************************************************

Remove specific DataBase Connection

use master

SET NOCOUNT ON

DECLARE @DBName varchar(50)

DECLARE @spidstr varchar(8000)

DECLARE @ConnKilled smallint

SET @ConnKilled=0

SET @spidstr = ''

Set @DBName = 'Speranza_Process'

IF db_id(@DBName) < 4

BEGIN

PRINT 'Connections to system databases cannot be killed'

RETURN

END

SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '

FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0

BEGIN

EXEC(@spidstr)

SELECT @ConnKilled = COUNT(1)

FROM master..sysprocesses WHERE dbid=db_id(@DBName)

END

******************************************************************************************************************************************************************

Bring Database Online/Offline

Go offline

USE master

GO

ALTER DATABASE YourDatabaseName

SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

Go online

USE master

GO

ALTER DATABASE YourDatabaseName

SET ONLINE

GO