Viewing active locks in SQL Server

SQL Server has a built-in stored procedure to show all the locks being held on the current database.  It's called sp_lock, and its output looks like this.


spid dbid ObjId            IndId Type       Resource Mode Status
51 4 0                   0           DB                        S           GRANT
52 1 1131151075  0          TAB                        IS         GRANT
54 4 0                    0           DB                        S           GRANT

Yeah, right.  This is helpful, but frustrating.  I can see that I have two shared locks on database 4 and one intent shared table lock on object 1131151075.  But I definitely don't want to run several more queries to figure out what's really being locked, and more about the process itself that holds the locks.

Instead, I created a query that gives me the information I really need without executing more queries to figure out the owners and objects being locked.

My query uses the output of sp_lock itself with the OPENROWSET statement.  To use OPENROWSET, you have to enable Ad Hoc queries.  Here's the syntax.

     exec sp_configure 'Ad Hoc Distributed Queries', 1

     reconfigure
     go




Once you have ad hoc queries set up, here's my revised query for lock information


     with Lock_CTE ( [SpId], [DbId], [ObjId], [IndId], [Type], [Resource], [Mode], [Status])
     as 
     (
select * from OpenRowSet('SQLOLEDB', 'Server=(local);Trusted_Connection=yes;', 'exec sp_lock')                        )

     select d.name as 'Database', 
      o.name as 'Object',
      l.Mode as 'LockType',
      l.Type as 'Type',
      l.Status as 'Status',
      i.name as 'Index',
      l.SpId as 'SpId',
      sp.blocked as 'Blocked',
      sp.cmd as 'Command',
      sp.loginame as 'LoginName',
      sp.hostname as 'HostName',
      sp.cpu as 'Cpu',
      sp.memusage as 'MemoryUsage',
      sp.physical_io as 'PhysicalIO'
     from Lock_CTE l
     inner join sys.databases d on d.database_id = l.DbId
     inner join sys.objects o on o.object_id = l.ObjId
     inner join sys.sysprocesses sp on sp.spid = l.SpId
     inner join sys.indexes i on i.index_id = l.IndId and
      i.object_id = l.ObjId
     order by d.name, o.name, l.Type

The output of this query shows the actual names of the database and object being locked, as well as information about the process holding the lock.  The next time you have to diagnose locking behavior on your database, try this query instead.