Get lock information

SELECT resource_type, resource_associated_entity_id,
        request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
WHERE resource_database_id = 8

http://msdn.microsoft.com/en-us/library/ms190345.aspx