Save DBCC OPENTRAN result to a table or to a string

Question

Save DBCC OPENTRAN result to a table or to a string

srisagar_p

SSChasing Mays

Points: 623
More actions
January 11, 2009 at 12:42 am

#204859

Hi all,
I need to save "DBCC OPENTRAN" command result to a table column or to a string.
Please reply me as early as possible with the possibilities that will helps me a lot.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Many of the DBCC statements have an undocumented option TABLERESULTS which changes the output of the statement from text to a resultset that can be inserted somewhere.

With OPENTRAN, it'll be

DBCC OPENTRAN WITH TABLERESULTS

Wrap that inside an EXECUTE using dynamic SQL and you can insert it into a table. The table must have columns that match the DBCC output.

So that'll be

INSERT INTO SomeTable

EXECUTE ('DBCC OPENTRAN WITH TABLERESULTS')

The output from OPENTRAN with the tableresults option will look something like this:

OLDACT_SPID52
OLDACT_UID-1
OLDACT_NAMEuser_transaction
OLDACT_LSN(2071:33:1)
OLDACT_STARTTIMEJan 11 2009 11:09:37:333AM
OLDACT_SID0x0105000000000005150000002e86f8cbc457a001b905

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 2

Hi,

You can do something like this:

CREATE table dbccOpenTran

(

DatabaseName sysname

, OpenTran varchar(30)

)

insert into dbccopentran

exec ('dbcc opentran with tableresults')

Helpful?

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

srisagar_p SSChasing Mays Points: 623 More actions · Answer 3

Thank you very much for your quick reply.

Really i am very grateful to u.......

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 4

Hi,

I just realized you will not get a timestamp so you might want to do something like this instead:

CREATE table dbccOpenTran

(

DatabaseName sysname

, OpenTran varchar(30)

, ntime datetime DEFAULT getdate()

)

insert into dbccopentran (databasename, opentran)

exec ('dbcc opentran with tableresults')

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

ftpex1 SSC Enthusiast Points: 148 More actions · Answer 5

Unless you can convert the sid to something usable how does that help?