How to analyze current SQL transactions

book

Article ID: 180960

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
How can I determine which SQL transactions are currently running, and what query they are executing?

Answer

List the oldest live transaction

This command will identify the oldest running transaction for the target database. This is useful when the SQL transaction log has recently had a drastic increase in size. Run the command a couple of times to ensure that the same transaction is still executing (compare the LSN). Make note of the SPID for use in the next section.

--SQL 2000 & SQL 2005
USE Altiris
GO
dbcc opentran


Sample output:

Transaction information for database 'Altiris'.
Oldest active transaction:
    SPID (server process ID): 56
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (55:189:1)
    Start time    : Apr 25 2007  9:38:16:463AM
    SID           : 0x010500000000000515000000c835dcc4da5b12f1fb711052eb030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



Show the query (2005)

This command will display the first 8,000 characters of the currently executing query for the specified  SPID (server process ID). 

--SQL 2005 only
 DECLARE @spid int
-- Don't forget to change the value of  @spid below
SET @spid = 56

DECLARE @handle binary(20)
SELECT @handle = sql_handle
FROM master..sysprocesses
    WHERE spid = @spid
SELECT [text]
    FROM ::fn_get_sql(@handle)

Show the query (SQL 2000 & 2005)

This command will display the first portion of the currently executing query for the specified  SPID (server process ID). 
--SQL 2000 and 2005
 DECLARE @spid int
-- Don't forget to change the value of  @spid below
SET @spid = 56

dbcc inputbuffer (@spid)


Note: By default, the SQL GUI tools will limit output to the first 256 characters per column (in text mode). To change for Studio 2005: 

Tools > Options > Query Results > Results to Text > Maximum # of characters displayed in each column
Tools > Options > Query Results > Results to Grid > Maximum Characters Retrieved > Non XML data (default of 65,535 is acceptable)