Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted June 1, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

How to Read the Transaction Log File

By Greg Larsen

Have you ever wanted to be able to see the actual transactions that are contained in the transaction log file?  Well there is a way to read the transaction log file using an undocumented function.   In this article, I will show you how to browse the transaction log using this undocumented function.

To read the transaction log you I will be exploring using the undocumented function named “fn_dblog”.   This function requires two parameters to be passed.  The first parameter is the starting LSN (Log sequence number) and the second parameter is the ending LSN.   If you wanted to read all the data in the transaction log then you will need to pass null values for both of these parameters like so: 

 SELECT * FROM sys.fn_dblog(NULL,NULL)

Let’s see how we can use this function by looking at an example.  For this demonstration, I will be running the following code to place some records in the transaction log for the database in this demo:

CREATE DATABASE TranlogRead;
GO
USE TranlogRead;
GO 
CREATE TABLE dbo.Color (Color varchar(10));
GO 
INSERT INTO dbo.Color VALUES ('Red'),('Yellow'),('Green')
GO 
DELETE FROM dbo.Color WHERE Color = 'Red';
GO

In this code I first created a database named TranLogRead.  I did this just to create an isolated testing database, so I could explore the transaction log for this isolated database.   Next I created a table named “Color”.  Once created I then inserted three rows, and then finished up by deleting the row that contained the color “Red”. 

In order to find the CREATE TABLE, INSERT and DELETE transaction in the transaction log I can run the following code:

SELECT [Current LSN]
      ,[Operation]
      ,[Transaction ID]
      ,AllocUnitName 
      ,[Transaction Name]
      ,[Transaction SID]  
      , SUSER_SNAME([Transaction SID]) AS DBUserName
      ,[Begin Time]  
      ,[Lock Information]
FROM fn_dblog(NULL, NULL)
WHERE SUSER_SNAME([Transaction SID]) = 'Greg-PC\GREG'
AND   [Transaction Name] in ('CREATE TABLE','INSERT','DELETE')

In this code, you can see I used the fn_dblog function in the “FROM” clause.  I also used the “WHERE” predicate to return only transaction log rows that involved a CREATE TABLE, INSERT and/or DELETE transaction created by database user Greg-PC\GREG.

Keep in mind the fn_dblog function is undocumented and you should use it with caution.  To find out more about the information returned by fn_dblog I suggest you remove the “WHERE” predicates from the above code and look at all the different transaction log information that this function returns.  Additionally, you should explore using the “Transaction ID” column in a WHERE predicate to see all the different transaction log records that go along with a single transaction. 

See all articles by Greg Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM