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:

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:

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