How can I determine which SQL transactions are currently running, and what query they are executing?
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|
|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.
--SQL 2005 only
|--SQL 2000 and 2005|
DECLARE @spid int
-- Don't forget to change the value of @spid below
SET @spid = 56
dbcc inputbuffer (@spid)
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)