Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SS7,SS2K
Expertise: Advanced
May 5, 2001



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Advanced Lock Monitoring

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

Giuseppe Dimauro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date