Query data in on-line Transaction Log

  • Is there any way of quering the data in the on-line transaction log ? Or perhaps any tools available too check the data ?

  • If you mean the SQL server log, it's just a text file. You can open it with notepad.

  • Can't remember who on the list provided this, but here is a method to read the transaction log. I have yet to give this code a test ride.

    To read the entire log, (and for a database with a large number of transaction I Do Not Recomend Using this function in this manner) your statement will look like:

    Select * from ::fn_dblog('', '')

    This will give you every entry in the transaction log. You can customize the statement to get the desired results.

    Ex: Select * from ::fn_dblog('', '')

    where [Begin Time] is Null

    and [End Time] is Null

    and [Transaction ID] in (select [Transaction ID] from ::fn_dblog('', '')

    where [Begin Time] >= '2002/09/01 00:00:00:000'

    or [End Time] <= '2002/09/12 08:08:59:999')

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • quote:


    I actually mean the database log.


  • Is the :: perhaps refering too something specific ?

  • You could try a tool such as LogExplorer

    http://www.lumigent.com

    Also, you could try DBCC LOG to look at the information but as of yet I have not found the details of understanding it, so not really usefull.

    DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])

    where:

    dbid or dbname - Enter either the dbid or the name of the database

    type - is the type of output, and includes these options:

    0 - minimum information (operation, context, transaction id)

    1 - more information (plus flags, tags, row length, description)

    2 - very detailed information (plus object name, index name, page id, slot id)

    3 - full information about each operation

    4 - full information about each operation plus hexadecimal dump of the current transaction log's row.

    -1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID

    by default, type = 0

    To view the transaction log for the master database, run the following command:

    DBCC log (master)

    The function Greg is referring to looks like they wrapped DBCC LOG into a function for 2000 and is not valid for 7. Greg where did you fine this by chance?

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

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