Systems Engineering and RDBMS

Getting current SQL statements using ::fn_get_sql()

Posted by decipherinfosys on February 26, 2008

In SQL Server, getting the current SQL statements that are being executed by the active sessions is quite easy. In prior versions, one either had to make use Enterprise Manager or had to use DBCC INPUTBUFFER (<spid>) in order to get that information. In newer versions, one can just make use of the ::fn_get_sql() function. This was first introduced in SQL Server 2000 and is much better than the DBCC command since there are no text limitations.

Using the cross apply operator that we had discussed in one of our previous blog posts, one can write up a simple script to show all the SQL commands that are being fired by the active sessions on an instance:

SELECT spid, db_name(s.dbid), text
FROM master.dbo.sysprocesses as s
cross apply ::fn_get_sql(s.sql_handle)

One Response to “Getting current SQL statements using ::fn_get_sql()”

  1. […] […]

Sorry, the comment form is closed at this time.

%d bloggers like this: