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.
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)
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
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:
