How to find query\text of an OPEN Transactions ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

How to find query\text of an OPEN Transactions ?

Author

Topic

Martyn123
Starting Member

29 Posts

Posted - 2011-01-24 : 08:06:37

Hi,

I have a DB in which some transaction remains open that is increasing the size of differential backups , I had to kill those transaction frequently , I want to drill down to the transaction and find out why it is remaining open so I am running some DMVs to achieve this task but to no avail. Following are the commands :

DBCC OPENTRAN

Oldest active transaction:
SPID (server process ID): 22s
UID (user ID) : -1
Name : user_transaction
LSN : (8564:1851167:3)
Start time : Jan 21 2011 11:58:20:107AM
SID : 0x01

When I executed SELECT * FROM sys.dm_tran_database_transactions

trans_id db_trans_begin_time database_id db_trans_type
23705894 2011-01-21 11:58:20.133 2 (tempdb) 1
23705894 2011-01-21 11:58:20.107 7 (userdb) 1

but when I executed select * from sys.dm_tran_session_transactions
I am not getting any resultset(no rows).

actually I wanted to join above table with following tables

sys.[dm_exec_sessions] s_es
ON s_es.[session_id] = s_tst.[session_id]
JOIN sys.dm_exec_connections s_ec
ON s_ec.[session_id] = s_tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests s_er
ON s_er.[session_id] = s_tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (s_ec.[most_recent_sql_handle])

to get the text of that open transaction but the table "sys.dm_tran_session_transactions" is empty I dont understand why am I getting this table empty when I have an open transaction.

My only aim is to find that query which is making this transaction remain open for days which is causing my backup size increasing.

Please help me out in resolving this issue.

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-24 : 09:37:22

DBCC INPUTBUFFER(spid)

Martyn123
Starting Member

29 Posts

Posted - 2011-01-24 : 23:48:45

Hi,Russel

Thank you very much for replying to my post.

I have executed DBCC OPENTRAN

Oldest active transaction:
SPID (server process ID): 23s
UID (user ID) : -1
Name : user_transaction
LSN : (8665:2025849:1)
Start time : Jan 24 2011 7:37:05:027PM
SID : 0x01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As you have suggested I have executed

DBCC INPUTBUFFER(23)

EventType Parameters EventInfo
No Event 0 NULL

Even though one transaction is open with spid=23 why the above command is showing no resultset ?

Can anybody please help me out in resolving this issue.

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-25 : 11:30:17

What is listed in the command column for that SPID if you execute sp_who2?

I haven't seen a system SPID keep an open transaction before. Is your tempdb full perhaps?

udayk_36
Starting Member

2 Posts

Posted - 2011-05-09 : 03:08:15

Hi Team,

I have the same issue, with DBCC OPENTRAN, I am not getting the SPID.

Here is the result,
Transaction information for database 'iPlan_AmbaCOL'.

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (89755:86:19)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Kindly let me know how to get the SPID

Regards,
Uday

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-09 : 03:20:10

Try this may help to find OPENTRAN if you are using SQL 2005 Or 2008

SELECT s.SPID,S.OPEN_TRAN,TEXT,s.Hostname,s.nt_domain,nt_username,net_address,s.loginame FROM SYS.SYSPROCESSES S CROSS APPLY SYS.DM_EXEC_SQL_TEXT(S.SQL_HANDLE)AS A WHERE OPEN_TRAN=1

In Love... With Me!

udayk_36
Starting Member

2 Posts

Posted - 2011-05-10 : 01:39:14

Hi Raghuveer,

Many thanks for the reply.
There are no OPEN_TRAN with 1.

The log file is growing huge, its currently 28 GB and I am not able to shrink the log.
Tried with shrinking, recreating the replication with no desired result.
Kindly let me know what needs to be done.

Options tried.
DBCC SHRINKFILE
Shrinking from Management Studio after setting the recovery model to simple
Recreated the replication

Thanks and Regards,
Uday

Regards,
Uday

Bruce Sherwood
Starting Member

12 Posts

Posted - 2012-10-31 : 13:15:57

So, I too was running into something like this, and thought I would share what I have drummed up.

My problem: A TempDB that was getting out of control size wise. Consuming far to much disk and unshrinkable. Basically it was 40+ gigs, but had over 25 gigs of free space.

A quick look at DBCC OPENTRAN showed me I had an open transaction that had been up for 3 days. I killed that off, and suddenly the space reserved in TempDB was free. I could now shrink the database as I saw fit.
(note: It hosts a 24*7 application - so I can't just stop and restart SQL Server to force it to just throw away TempDB and start with a fresh small file)

The real trick for me is the fact this was a long running transaction that was (in theory) stuck in a massive sort operation. Most likely a really bad join. So I need to keep an eye open for that situation occurring again. Taking a couple of the DMVs for transactions, here is what I have come up with so far. It tuned specifically to look at TempDB. It's not pretty, but it seems to work. This can form the basis for an SQL Agent job that you could have alert you of a potential issue.

select dtl.request_session_id as [Session ID], tdt.database_transaction_begin_time as [Started At],
tat.name as [Action], DATEDIFF(mi,tdt.database_transaction_begin_time,GETDATE()) as [Running Minutes],
tdt.database_transaction_log_bytes_used as [Log Space Used bytes)], tdt.database_transaction_log_record_count,
case tdt.database_transaction_state when 1 then 'Uninitialized' when 3 then 'No Log Records' when 4 then 'Has log records'
when 5 then 'Prepared' when 10 then 'Committed' when 11 then 'Rolled Back' when 12 then 'Committed' end as [Transaction State]

from sys.dm_tran_database_transactions as tdt
left join sys.dm_tran_locks as dtl on tdt.transaction_id = dtl.request_owner_id
left join sys.dm_tran_active_transactions as tat on tdt.transaction_id = tat.transaction_id
where dtl.request_owner_type ='TRANSACTION'
and db_name(tdt.database_Id) like 'tempdb'
and tdt.database_transaction_begin_time is not null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources