Displaying Locking Information (Database Engine)

SQL Server provides several ways to get information about the current locking activity in an instance of the SQL Server Database Engine.

Topic

Description

Locks Event Category

Using SQL Server Profiler, you can specify the locks event category to capture information about locking events in a trace.

SQL Server, Locks Object

In the System Monitor, you can specify counters from the locks object to monitor the level of locking in an instance of the Database Engine.

sys.dm_tran_locks (Transact-SQL)

You can query the sys.dm_tran_locks dynamic management view to obtain information about the current state of locking in an instance of the Database Engine.

EnumLocks

An application using the SQL Server Management Objects (SMO) API can get a list of the active locks in an instance of the Database Engine using the EnumLocks method of the Server class.

EnumLocks

An application using the SMO API can get a list of the active locks in a specific database using the EnumLocks method of the Database class.

As a backward compatibility feature, the Database Engine also supports the following ways to get information on locks that are available in earlier versions of SQL Server.

Topic

Description

EnumLocks Method

An application using the SQL Distributed Management Objects (DMO) API can get a list of the active locks in an instance of the Database Engine using the EnumLocks method of the SQLServer class. For SQL Server 2005, use the SMO EnumLocks method of the server object.

sp_lock (Transact-SQL)

This system stored procedure returns information about the active locks in an instance of the Database Engine. For SQL Server 2005 and later, use the sys.dm_tran_locks dynamic management view instead.

sys.syslockinfo (Transact-SQL)

This compatibility view returns information about the active locks in an instance of the Database Engine. For SQL Server 2005 and later, use the sys.dm_tran_locks dynamic management view instead.

See Also

Concepts