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
