Posts
27
Comments
58
Trackbacks
0
SQL Server - get last executed SQL statement
We all know the usage of sp_who2 command in SQL server, its life-saver when you need to know what's causing your query take longer time to execute or in other words who's blocking you r query.

But, SQL server has many in-built functions which lets you sneak a peek inside the sql server and we can use them to our advantage. One of them is 

DBCC INPUTBUFFER(SP_ID)

Using this function, we can see the last executed sql statement in that process id.

There are several ways to find out what is the latest run query from system table sys.sysprocesses.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext sql_handle
FROM sys.sysprocesses
WHERE spid 61
SELECT TEXT
FROM 
sys.dm_exec_sql_text(@sqltext)
GO



posted on Monday, March 4, 2013 3:32 PM Print
Comments
Gravatar
# re: SQL Server - get last executed SQL statement
Alex
3/6/2013 4:22 AM
Nice, very useful. Question, what permissions are required to run these commands?
Gravatar
# re: SQL Server - get last executed SQL statement
Vipin
3/6/2013 10:00 AM
You must be a member of fixed sysadmin server role
Gravatar
# re: SQL Server - get last executed SQL statement
Madhava
4/16/2014 11:10 AM
Very usefull one thanks for the post.

Post Comment

Title *
Name *
Email
Comment *  
Verification