4

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
Improve this question
9
  • Please note that I have tried the following but none of them gives me the full SQL text:
    – PTL_SQL
    Dec 15, 2021 at 11:21
  • 1
    You can get exact text only while query is running, once query completed the only way to get is through some monitoring tool. I don't think there is any readily available functionality to store SQL text upon completion of execution.
    – Learning_DBAdmin
    Dec 15, 2021 at 11:40
  • @Learning_DBAdmin, thank you for your contribution. Yes, that is the issue I'm trying to investigate i.e. can I find the complete SQL text for a query that executed at a past point in time, and reported in error log?
    – PTL_SQL
    Dec 15, 2021 at 12:58
  • 1
    @GrantFritchey Just want to say how cool it is to find validation in something I said from someone such as yourself. Your book "SQL Server 2012 Query Performance Tuning" was my very first reference, oh some seven or so years ago, for learning about anything beyond writing basic CRUD queries in SQL Server (and specifically indexing) and is one I still keep on my desk and utilize for spreading knowledge to my colleagues. Sorry to fangirl, but what an awesome day and age we live in, to be able to cross paths. 🙂
    – J.D.
    Dec 16, 2021 at 3:40
  • 1
    Thanks for the kind words. I'm happy to help out any way I can.
    – Grant Fritchey
    Dec 16, 2021 at 13:23

1 Answer 1

Reset to default
5

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

  1. 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.

  2. 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.

  3. 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.

Improve this answer
3
  • @JD, thank your for your help always. My understanding is that the above 3 methods will only give info on a currently running query. Is that correct? My issue on this occasion is to find what had been run at a past time, where the SPID is logged in error log. Do you know if that can be done?
    – PTL_SQL
    Dec 15, 2021 at 13:00
  • @PTL_SQL I don't believe that can reliably be done unfortunately. Extended Events or Query Store are your best bets to setup and leave running continuously in the background (at the cost of minor performance implications - but IMO nothing to be concerned of), if you want to use a proactive solution like the above. Otherwise if you know when the query runs that you want to capture you can try to quickly run the Profiler during that timeframe instead.
    – J.D.
    Dec 15, 2021 at 14:24
  • 1
    @JD, thanks again. Yes, I need to get into learning extended events and Query Store. Once again, thanks for your help as always. :-)
    – PTL_SQL
    Dec 15, 2021 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.