Get Current Running Queries in SQL Server with fn_get

By: Greg Robidoux | Updated: 2020-08-14 | Comments (1) | Related: > Performance Tuning

Problem

If you have ever had the need to troubleshoot a process that is currently running in SQL Server there are a few different ways to see what query statement is being run. These options include:

using SQL Server Management Studio and Activity Monitor
using the system stored procedures sp_who2
querying the system tables directly

Using any of these methods allows you to see which connections are currently active as well part of the statement that is currently running, but they don't all show the entire contents of the statement.

Solution

Another option is to use the "::fn_get_sql" statement. This was introduced with SQL Server 2000, but still works.

Using fn_get_sql

This allows you to see the entire command that is running. Instead of limiting the output when using DBCC INPUTBUFFER.

To use this command, all you need is the SPID that you want to collect the information for and run the command.

In the following examples the SPID that we are investigating is 55, therefore this is what is used for both statements. The SPID can be retrieved by getting the Process ID from SSMS or by running the stored procedure sp_who2.

DECLARE @Handle binary(20) 

SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55

SELECT * FROM ::fn_get_sql(@Handle)

Here is the output we get from running the above.

SELECT *, Orders.OrderID AS Expr1, Orders.CustomerID AS Expr2, Orders.EmployeeID AS Expr3, Orders.OrderDate AS Expr4, Orders.ShipPostalCode AS Expr5, Orders.ShipCountry AS Expr6, Orders.ShipRegion AS Expr7, Orders.ShipCity AS Expr8, Orders.ShipAddress AS Expr9, Orders.ShipName AS Expr10, Orders.Freight AS Expr11, Orders.ShipVia AS Expr12, Orders.ShippedDate AS Expr13, Orders.RequiredDate AS Expr14, Customers.CustomerID AS Expr15, Customers.CompanyName AS Expr16, Customers.ContactName AS Expr17, Customers.ContactTitle AS Expr18, Customers.Fax AS Expr19, Customers.Phone AS Expr20, Customers.Country AS Expr21, Customers.PostalCode AS Expr22, Customers.Region AS Expr23, Customers.Address AS Expr24, Customers.City AS Expr25, [Order Details].OrderID AS Expr26, [Order Details].ProductID AS Expr27, [Order Details].UnitPrice AS Expr28, [Order Details].Quantity AS Expr29, [Order Details].Discount AS Expr30 FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

Using DBCC INPUTBUFFER

If we use DBCC INPUTBUFFER as shown below, this only returns part of the command.

DBCC INPUTBUFFER (55)

Using SSMS Activity Monitor

If you use Activity Monitor in SSMS, it will give you the same output as DBCC INPUTBUFFER and doesn't return the entire command. To do so, right click on one of the processes and select Details. This will show you the statement.

Conclusion

As you can see using this function can help return more of the query statement. In later versions of SQL Server the output of DBCC INPUTBUFFER has been increased, but it still cuts off some of the text.

Next Steps

When you are troubleshooting a SQL Server issue or a current running processes use this function to provide all the details
Add this tool to your SQL Server toolbox
Take a look at these related articles:
- Retrieve Actively Running T-SQL Statements from SQL Server
- How to isolate the current running commands in SQL Server

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-08-14

Get Current Running Queries in SQL Server with fn_get_sql