0

We have this recurring situation where several times a week our application stops responding. What I would like to do is be able to view the text of the query running on SQL Server.

I can use sp_who to see the open connections, but, it does not display the actual query text.

If I can see the query that is freezing my database I can have a starting point for optimization.

This happened a few minutes ago and our sys admin had to reboot the box. This rebooting is not sustainable.

What steps should I take?

I would like to see the actual text of the queries that are running on my server.

SQL Server 2000

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
OpenCoderX
  • 6,180
  • 7
  • 34
  • 61

2 Answers2

1

use this while the block is happening:

SELECT
    r.session_id AS spid
        ,r.cpu_time,r.reads,r.writes,r.logical_reads 
        ,r.blocking_session_id AS BlockingSPID
        ,LEFT(OBJECT_NAME(st.objectid, st.dbid),50) AS ShortObjectName
        ,LEFT(DB_NAME(r.database_id),50) AS DatabaseName
        ,s.program_name
        ,s.login_name
        ,OBJECT_NAME(st.objectid, st.dbid) AS ObjectName
        ,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 SQLText
    FROM sys.dm_exec_requests                          r
        JOIN sys.dm_exec_sessions                      s ON r.session_id = s.session_id
        CROSS APPLY sys.dm_exec_sql_text (sql_handle) st
    WHERE r.session_id!=@@SPID

this will list all active SPIDs, who is blocking them and the SQL of each SPID

EDIT
this query is for SQL Server 2005+, initial question did not state SQL Server 2000

KM.
  • 101,727
  • 34
  • 178
  • 212
  • I get this error 'Msg 174, Level 15, State 1, Line 5 The object_name function requires 1 arguments' when running the above sql – OpenCoderX Jan 13 '12 at 21:56
  • Also what is the white space after 'FROM sys.dm_exec_requests' and 'JOIN sys.dm_exec_sessions'? – OpenCoderX Jan 13 '12 at 21:57
  • sql server 2000? white space is so all the table alias values align, I find it easy to look at a lot of joins and quickly see all the alias values – KM. Jan 13 '12 at 21:57
  • SQL Server 2000, ouch ;-o, I can't remember anything from that far back! all the good methods and system views are for 2005+ (including my query) – KM. Jan 13 '12 at 21:59
  • Thank You. I will add this to my query arsenal, as we have a 2005 server also. What about the what space in the FROM and JOIN clauses? should those say 'AS r' and 'AS s'? – OpenCoderX Jan 13 '12 at 22:03
  • white space is so all the table alias values align, I find it easy to look at a lot of joins and quickly see all the alias values. The "AS" is optional when you alias a table. – KM. Jan 13 '12 at 22:04
  • In 2000 you can use `DBCC INPUTBUFFER` or `DBC PSS` can't remember if there were any better ways. – Martin Smith Jan 13 '12 at 22:06
0

See the article How to monitor blocking in SQL Server 2005 and in SQL Server 2000 for the definition of sp_blocker_pss08 (a SQL Server 2000 compatible script).

Martin Smith
  • 438,706
  • 87
  • 741
  • 845