How to analyze current SQL transactions

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)