Question How can I determine which SQL transactions are currently running, and what query they are executing?
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
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)
Imported Document ID: HOWTO7251
Subscribing will provide email updates when this Article is updated. Login is required.