• If you don't see it, then there's something in sp_whoisactive that's filtering out those locks. Probably it's only showing sessions that are running, not ones that are sleeping and holding locks.

    The lock is there from the time you connect to the database til the time you close the connection or change database context.

    Trivial to show too.

    Run in one window (and note the session_id)

    USE AdventureWorks

    Run in another window

    SELECT resource_type ,

    resource_database_id ,

    request_mode ,

    request_type ,

    request_status ,

    request_session_id ,

    request_owner_type

    FROM sys.dm_tran_locks

    WHERE request_session_id = <session id from the first window>

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass