debugging an open transaction.

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 2000 Forums

SQL Server Administration (2000)

debugging an open transaction.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-19 : 09:01:12

Prasad Pai writes "Hi,

SQL Version: SQL Server 2000 MSDE
OS Version: Win XP

I didnt get much help on this thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59075
so tght of asking directly..

I want to know if there is a way we can debug wat all DML statements where executed in an transaction which is still open..

dbcc opentran gives only the spid ..
dbcc inputbuffer(@spid) doesnt give much info..
i tried ::fn_get_sql(@Handle) function but it is helpful
only when the spid is executing something when the function
is used..

i wud like 2 know what that spid executed but failed to
commit/rollback before killing it."

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-12-20 : 00:16:50

Hi,
use sql profiler instead to measure it.

:-)
Regards

Andy Davis
Activecrypt Team
--------------------------------------------
SQL Server Encryption Software
http://www.activecrypt.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-20 : 00:56:02

See if this helps
http://www.mindsdoor.net/SQLAdmin/sp_nrInfo.html

Madhivanan

Failing to plan is Planning to fail

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-12-22 : 19:06:00

activecrypt..

profiler is helpful only to know whats running currently..it doesnt tell u anything
about active transaction which are hanging around...

Madhivanan
thanks for the link..but the proc doesnt help @all..

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-12-23 : 06:34:25

Hi,
you can trace for statement start and ending time , isn't it ?!

Andy Davis
Activecrypt Team
--------------------------------------------
SQL Server Encryption Software
http://www.activecrypt.com

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-12-27 : 18:42:58

not if the profiler is not running when the transaction was started..
u cant have profiler running in the prod db ...isn't it ?!

bakerjon
Posting Yak Master

145 Posts

Posted - 2005-12-28 : 13:42:51

You could use a server side trace with sp_trace_create (or use Profiler to create a trace script and save the t-sql) and save to a file. You could have this trace running in the back ground on the production server and try to reproduce the problem. This could create a load on the server if you are capturing all stmts, but if you were able to filter it by hostname, username, object_id or some other thing, it would help. The server side trace would be much less intrusive than running the Profiler GUI.

Jon
-Like a kidney stone, this too shall pass.

http://www.sqljunkies.com/weblog/outerjoin

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-12-28 : 14:04:39

thanks bakerjon ...
i wud have a look at that..
but my question is still unanswered...m talking bout something already happened...
DEBUG ..DEBUG..DEBUG..
and not how to capture info from now on.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-12-28 : 14:38:53

So what does dbcc inputbuffer(@spid) show?

Tara Kizer
aka tduggan

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-12-28 : 14:42:39

sp_cursorfetch

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-12-28 : 14:43:29

quote:
Originally posted by 2lazydba

thanks bakerjon ...
i wud have a look at that..
but my question is still unanswered...m talking bout something already happened...
DEBUG ..DEBUG..DEBUG..
and not how to capture info from now on.....

I'm thinking the best solution for you is to go take an SQL Server course or learn it on your own.

Here's what to do:

1) Run a select against sysprocesses to identify the spid you want to investigate. Look at the hostname, program_name, dbname, and loginame. All these should be of considerable help when trying to troubleshoot.


select spid, d.name dbname, hostname, program_name, loginame from master.dbo.sysprocesses p inner join master.dbo.sysdatabases d on (d.dbid = p.dbid) where open_tran != 0

2) Either use DBCC INPUTBUFFER([INSERT SPID HERE]) or this command to get the last text which was run from the spid(s).


exec master.dbo.sp_execresultset 'select ''select '' + cast(spid as varchar(4)) +  '' dbcc inputbuffer('' + cast(spid as varchar(4)) + '')'' from master.dbo.sysprocesses where open_tran != 0'
go

3) Use the text from DBCC INPUTBUFFER to search sysobjects with the query below.


USE [INSERT DB TO USE HERE]
GO
declare @name as varchar(100)
SET @Name = 'INSERT TEXT FROM DBCC INPUTBUFFER HERE'

select name 
from sysobjects 
where id in (
	select distinct id 
	from syscomments 
	where text like '%' + @name + '%'
)
go

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-12-28 : 14:46:31

quote:
Originally posted by 2lazydba

sp_cursorfetch

If that's the case, then you can safely kill it.

Tara Kizer
aka tduggan

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-12-28 : 14:50:20

quote:
Originally posted by 2lazydba

sp_cursorfetch

I remember this question... Why not just look at sysprocesses and see the application that's running the transaction? You could also look at the last_batch, login_time, hostprocess, and netaddress.

Sysprocesses should be everything you need. Hostprocess is the PID on the host that's requesting the query. This is useful if you're using citrix or some other type of terminal server. An NT admin can lookup who the PID belongs to for you if you don't know how. Netaddress is the MAC address for the machine that is requesting the query and can be converted to IP using arp.

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-12-28 : 15:54:25

ok fellas...lemme give u a complete pic again...

dont ask y such an arch n all..

we have xml files as input which are then processed by an C++ service..
begin tran
this service processes the xml and inserts into database A...
triggers fire on the tables of database A and insert/update/delete from tables of database B..

if error rollback else commit...

somehow we landed up in an open transaction with the fate of transaction unknown..
dbcc inputbuffer says sp_cursorfetch...
tats all...
so i want to know which tables it tried 2 insert n failed...
it failed in database A or triggers failed in database B..

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-12-28 : 15:59:13

quote:
Originally posted by 2lazydba

ok fellas...lemme give u a complete pic again...

dont ask y such an arch n all..

we have xml files as input which are then processed by an C++ service..
begin tran
this service processes the xml and inserts into database A...
triggers fire on the tables of database A and insert/update/delete from tables of database B..

if error rollback else commit...

somehow we landed up in an open transaction with the fate of transaction unknown..
dbcc inputbuffer says sp_cursorfetch...
tats all...
so i want to know which tables it tried 2 insert n failed...
it failed in database A or triggers failed in database B..

1) Kill the transaction and it will do a rollback.
2) Fix the C++ service.
3) While fixing the C++ service add logging of some sort to it.
4) Add logging of some sort to the database sp's and triggers.

Enough said.

Enjoy,

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

EDIT:
Oh, oh, oh! Don't forget to get rid of that dumpy bloated excuse for a lauguage they call XML, too.

Passing a transactionid with a bit field in XML (182 charactors just so I can pass 12? Any idea why credit card terminals don't use XML for everything?) LOL. :

<?xml version="1.0"?>
<CASH_TRANSACTION_CONFIRMATION>
<TRANSACTION_ID>051229us251</TRANSACTION_ID>
<USER_ACCEPTED_CONTRACT>1</USER_ACCEPTED_CONTRACT>
</CASH_TRANSACTION_CONFIRMATION>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources