It is generally a good practice -during dev work to view the impact that your components could have your application database. Of the many analysis that could be done in this regard , one such metrics is the monitoring of locks. SQL provides one such stored proc called the sp_lock. In using this, I have to filter alot “garbage” (things that I would not like to view) just to view my relevant information.
The sp_lock returns this massive list of database ids, resources, etc. In reality, all I’m intrested in – is to view the process names that create locks on my database. Here is one such handy stored procedure to do just that 
– I’m not claiming that the sp_locks is better or worse than the one below. (fingers crossed).
1) Create the following stored procedure (SQL scipts listed below)
2) Optionally, include it in your master database. This would allow you to run it from any database context and retreive information relative to your database context
USE MASTER EXECUTE sp_ms_marksystemobject 'sp_DatabaseLocks'
3) View your database locks using the command
sp_DatabaseLocks ‘MyCustomDatabase’
SQL Script:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_DatabaseLocks]
@IncomingDatabaseName sysname = NULL,
@IncomingObjectName sysname = NULL
WITH RECOMPILE
AS
DECLARE @sqlCommand varchar(1000)
DECLARE @DatabaseName sysname
SELECT @DatabaseName = ISNULL(@IncomingDatabaseName, DB_NAME())
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases
WHERE Name = @DatabaseName)
BEGIN
RAISERROR(15010,1,1,@DatabaseName) WITH SETERROR
RETURN 15010
END
SELECT @sqlCommand =
‘ SELECT convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
so.Name As ObjectName,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8 ) As Mode,
substring (x.name, 1, 5) As Status
FROM master.dbo.syslockinfo sli
JOIN master.dbo.spt_values v
ON sli.rsc_type = v.number
AND v.type = ”LR”
JOIN master.dbo.spt_values x
ON sli.req_status = x.number
AND x.type = ”LS”
JOIN master.dbo.spt_values u
ON sli.req_mode + 1 = u.number
AND u.type = ”L”
JOIN master.dbo.sysdatabases sd
ON sli.rsc_dbid = sd.dbid
JOIN ‘ + @DatabaseName + ‘.dbo.sysobjects so
ON sli.rsc_objid = so.id
WHERE sd.name = ”’ + @DatabaseName + ””
IF NOT (@IncomingObjectName IS NULL)
SELECT @SQLCOMMAND = @SQLCOMMAND + ‘ AND so.Name = ”’ + @IncomingObjectName + ””
SELECT @SQLCOMMAND = @SQLCOMMAND + ‘ ORDER BY spid’
IF @IncomingObjectName IS NULL
PRINT ‘Resource Locks for Database ‘ + @DatabaseName
ELSE
PRINT ‘Resource Locks for Database ‘ + @DatabaseName + ‘, Resource ‘ + @IncomingObjectName
EXEC (@sqlCommand)
RETURN @@ERROR
Happy monitoring
