none
How do I see the full SQLl/Query text of a spid?

    Question

  • How do I see the full SQLl/Query text of a spid?

    I use different ways to get the query, the problem is it truncates the end, so I cannot see the entire query/sql text. Is there another way to a query/sql text that is being run?


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Tuesday, February 11, 2014 9:11 AM

Answers

  • IF @@TRANCOUNT > 0 COMMIT TRAN


    What i want to see is the entire batch.


    It means this was the last batch submitted by client. You can try below as well.

    SELECT   s.session_id, 
                r.status, 
                r.blocking_session_id                                 'Blk by', 
                r.wait_type, 
                wait_resource, 
                r.wait_time / (1000.0)                             'Wait Sec', 
                r.cpu_time, 
                r.logical_reads, 
                r.reads, 
                r.writes, 
                r.total_elapsed_time / (1000.0)                    'Elaps Sec', 
                Substring(st.TEXT,(r.statement_start_offset / 2) + 1, 
                        ((CASE r.statement_end_offset 
                            WHEN -1 
                            THEN Datalength(st.TEXT) 
                            ELSE r.statement_end_offset 
                            END - r.statement_start_offset) / 2) + 1) AS statement_text, 
                Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)), 
                        '') AS command_text, 
                r.command, 
                s.login_name, 
                s.host_name, 
                s.program_name, 
                s.last_request_end_time, 
                s.login_time, 
                r.open_transaction_count 
    FROM     sys.dm_exec_sessions AS s 
                JOIN sys.dm_exec_requests AS r 
                ON r.session_id = s.session_id 
                CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 
    WHERE    r.session_id != @@SPID 
    ORDER BY r.cpu_time desc, r.status, 
                r.blocking_session_id, 
                s.session_id 


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, February 11, 2014 10:02 AM
    Moderator

All replies

  • Hi,

    Try this.

    DBCC INPUTBUFFER(spid)

    Best Regards,

    Karthik

    Tuesday, February 11, 2014 9:13 AM
  • DBCC INPUTBUFFER will not give me entire query, if i run a procedure with multiple statements in it i can see only part of it.

    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Tuesday, February 11, 2014 9:19 AM
  • Praveen,

    check this:

    --method 1
    dbcc inputbuffer(65)
    
    --method 2
    declare @sql varbinary(max)
    select @sql=sql_handle 
    from sys.sysprocesses
    where spid=65
    select text from sys.dm_exec_sql_text(@sql) 

    Edited:

    65 is the spid for which we are getting the query..


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>



    Tuesday, February 11, 2014 9:23 AM
  • no luck..

    if i run above queries i would get partial result, for example if i run above queries with spid 68 in my environment i would get below result.

    IF @@TRANCOUNT > 0 COMMIT TRAN

    What i want to see is the entire batch.


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Tuesday, February 11, 2014 9:33 AM
  • Praveen,

    You will only get the current query which is running/the last query which was run under the spid.

    for example, in the case of the below query:

    --block 1
    --Having the sample table as sys.objects
    select *
    into #temp
    from sys.objects
    where name like '%sys%'
    
    exec sp_spaceused #temp
    
    drop table #temp
    
    -----------------------------------------------------------------
    -----------------------------------------------------------------
    
    --block 2
    declare @ii int
    set @ii=1
    while @ii <= 255 
    begin
    	print cast(@ii as varchar) + '  ---->   ' + char(@ii) 
    	set @ii=@ii+1
    end

    Scenario 1: Here if you select block1 code and execute just that, then the query for this SPID would only be that part not the whole - as only that was getting executed.

    Scenario 2: If you just press F5 for the whole text of the particular query window, the whole query would be displayed.

    Scenario 3: If the code had the calling of certain functions, and they are in execution when you run the query (to get query text of the particular SPID) then, you'd be getting the definition of the particular function instead.


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Tuesday, February 11, 2014 9:57 AM
  • IF @@TRANCOUNT > 0 COMMIT TRAN


    What i want to see is the entire batch.


    It means this was the last batch submitted by client. You can try below as well.

    SELECT   s.session_id, 
                r.status, 
                r.blocking_session_id                                 'Blk by', 
                r.wait_type, 
                wait_resource, 
                r.wait_time / (1000.0)                             'Wait Sec', 
                r.cpu_time, 
                r.logical_reads, 
                r.reads, 
                r.writes, 
                r.total_elapsed_time / (1000.0)                    'Elaps Sec', 
                Substring(st.TEXT,(r.statement_start_offset / 2) + 1, 
                        ((CASE r.statement_end_offset 
                            WHEN -1 
                            THEN Datalength(st.TEXT) 
                            ELSE r.statement_end_offset 
                            END - r.statement_start_offset) / 2) + 1) AS statement_text, 
                Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)), 
                        '') AS command_text, 
                r.command, 
                s.login_name, 
                s.host_name, 
                s.program_name, 
                s.last_request_end_time, 
                s.login_time, 
                r.open_transaction_count 
    FROM     sys.dm_exec_sessions AS s 
                JOIN sys.dm_exec_requests AS r 
                ON r.session_id = s.session_id 
                CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 
    WHERE    r.session_id != @@SPID 
    ORDER BY r.cpu_time desc, r.status, 
                r.blocking_session_id, 
                s.session_id 


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, February 11, 2014 10:02 AM
    Moderator
  • IF @@TRANCOUNT > 0 COMMIT TRAN


    What i want to see is the entire batch.


    It means this was the last batch submitted by client. You can try below as well.

    SELECT   s.session_id, 
                r.status, 
                r.blocking_session_id                                 'Blk by', 
                r.wait_type, 
                wait_resource, 
                r.wait_time / (1000.0)                             'Wait Sec', 
                r.cpu_time, 
                r.logical_reads, 
                r.reads, 
                r.writes, 
                r.total_elapsed_time / (1000.0)                    'Elaps Sec', 
                Substring(st.TEXT,(r.statement_start_offset / 2) + 1, 
                        ((CASE r.statement_end_offset 
                            WHEN -1 
                            THEN Datalength(st.TEXT) 
                            ELSE r.statement_end_offset 
                            END - r.statement_start_offset) / 2) + 1) AS statement_text, 
                Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)), 
                        '') AS command_text, 
                r.command, 
                s.login_name, 
                s.host_name, 
                s.program_name, 
                s.last_request_end_time, 
                s.login_time, 
                r.open_transaction_count 
    FROM     sys.dm_exec_sessions AS s 
                JOIN sys.dm_exec_requests AS r 
                ON r.session_id = s.session_id 
                CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 
    WHERE    r.session_id != @@SPID 
    ORDER BY r.cpu_time desc, r.status, 
                r.blocking_session_id, 
                s.session_id 


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Very helpful!
    Monday, November 27, 2017 9:23 AM