View SQL Server transaction logs using DBCC

SQL Server transaction logs aren't typically exposed to the end user or database administrator; they are usually only employed by fairly technical users who can interpret the data within. So, if you want to see the data in a given database's transaction log, the most direct approach is the DBCC LOG command.

Earlier versions of SQL Server (mainly 6.5) had a system table named syslogs against which someone could run queries to retrieve log data. SQL Server 7.0 and later now use the DBCC LOG command, probably because people kept trying to do unorthodox things to the syslogs table (such as manually insert or delete records!). It's not that they could, but having that information stored conventionally in a table probably misled people into thinking it could be manipulated like a regular table.

To retrieve the transaction log for a given database, use the following command, where <databasename> is the name of the database to retrieve a transaction log for, and <output> is the type of output generated:

DBCC LOG(<databasename >, <output >)

0: Return only the minimum of information for each operation -- the operation, its context and the transaction ID. (Default)
1: As 0, but also retrieve any flags and the log record length.
2: As 1, but also retrieve the object name, index name, page ID and slot ID.
3: Full informational dump of each operation.
4: As 3 but includes a hex dump of the current transaction log row.

Note that the larger the transaction log and the more detailed the information you ask for, the longer the dump will take.

DBCC LOG can also be called as a system function for use in a SELECT statement or other queries via the fn_dblog function. For instance, the following command will retrieve the top 10 rows with all of the available transaction log columns from the currently-selected database's transaction log.

USE MASTER
SELECT TOP 10 * FROM ::fn_dblog(<start>, <end>)

To change the database context here, change the USE statement. The <start> and <end> parameter are the starting and ending logical sequence numbers (LSNs) for the retrieved records.

One very useful application of this is to determine which tables are suffering from page splits, a performance problem that can become chronic on systems with high database activity. To understand how often this happens on a given database, try the following command:

USE <database>
select [Object Name], [Index Name]
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'

This will tell you which tables and indices suffer from splits. If you get no records returned, then your database is probably not suffering from page splitting problems all that often.

Hint: Do this at times when your database is fairly heavily populated and has not yet been backed up or had records rotated out for archiving, as this gives you a more realistic picture of page splits.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

More information from SearchSQLServer.com