Apr 25, 2012
tom

Deriving the SQL query from sys.syslockinfo

Question

With the following query I can find processes on the dataserver

select *,db_name(rsc_dbid) as db_name from sys.syslockinfo (nolock)

Can I derive the SQL query from processes that are blocked?

Specifically, we get orphaned, blocking processes (with a req_spid of -2) but we don’t know why.

Asked by Synesso

Answer

Have a look at sp_WhoIsActive from Adam Machanic. It tells you what’s running, what’s blocking, and it derives the SQL text from those queries.

Answered by Randolph West

Related posts:

  1. What is the best way to determine what’s locking up SQL Server using a query?
  2. does SQL server have the equivalent of MySQL query logs
  3. Run SQL query from cron
  4. T-SQL query to schedule the back-up by using Transactional logs!sql server 2008
  5. Hiding SQL Server 2005 system objects/schemas from Microsoft Query

Leave a comment