Storeced procedure hangs

Once in a while the stored procedure hangs, How can I find which statement from stored procedure is timing out or making the SQL hang while process is hang.

sp_who2  can find the process ID but Is there any way I can find which statement within the stored procedure makes whole stored procedure hangs?
Bharat GuruAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can pass the spid to DBCC INPUTBUFFER (<spid>)

alternatively , you can get the SQLHandle, passing the spId to the below statement
SELECT SQL_HANDLE  
FROM MASTER..SYSPROCESSES  
WHERE SPID = @SPID    
 -- and run this statement using that sqlhandle
SELECT [TEXT] FROM ::FN_GET_SQL(@SQLHandle)
0
 
HugoHiaslCommented:
Another way is to use PRINT('text')  statements and run the stored procedure in SQL Management studio. In the "Messages" tab you can see what PRINT statement was executed last when it hangs.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Start a SQL Profiler just before you run the SP again and capture and single SQL Statements. You'll see which one is originating the issue.
0
 
Bharat GuruAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.