Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

SQL Server statements currently running with fn_get_sql

MSSQLTips author Greg Robidoux By:   |   Read Comments   |   Related Tips: More > Performance Tuning

Problem
If you have ever had the need to troubleshoot current processing that is occurring on your SQL Server there are a few different ways to see what is going on.  These options include:

  1. using Enterprise Manager and looking at current activity
  2. using the system stored procedures sp_who2 or sp_who
  3. or maybe 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. With Enterprise Manager you can double click on process and a window will pop up with the current batch or last batch that was run or with options 2 and 3 you can use DBCC INPUTBUFFER from Query Analyzer to see the same information, but both of these options only allow you to see the first 255 characters of the statement. 

If you are running a stored procedure with a few parameters, 255 characters is probably not a problem, but if a long statement is running most of the statement will not be displayed and therefore it makes it much tougher to troubleshoot the problem.

Solution
Another option is to use the "::fn_get_sql" statement.  This was introduced with SQL Server 2000 and allows you to see the entire command that is running.  Instead of limiting the output to 255 characters, the output is a Text data type and therefore the entire statement can be seen.

To use this command it is pretty similar to using DBCC INPUTBUFFER. All you need is the SPID that you want to collect the information on for the currently running process and run the command using Query Analyzer. 

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 retreived by getting the Process ID from Enterprise Manager or by running sp_who2 in Query Analyzer.

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
SELECT * FROM ::fn_get_sql(@Handle)
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

DBCC INPUTBUFFER (55)
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
 
Enterprise Manager Output

   
As you can see the output using ::fn_get_sql provides a lot more detail then DBCC INPUTBUFFER and what Enterprise Manager offers.

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


Last Update: 6/27/2006


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
Sponsor Information