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.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Microsoft SQL Server Tools and Utilities Microsoft SQL Server Tools Guide How SQL Server 2008 components impact SharePoint implementations SQL Server Mailbag: Data restoration and DB property management SQL language crash course (just enough to be dangerous) Setting up SQL Server Service Broker for secure communication Microsoft SQL Server 2008 Resource Governor primer The sqlcmd utility in SQL Server Performance analysis tools for SQL Server Software security tools to improve your skills in a single day Surface Area Configuration and other security tools in SQL Server 2005 SQL/Transact SQL (T-SQL) SQL language crash course (just enough to be dangerous) Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility Testing transaction log autogrowth behavior in SQL Server SQL/Transact SQL (T-SQL) Research Database Management and Administration Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V How to create SQL Server virtual appliances for Hyper-V Push vs. pull: Configuring SQL Server replication

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) data aggregation  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

> 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