Viewing locks with sp_familylock

sp_familylock displays the locks held by a family. This example shows that the coordinating process (fid 51, spid 51) holds a shared intent lock on each of four tables and a worker process holds a shared page lock:

sp_familylock 51
fid    spid   loid        locktype                    table_id     page
      row    dbname           class                             context
-----  -----  ----------  --------------------------  ----------   ----------
    -----    ---------------  ---------------------        ---------------
   51     23         102                     Sh_page   208003772          945
        0             sales
   51     51         102                   Sh_intent    16003088            0
        0             sales                 Fam dur
   51     51         102                   Sh_intent    48003202            0
        0             sales                 Fam dur
   51     51         102                   Sh_intent   176003658            0
        0             sales                 Fam dur
   51     102                              Sh_intent   208003772            0
        0             sales                 Fam dur

You can also specify two IDs for sp_familylock.