[SQL]Concurrency-Lock:Timeout on SQL Server profiler

Recently, I’ve found one of my server has lots lock: timeout happened. I am curious what caused this? So I opened SQL Server Profiler to watch the server

The same session (77 or 117) had few lock:Timeout
What the thoughts popped out on my mind were:

Blocked, Blocking, Long query

If Long query, first I look at "Row_Count" and "Login_time", please use the scripts that i wrote before,

• Click here 

 

 

The result show the login session (102) has been running since one month ago.
It's unnormal, so ran the undocumented command "SP_WHO2 102" to see.

• Sp_who2

The command’s status is sleeping and “AWAITING COMMAND”, it means that the transaction for spid 102 has not committed or rolled back, and it still holds a lock on a table.

So if it’s the last statement sent from a client to SQL Server, I need to find out what’s the last statement for this SPID

• DBCC INPUTBUFFER

DBCC INPUTBUFFER
Displays the last statement sent from a client to Microsoft® SQL Server™.
You can use this command to see what was the longest running command which may point to the transaction that is causing the blocking.

Syntax
DBCC INPUTBUFFER (spid)

So I got the result from SPID 102




The session was doing a “Bulk Insert” and seems not “Roll Back” or “Commited” yet but already finished its own purposed job


Then, I want to Kill 102, but I have to find out if any locked happened

SELECT
    sp1.SPID AS BlockedSPID, sp2.SPID AS BlockingSPID
FROM 
    master..sysprocesses sp1
    JOIN
master..sysprocesses sp2 ON sp1.blocked = sp2.spid

So there was not any blocked happened. SO I am able to kill the longest session.

But for in case, in the mean time I need to check if there are “active open transactions” running
So I ran the DBCC command DBCC OPENTRAN, below is the official description:

• DBCC OPENTRAN

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

Syntax
DBCC OPENTRAN
( { 'database_name' | database_id} )
[ WITH TABLERESULTS
[ , NO_INFOMSGS ]
]

DBCC OPENTRAN(myDbName)

Ensure No one is running a transaction on the database, if yes, you need to make sure it’s not impact your longest session


Expended reading:
Finding the Causes of Poor Performance in SQL Server, Part 1
Finding the causes of poor performance in SQL Server, Part 2
DBCC OPENTRAN detect all open trans from the writer SQLWork

--Run that on every database on your server. 
DECLARE @SQLScript NVARCHAR(MAX)
SELECT @SQLScript = ISNULL(@SQLScript,'') + 'DBCC OPENTRAN('+ Name +');'
FROM sys.databases
EXEC (@SQLScript)