Prev: Cannot Delete Maintenance Plans - SQL 2005
Next: Query Help for Newb
From: Rubens on 12 Jul 2010 10:01
Is there a simple way of determining the actual T-SQL being executed in the
current transaction_id?

The code below displays the active transaction_id, but I'd like to quickly
and easily see what it's actually running.

Thanks,
Rubens

-- create the temporary table to accept the results.
CREATE TABLE #OpenTranStatus (
ActiveTransaction varchar(25),
Details sql_variant
)
-- execute the command, putting the results in the table
INSERT INTO #OpenTranStatus
EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');

-- display the results
SELECT * FROM #OpenTranStatus;
GO

DROP TABLE #OpenTranStatus




From: Erland Sommarskog on 12 Jul 2010 16:32
Rubens (rubensrose(a)hotmail.com) writes:
> Is there a simple way of determining the actual T-SQL being executed in
> the current transaction_id?
>
> The code below displays the active transaction_id, but I'd like to quickly
> and easily see what it's actually running.

Simple and simple...

Before we go any further, be aware of that the process owning the
transaction may not be doing anything at all. The transaction could be
orphaned, that is someone failed to rollback or commit. It could be a
distributed transaction where work is going on in the other data source.
Or, God forbid, the application is waiting for user input.

But if the process is active, there is one or more rows in
sys.dm_exec_requests where session_id = OLDACT_SPID. (There will only
be more than one if someone is using MARS.) In this view you find
three columns: sql_handle, statement_start_offset and statement_end_offset.
These you can in turn use as input to sys.dm_exec_sql_text to get the
statement text.

There are not entirely trivial to use, but here is an expression that
I have:

CASE WHEN p.stmt_start >= 0
THEN substring(est.text, (p.stmt_start + 2)/2,
CASE p.stmt_end
WHEN -1 THEN datalength(est.text)
ELSE (p.stmt_end - p.stmt_start + 2) / 2
END)
END

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Rubens on 13 Jul 2010 10:58
Erland,

First off, thank-you for this information... it's very useful and much
appreciated.

In digging a little further, I realized that DBCC OPENTRAN is pretty much
replication specific. So your suggestions below are much better because
essentially I want to be able to see current open transactions and what they
are running, regardless of whether the database is replicated. We had an
incident over the weekend where a database log was blowing up and my other
methods of tracking down what was happening didn't give me the information I
wanted, and in turn lead to this post).

I've put the code together below with how I intend to use it. The only
question I have is around the case statement. It returns less information
than the actual 'text' column, so I am wondering if I've applied it
incorrectly. Either way, the text field seems to return what I need.

Thanks,
Rubens

-- get current active transaction info
select
--*
--st.dbid
r.database_id as [db_id]
,db_name(r.database_id) as database_name
--,db_name(st.dbid) as database_name
,st.objectid
,object_name(st.objectid, st.dbid) as [object_name]
,r.session_id
,r.user_id
,r.command
,st.text as sql_text
--,case when r.statement_start_offset > 0
--then substring ( st.text, ( r.statement_start_offset + 2 ) / 2,
-- case r.statement_end_offset
-- when -1 then datalength ( st.text )
-- else ( r.statement_end_offset - r.statement_start_offset + 2 ) / 2
-- end ) as sql_text
--end
--,st.number
--,st.encrypted
,r.start_time
,r.status
,r.statement_start_offset
,r.statement_end_offset
--,r.sql_handle
--,r.plan_handle
,r.blocking_session_id
,r.wait_type
,r.wait_time
--,r.wait_resource
,r.open_transaction_count
,r.transaction_id
,r.percent_complete
,r.estimated_completion_time
,r.total_elapsed_time
,r.cpu_time
,r.reads
,r.writes
,r.logical_reads
,r.text_size
,r.transaction_isolation_level
,r.row_count
--,r.query_hash
--,r.query_plan_hash
--select r.*
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text ( r.sql_handle ) st
-- where r.session_id = -- insert SPID here


"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DB3E56085A6FYazorman(a)127.0.0.1...
> Rubens (rubensrose(a)hotmail.com) writes:
>> Is there a simple way of determining the actual T-SQL being executed in
>> the current transaction_id?
>>
>> The code below displays the active transaction_id, but I'd like to
>> quickly
>> and easily see what it's actually running.
>
> Simple and simple...
>
> Before we go any further, be aware of that the process owning the
> transaction may not be doing anything at all. The transaction could be
> orphaned, that is someone failed to rollback or commit. It could be a
> distributed transaction where work is going on in the other data source.
> Or, God forbid, the application is waiting for user input.
>
> But if the process is active, there is one or more rows in
> sys.dm_exec_requests where session_id = OLDACT_SPID. (There will only
> be more than one if someone is using MARS.) In this view you find
> three columns: sql_handle, statement_start_offset and
> statement_end_offset.
> These you can in turn use as input to sys.dm_exec_sql_text to get the
> statement text.
>
> There are not entirely trivial to use, but here is an expression that
> I have:
>
> CASE WHEN p.stmt_start >= 0
> THEN substring(est.text, (p.stmt_start + 2)/2,
> CASE p.stmt_end
> WHEN -1 THEN datalength(est.text)
> ELSE (p.stmt_end - p.stmt_start + 2) /
> 2
> END)
> END
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
From: Erland Sommarskog on 13 Jul 2010 18:17
Rubens (rubensrose(a)hotmail.com) writes:
> I've put the code together below with how I intend to use it. The only
> question I have is around the case statement. It returns less information
> than the actual 'text' column, so I am wondering if I've applied it
> incorrectly. Either way, the text field seems to return what I need.

Not sure what problem you are seeing, but the idea is that if current
batch has multiple statement, you only want to see the current statement.

You may also be interested in beta_lockinfo, which is on my web site:
http://www.sommarskog.se/sqlutil/beta_lockinfo.html. This stored procedure
performs this work, and also extracts a lot more information, including
open transactions.


(And DBCC OPENTRAN is not replication-specific, but it only shows one
transaction.)

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Rubens on 19 Jul 2010 14:36
Hi Erland,

Thanks again for the info and the link. I will check it out, much
appreciated.

Rubens

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DB530CCBEC5Yazorman(a)127.0.0.1...
> Rubens (rubensrose(a)hotmail.com) writes:
>> I've put the code together below with how I intend to use it. The only
>> question I have is around the case statement. It returns less
>> information
>> than the actual 'text' column, so I am wondering if I've applied it
>> incorrectly. Either way, the text field seems to return what I need.
>
> Not sure what problem you are seeing, but the idea is that if current
> batch has multiple statement, you only want to see the current statement.
>
> You may also be interested in beta_lockinfo, which is on my web site:
> http://www.sommarskog.se/sqlutil/beta_lockinfo.html. This stored procedure
> performs this work, and also extracts a lot more information, including
> open transactions.
>
>
> (And DBCC OPENTRAN is not replication-specific, but it only shows one
> transaction.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 | 
Pages: 1
Prev: Cannot Delete Maintenance Plans - SQL 2005
Next: Query Help for Newb