By: Greg Robidoux | 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.
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:
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips