September 7, 2010 at 6:55 am
Hi Experts,
Can anyone assist in troubleshooting one issue at my end.
Using dbcc inputbuffer (spid) will give the current command that the process is executing.
My question is how exactly we can find the exact query that the user has submitted to the sql server.
The reason i was looking at it was because of some heavy blockings on the server, i used sp_who2 and found an spid which is causing huge blocking on the server. But when i used dbcc inputbuffer it is showing as an Insert command (Might be an inserts into the tempDB) and it is not the exact Insert command because the application where user's use to connect has no insert function on it.
Is there any way to find the exact t-sql that the user has issued.
Regards,
Sandhya
September 7, 2010 at 7:04 am
as this is in 2005 forum..................
SELECT SDER.[session_id] as SPID,
CASE
WHEN SDER.[statement_start_offset] > 0 THEN
--The start of the active command is not at the beginning of the full command text
CASE SDER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
convert(char(200),SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647) )
ELSE
--The end of the active statement is not at the end of the full command
convert(char(200),SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2) )
END
ELSE
--1st part of full command is running
CASE SDER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
convert(char(200),RTRIM(LTRIM(DEST.[text])) )
ELSE
--The end of the active statement is not at the end of the full command
convert(char(200),LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1) )
END
END AS [executing statement]
-- DEST.[text] AS [full statement code]
FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST
WHERE SDER.session_id = 'your spid'
---------------------------------------------------------------------
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply