monLockTimeout

Description

Provides information about lock timeouts. Each row identifies the object on which a blocked lock request occurred, and identities of the blocked and blocking processes.

You must enable the enable monitoring, lock timeout pipe active, and lock timeout pipe max messages configuration parameters for monLockTimeout monitoring table to collect data.

Columns

The columns in monLockTimeout are:

Name	Datatype	Attributes	Description
InstanceID	tinyint		(Cluster environments only) ID of an instance in a cluster.
LockWaitPeriod	int		Configured amount of time processes wait before a timeout occurs.
LockTimeoutLevel	varchar (20)	Null	Timeout level. One of: DTM_SERVER SERVER SESSION COMMAND INVALID
ObjectDBID	int		Unique database identifier for database in which the object resides.
ObjectDBName	varchar(30)	Null	Name of database in which the object resides.
ObjectID	int		Unique identifier for the object.
ObjectName	varchar(255)	Null	Name of the object.
PageNumber	int		Page number requested for the lock, if applicable.
RowNumber	int		Row number requested for the lock, if applicable.
ExpiredAtTime	datetime		Time when lock expires.
HeldSPID	int		Server process ID (spid) of process holding the lock.
HeldKPID	int		Kernel process ID (kpid) of process holding the lock.
HeldUserName	varchar(30)	Null	Name of the user for whom the lock is held.
HeldApplName	varchar(30)	Null	Name of the application holding the lock.
HeldHostName	varchar(30)	Null	Name of the host machine on which the application that executed the query holding the lock is running.
HeldClientName	varchar(30)	Null	Value of the clientname property set by the application holding the lock.
HeldClientApplName	varchar(30)	Null	Value for the clientapplname property set by the application holding the lock.
HeldClientHostName	varchar(30)	Null	Value for the clienthostname property set by the application holding the lock.
HeldTranName	varchar(255)	Null	Name of the transaction that acquired the lock.
HeldCommand	varchar(30)	Null	Category of process or command the process was executing when the process was blocked.
HeldFamilyID	int		spid of the parent process holding the lock.
HeldProcDBID	int		Unique identifier for the database where the stored procedure that caused the lock to be held resides, if applicable.
HeldProcDBName	varchar(30)	Null	Name for the database where the stored procedure that caused the lock to be held resides, if applicable.
HeldProcedureName	varchar(255)	Null	Name for the stored procedure that caused the lock to be held, if applicable.
HeldBatchID	int		Identifier of the SQL batch executed by the process holding the lock when the lock timeout occurred.
HeldContextID	int		Unique context identifier for the process holding the lock when it was blocked by another process (not when it acquired the lock).
HeldLineNumber	int		Line number in the SQL batch of the SQL statement holding the lock.
HeldStmtNumber	int		Statement number in the SQL batch of the SQL statement holding the lock.
HeldLockType	varchar(20)	Null	Type of lock. One of: Exclusive table Shared table Exclusive intent Shared intent Exclusive page Shared page Update page Exclusive row Shared row Update row Next key Exclusive address Shared address Semaphore
HeldNumLocks	int		Number of locks currently held by holding spid.
HeldNumTimeoutsCausedByTran	int		Number of timeouts caused by this holding transaction.
HeldNumTimeoutsCausedByLock	int		Number of timeouts caused by this lock resource.
HeldSourceCodeID	varchar(30)	Null	Location of the source code where the lock being held was acquired (internal use only).
WaitSPID	int		spid of the process waiting for the lock.
WaitKPID	int		kpid of the process waiting for the lock.
WaitUserName	varchar(30)	Null	Name of the user for whom the lock is being requested.
WaitApplName	varchar(30)	Null	Name of the application waiting for the lock.
WaitHostName	varchar(30)	Null	Name of the host running the process waiting for the lock.
WaitClientName	varchar(30)	Null	Value of the clientname property set by the application waiting for the lock.
WaitClientApplName	varchar(30)	Null	Value of the clientapplname property set by the application waiting for the lock.
WaitClientHostName	varchar(30)	Null	Value of the clienthostname property set by the application waiting for the lock.
WaitTranName	varchar(255)	Null	Name of the transaction in which the lock was requested.
WaitCommand	varchar(30)	Null	Category of process or command that the process was executing when it was blocked and then timed out.
WaitFamilyID	int		spid of the parent process waiting for the lock.
WaitProcDBID	int		Unique identifier for the database in which the stored procedure waiting for the lock resides, if applicable.
WaitProcDBName	varchar(255)	Null	Name for the database where the stored procedure that is waiting for the lock resides, if applicable.
WaitProcedureName	varchar(255)	Null	Name for the stored procedure waiting for the lock, if applicable.
WaitBatchID	int		Identifier of the SQL batch executed by the process waiting for the lock when the lock timeout occurred.
WaitContextID	int		Unique context identifier for the process waiting for the lock when it was blocked by another process.
WaitLineNumber	int		Line number of the SQL statement in the SQL batch waiting for the lock.
WaitStmtNumber	int		Line number in SQL batch waiting for the lock.
WaitLockType	varchar(30)	Null	Type of lock. One of: Exclusive table Shared table Exclusive intent Shared intent Exclusive page Shared page Update page Exclusive row Shared row Update row Next key Exclusive address Shared address Semaphore
WaitNumTimeoutsCausedByTran	int		Number of timeouts caused by a waiting transaction.
WaitSourceCodeID	int		Location in the source code when the timeout occured and the waiting lock request was made (for internal use only).
HeldProcedureID	int		Unique object identifier for the stored procedure that the blocking process was executing when the timeout occurred
WaitProcedureID	int		Unique object identifier for the stored procedure that is waiting for the lock, if applicable