See currently running queries in SQL Server 2012

SELECT r.start_time [Start Time],session_ID [SPID],
DB_NAME(database_id) [Database],
CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
END) [Executing SQL],
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID — don’t show this query
AND session_id > 50 — don’t show system queries
ORDER BY r.start_time

This query is great at finding locking queries like the one below:

To fix this, either add “(NOLOCK)” table hint to allow the select to run while the update is happening or kill the offending process using “KILL” command followed by the SPID

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.