Blocking Query

Question

Blocking Query

Minnu

SSCertifiable

Points: 6451
More actions
July 31, 2013 at 2:02 am

#278154

Hi Team,
I used to run 'sp_who2' to check the locks, i found that some sp_id are showing in BlkBy column, and in command column it is showing as 'Select'
I to find the full query, which is blocked.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

one method is to use dbcc inputbuffer

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Minnu SSCertifiable Points: 6451 More actions · Answer 2

hi,

Using DBCC INPUTBUFFER(<spid>)

but am not getting the full query.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Is your query running a loop, cursor or does it have multiple batches within it?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Minnu SSCertifiable Points: 6451 More actions · Answer 4

Hi Jason,

there is no Loop and cursors, it is a simple select query, is there any other way (without using 3rd party tools) to get the full query which is Blocked.

Please suggest.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

You can try the query in this article

http://www.sqlservercentral.com/articles/sp_who2/70222/

You can also try sp_whoisactive (link in the discussion on that article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Minnu SSCertifiable Points: 6451 More actions · Answer 6

Thank You Jason,

i'll check the link and revert back if any queries.