Displaying Locking Information (Database Engine)

  • Article

Microsoft SQL Server 2005 provides several ways to get information about the current locking activity in an instance of the 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

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 SQL Server 2005 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, 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, use the sys.dm_tran_locks dynamic management view instead.