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.

Washington Park: The gem of Anacortes

It's Never Too Soon...

Our national dialog on gun violence needs some work.

Clearly, the status quo indicates that we have a problem compared to other developed nations.  There are those who would rather ignore the statistics, and those people use the statistics as evidence that we all need to be armed to deal with the problem.

I want to specifically address a concept that I hear frequently.  Those on the "unlimited" side of the Second Amendment discussion use the argument "When guns are illegal, only criminals will have guns".  While I don't believe in revoking rights to own non-military grade weapons, I want to examine this facetious claim in detail.

First of all, behind every slogan are specific claims.  This slogan includes the following assertions.

• Criminals are a danger to society. 
• Criminals are resourceful
• Criminals will not be restricted by laws prohibiting access to assault-grade weapons

So, what would we expect to be true if all these assertions are true?  What would we see in our society?

Well, we already have quite destructive portable weapons that are under restricted access.  Hand grenades, rocket propelled grenades, shoulder mounted missiles, bazookas, etc.  Yet, someone who wanted to quickly murder a group of people would be far more effective at taking out an entire building with one of these weapons rather than an assault rifle.   Much more efficient at creating shock and calamity.

So, where are our resourceful criminals looking for firepower on the black market?  Do we see schizophrenic community college dropouts blowing up city blocks with hand grenades? I submit that you don't.  The tragic violence in our country is perpetrated with legally accessible weapons.  Which leads me to a personal direct refutation of the final assertion.

Criminals (especially the borderline mentally ill kind) are as opportunistic and lazy as the rest of us.  If it takes extra effort or risk to acquire their means of wreaking havoc, they likely will not do it.