SQL Transaction Log Viewer - Poor Man's Version

I came up with a way to somewhat see what is in the SQL Transaction Log in conjunction with the SQL Profiler. So, in addition to seeing the actual SQL statements in the SQL Profiler, you also get an idea, cryptic as it may be, of what those statements are doing to the database. You will need to stop the transaction log backups while you are using this since the transactions, once backed up, will not be returned in our select statement. Note that the Transaction Log only keeps track of changes to the database, which is why we have to run a SQL Profiler trace as well.

The Content0 column is the main payload. There are a couple WHERE statements there to help filter out some of the noise and to look at a particular SQL session (a user's connection) and/or timeframe.

The first script is the my_HexToChar function which translates some of the transaction log information to near-plain-english. You will need to run this script in each database for which you want to look at the transaction log information.

The second and third scripts are for looking at the transaction log information. The first script looks only at the transaction log. The second looks at both the transaction log and the SQL Profiler trace output.

In order to use the third script, you will need to start and run a SQL Profiler trace. Open SQL Profiler, create a new Trace, choose the TSQL_Replay template, check the box for Save to table, select a database other than the one for which you are tracing (I might suggest creating a new DB called ProfilerDB for this purpose), and specify table myReplay. Run the trace and leave it running while you run your queries or programs.

EDIT:
To filter the date range of the returned records, use the commented-out WHERE statement filter for the [Begin Time] column in script 2 or 3.

You can use the fourth script to clear out the trace log and reset the starting point of the transaction log query. If you are wondering what CHECKPOINT is doing to your log/data (it's not clearing out your transaction log), here is a great article from Paul Randal.

SQL Profiler Settings

Script 1: my_HexToChar function

CREATE FUNCTION my_HexToChar (@in VARBINARY(4000))
RETURNS varchar(4000)
AS
  BEGIN
      DECLARE @result varchar(4000)
      DECLARE @i int
      SET @result = ''
      SET @i = 1
      WHILE @i < Len(@in)
        BEGIN
            IF Substring(@in, @i, 1) BETWEEN 0x20 and 0x7A
              -- limiting it to certain visible characters 
              SET @result = @result + cast(Substring(@in, @i, 1) as char(1))
            SET @i = @i + 1
        END
      RETURN @result
  END
GO 

Script 2: view Transaction Log only

SELECT 
    [Begin Time],
    [Transaction Name],
    AllocUnitName,
    [Transaction ID],
    SPID,
    Operation,
    dbo.my_HexToChar([RowLog Contents 0]) Contents0, -- gives an idea of what was changed
    dbo.my_HexToChar([RowLog Contents 1]) Contents1,
    dbo.my_HexToChar([RowLog Contents 2]) Contents2,
    dbo.my_HexToChar([RowLog Contents 3]) Contents3,
    dbo.my_HexToChar([RowLog Contents 4]) Contents4,
    dbo.my_HexToChar([Log Record]) AllContents,
    *
FROM
    fn_dblog(null, null) a
WHERE
    [Transaction ID] in (
        SELECT [Transaction ID]
        FROM fn_dblog(null,null)
        where Operation = 'LOP_BEGIN_XACT'
        and [Transaction Name] not IN ('AutoCreateQPStats','SplitPage','SpaceAlloc','UpdateQPStats')

--        and SPID = 51    -- uncomment this line to limit it to a particular SQL session - look in Activity Monitor for SPIDS

--        and cast([Begin Time] as DATETIME) BETWEEN '07/30/2010 11:30' and '07/30/2010 2:50pm'   -- uncomment this line to limit by date/time
    )
order by a.[Transaction ID],a.[Current LSN]

Script 3: view Transaction Log and SQL Profiler info

SELECT
    *
FROM
(
    SELECT 
        b.[Begin Time],
        [Transaction Name],
        AllocUnitName,
        a.[Transaction ID],
        b.SPID,
        Operation,
        dbo.my_HexToChar([RowLog Contents 0]) Contents0, -- gives an idea of what was changed
        dbo.my_HexToChar([RowLog Contents 1]) Contents1,
        dbo.my_HexToChar([RowLog Contents 2]) Contents2,
        dbo.my_HexToChar([RowLog Contents 3]) Contents3,
        dbo.my_HexToChar([RowLog Contents 4]) Contents4,
        dbo.my_HexToChar([Log Record]) AllContents,
        2 EventType,
        [Current LSN] SortOrder
    FROM
        fn_dblog(null, null) a
    INNER JOIN
        (SELECT max([Begin Time]) [Begin Time],[Transaction ID],max(SPID) SPID FROM fn_dblog(null, null) GROUP BY [Transaction ID] ) b
    ON a.[Transaction ID] = b.[Transaction ID]
    WHERE
        a.[Transaction ID] in (
            SELECT [Transaction ID]
            FROM fn_dblog(null,null)
            where Operation = 'LOP_BEGIN_XACT'
            and [Transaction Name] not IN ('AutoCreateQPStats','SplitPage','SpaceAlloc','UpdateQPStats')
        )
    UNION ALL
    SELECT
        StartTime,
        '',
        '',
        convert(varchar(20),ClientProcessID),
        SPID,
        '',
        TextData,
        ApplicationName,
        '',
        '',
        '',
        '',
        1 EventType,
        convert(varchar(20),EventSequence) SortOrder
    FROM    
        ProfilerDB..myReplay
    WHERE
        DatabaseName = DB_NAME()
) Logs
WHERE
    1=1
    -- and SPID = 60    -- uncomment this line to limit it to a particular SQL session - look in Activity Monitor for it
    -- and cast([Begin Time] as DATETIME) BETWEEN '03/13/2011 18:24:05' and '03/13/2011 18:26:39'   -- uncomment this line to limit by date/time
order by [Begin Time],[Transaction ID],SortOrder

Script 4: clear the Profiler Log and reset the Transaction Log query start point

DELETE FROM ProfilerDB..myReplay
CHECKPOINT