In order to assist continuous and effective monitoring of lock status, SQL Server offers the system stored procedure SP_LOCK, which can provide several useful pieces of information on how locks are set and removed.
However, using this information – such as process ID, database ID, and object ID – to determine the actual resources involved in the blocking transaction is much more difficult. The solution I illustrate here displays not only all the locked resources, but also the login and user name, and the name of the application, the process ID, and even the MAC address of the network card mounted on the machine where the host app which is “to blame” or which is the “victim” of the lock. Let’s more in detail how this information is retrieved.
The sp_ExBlockingLocks procedure illustrated below is an extension of an example contained in the book Inside MS-SQLServer 7.0 by Ron Soukup e Kalen Delany, improved to display all the extra data mentioned previously. This stored procedure retrieves the name of all the databases involved in the lock from the syslockinfo table (which contains all the most important data related to system locks) and from the sysdatabases table in the Master database. It retrieves the name of the involved table from the sysobjects table in current database. Finally, the following information is retrieved from the sysprocesses table in the master database:
hostname: La macchina su cui gira l’applicazione bloccante oppure bloccata.
program_name: Il nome della applicazione.
hostprocess: Rappresenta il process ID della applicazione in questione.
nt_domain: Il dominio da cui proviene la connessione.
nt_username: Il nome utente con cui
