How to get complete SQL Text running of the query in a given SPID

Can anyone help me please, either by giving me the query or pointing me to an article, as I can't seem to find one.

I have the SPID of a session that raised an error and we need to know the full SQL text of the query that was run by that SPID.

Can anyone help please?

Please note that I have tried the following but none of them gives me the full SQL text:

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 174
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

SELECT
sysprc.spid,sysprc.waittime,sysprc.lastwaittype,DB_NAME(sysprc.dbid) AS database_name,
sysprc.cpu,sysprc.physical_io,sysprc.login_time,sysprc.last_batch,sysprc.status,
sysprc.hostname,sysprc.[program_name],sysprc.cmd,sysprc.loginame,
OBJECT_NAME(sqltxt.objectid) AS [object_name],sqltxt.text
FROM sys.sysprocesses sysprc 
OUTER APPLY sys.dm_exec_sql_text(sysprc.sql_handle) sqltxt
where spid = 174

DBCC INPUTBUFFER(174)
go

There are a few options available to monitor actively running queries which will provide the full query text:

-
SQL Server Profiler - This is the most deprecated methodology for tracing running queries, and the tool is a bit clunky and can sometimes be difficult in searching for the exact query you're looking for if you have a lot running on your SQL instance at one time. But it is also pretty simple to use, and even provides the values of parameters used in stored procedure queries that it captures in its trace.

-
Extended Events - This is the recommended replacement by Microsoft to the aforementioned Profiler. While a little bit more complicated of a learning curve, Extended Events provide more in-depth information that is better searchable.

-
Query Store - This is the most recent query monitoring feature released by Microsoft. Though usually intended to monitor query performance, it does provide the full text of the query as well, which can even be accessed in the sys.query_store_query_text sys view. Note this feature is only available in SQL Server 2016 and later.

Each of these features need to already be running though, for you to be able to capture the query. I don't believe there's a reliable way to retroactively find the full query text, if you weren't already using a feature or tool that was monitoring what is running.

StackExchange Database Administrators Q#303974, answer score: 5