SQL Server Performance Forum – Threads Archive

Transaction Log Function

Ok, it’s the old I want to read and make sense of the t-log – using the very useful if you can figure out function fn_dblog. It DOES differ slightly from 2000, ie the object name needs to be parsed out from allocunitname rather than transactionname So, I want to capture the type of tran, user, time, object, and maybe join it to sysobjects or sysprocesses. First, anyone know what the [Log Record] field is? Is that encrypted or plain hex-code, and is there a way to decipher it. Also, the user uid is the dbuser (ie dbo), so how could I know who logged in w/it? Just searching for ideas on using this function (I’ve been all over the internet w/this, so hoping something new and current can be added) Many thanks!

Are you looking similar in SQL 2005?
DBCC LOG can also be called as a system function for use in a SELECT statement or other queries via the fn_dblog function. That function is an undocumented
Returns a table of records from the transaction log. Arguments:
@StartingLSN : start Log Sequence Numbers (LSN), NULL requests log records from the beginning of the transaction log
@EndingLSN : ending Log Sequence Numbers (LSN), NULL requests log records till the ending of the transaction log
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thx Satya That’s pretty basic info – I already know how to query it. I have a more specific Q (rather than starting a new thread) Say I wanted to return all the rows for a particular transaction (transaction id) for a particular spid. (Only the first row of a transaction has the spid and is the Begin Tran operation. I need help w/the syntax – I guess you need to join it to itself? So, the results should something look like: tran id tran operation spid ————————————— 0000:0000425cLOP_BEGIN_XACT 52 0000:0000425cLOP_LOCK_XACT 52 0000:0000425cLOP_MODIFY_ROW 52 0000:0000425cLOP_PREP_XACT 52 0000:0000425cLOP_COMMIT_XACT 52 etc…… Please assist <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Try
select top 20 [Server UID], UID, SPID,[Current LSN], [Previous LSN], Operation, Context, [Transaction ID],
[Object Name], [Index Name]
from ::fn_dblog( default, default )
where [operation] = ‘LOP_BEGIN_XACT’
order by [Current LSN] desc
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

NG, Satya…..My result set above reflects 1 transaction, from beginning to end, where the spid only appears in the LOP_BEGIN_XACT row, but I would be interested to get the LOP_MODIFY_ROW info for the spid. Does this make sense? I appreciate you help immensely – THX!

I haven’t dug much into in this regard, so I believe we need more details into it.
Drop me an email as I will see to discuss this with one of the SQL Dev.team in Tech-ed and will come back to you. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I’d definately be interested in more ways to decode/decipher the log using this function. But, I think the tsql is a general syntax question. Thx again. Enjoy Tech-Ed and Magic Mountain <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />

Check the following…
http://novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2004/11/27/37.aspx MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks, if you are attending then drop by TLC to say hello. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by sql_jr I’d definately be interested in more ways to decode/decipher the log using this function. But, I think the tsql is a general syntax question. Thx again. Enjoy Tech-Ed and Magic Mountain <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> <hr height="1" noshade id="quote"></blockquote id="quote"> Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

]]>