SQL Server - sys.dm_exec_requests Issue

Question

In SSMS 2008 I created a new query window and issued the following statement (notice I don't commit the transaction):

Begin Tran
Update Master.Part Set LastModifiedUser = 'me'

I then open another new query window and entered the following query:

Select * From sys.dm_exec_requests

The DMV does not show the query from the first query window. Anyone know why not?

Thanks.

Answer 1

Your UPDATE statement has technically completed, so it is no longer an active request, even though it is still holding locks and waiting for a COMMIT or ROLLBACK. You could instead query

SELECT * FROM sys.dm_tran_session_transactions

or

SELECT * FROM sys.dm_exec_sessions

to find your SPID.