dbcc inputbuffer..sp_cursorfetch

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)

dbcc inputbuffer..sp_cursorfetch

Author

Topic

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-12-13 : 16:57:46

Hi,

We had a problem in one of our databases cos of an
open transaction.

When i ran dbcc inputbuffer(@spid) it showed
sp_cursorfetch as the result.

I want to know in depth about that spid which left an open transaction.

How can i do this...

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-12-13 : 17:01:36

Look here: http://support.microsoft.com/kb/325607
In most cases, this will give you more detail then dbcc inputbuffer.

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

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-12-13 : 17:14:34

thanks for the reply..
to test the function i did this..

use pubs
go

begin tran

truncate table bkp_authors -- i have a copy of authors

Insert into bkp_authors
Select *
from authors

--did not commit or rollback,

open another session in QA..

dbcc opentran

Transaction information for database 'pubs'.

Oldest active transaction:
SPID (server process ID) : 53
UID (user ID) : 1
Name : user_transaction
LSN : (1452:306:520)
Start time : Dec 13 2005 2:09:51:500PM
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master..sysprocesses WHERE spid = 53
SELECT * FROM ::fn_get_sql(@Handle)

(0 row(s) affected)

no results !!

ACALVETT
Starting Member

34 Posts

Posted - 2005-12-14 : 08:54:53

No results is because your transaction would have generated a zero cost query plan and fn_get_sql can not see queries associated with zero cost plans by default.

You can change this by adding trace flag 2861 but this can cause a significant performance overhead as enabling trace flag 2861 can cause your syscacheobjects table to grow substantially, which has a ripple effect that causes query compilations to take much longer than they should.

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-12-14 : 09:17:58

You're getting an open transaction confused with what this does. To test this you'll need to run it against a SPID that is running something right at that moment.

Look here: http://vyaskn.tripod.com/fn_get_sql.htm

There is a section where he shows how to test the function.

DBCC Opentran should be enough for you to find info on open transactions. I posted info on the "fn_get_sql" function because it often gives more information than dbcc input buffer. Try looking at this article for the sp_cursorfetch: http://www.sqlteam.com/item.asp?ItemID=11842

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-12-14 : 20:17:29

thanks for the link..
the function is really helpful.
only when the process is executing some sql stmt..
what i really want is if there are any active/open transactions
then what was the last sql stmt executed ..

in my system if there is an open transaction then the subsequent
processes halt with an error like

"An enlistment in transaction already exists."
googling didnt help...

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2005-12-15 : 20:15:13

dbcc opentran

Transaction information for database 'pubs'.

Oldest active transaction:
SPID (server process ID) : 53
UID (user ID) : 1
Name : user_transaction
LSN : (1452:306:520)
Start time : Dec 13 2005 2:09:51:500PM
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

can i used the LSN number and DBCC LOG to find out whats goin on
in that open transaction....

where is the sql team ????

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources