Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to read the SQL Server Database Transaction Log


By:   |   Read Comments (29)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Administration

Problem

Have you ever wondered how SQL Server logs track transactions and what information SQL Server captures in the transaction log file?  In this tip we will take a look at a SQL Server function that you can use to read the transaction log to see what entries are made for database transactions.

Solution

I will explain how to read your database transaction log file and how transactions are written for your database if you perform any database activity. This tip is helpful if you want to know what the transaction log file captures. There is an undocumented function called "fn_dblog" which enables you to read data from your transaction log which contains very informative data about things that are happening in your database. I strongly suggest testing any undocumented features in a lab environment first.

The function (fn_dblog) requires a beginning LSN and ending LSN for a transaction.  NULL is the default for this function and this will return all log records from the transaction log file.

Create a Test Database

To show how this works, we will create a database and a table to play with this function. Run the below SQL code to create a database and table.

--Create DB.
USE [master];
GO
CREATE DATABASE ReadingDBLog;
GO
-- Create tables.
USE ReadingDBLog;
GO
CREATE TABLE [Location] (
    [Sr.No] INT IDENTITY,
    [Date] DATETIME DEFAULT GETDATE (),
    [City] CHAR (25) DEFAULT 'Bangalore');

We have created a database named "ReadingDBLog" and a table 'Location' with three columns. Now you can check all information and processes which have been used by SQL Server to create the database and table. We will run the below code to check the log file for this newly created database to check what processes and steps SQL Server took to create the database and table.

USE ReadingDBLog;
GO
select COUNT(*) from fn_dblog(null,null)

No of rows generated during creating a db and table

We can see there are 339 rows that have been generated for just creating a dummy database and a blank table. You can go and check the logs by using this function to get details for all processes used to create the database and table. Look at the below code to see the data in the transaction log file.

USE ReadingDBLog;
GO
select [Current LSN],
       [Operation],
       [Transaction Name],
       [Transaction ID],
       [Transaction SID],
       [SPID],
       [Begin Time]
FROM   fn_dblog(null,null)

Capture data from log file of newly created database

As the total number of rows and columns are large, I gave a snapshot of some of the columns and rows but I have tried to capture a few informative columns here. You can see in the above screenshot that the transaction name column shows the database name, similarly it will show the create table for the table creation code. Transaction ID is the same for all parts of a transaction. The value for transaction name will be filled only when the particular transaction starts with "LOP_BEGIN_XACT" in the Operation column. "LOP_BEGIN_XACT" means begin transaction. The operation column will let us know which operation is performing like an insert, update, delete, shrink, lock, page allocation etc...  It is pretty easy to understand the operation based on these key words to see what operation is being performed by SQL Server.

Run some DML commands to see what is captured

Now we will run a few DML scripts to check how data insertion, updating or deletion is logged in the database log file. During this operation you can also track how a page is allocated or de-allocated.

USE ReadingDBLog
go
INSERT INTO Location DEFAULT VALUES ;
GO 100
GO
UPDATE Location
SET City='New Delhi'
WHERE [Sr.No]<5
GO
DELETE Location 
WHERE [Sr.No]>90
Go

Let's check our database log file again. As we saw from above, there is a lot of info logged in the transaction log file, so I will filter the data.

USE ReadingDBLog
go
SELECT
 [Current LSN],
 [Transaction ID],
 [Operation],
  [Transaction Name],
 [CONTEXT],
 [AllocUnitName],
 [Page ID],
 [Slot ID],
 [Begin Time],
 [End Time],
 [Number of Locks],
 [Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN 
   ('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
    'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')  

Output of DML cmd in t-log file

Your output will look something like the above screenshot after running the above script.

Let's see how we can read the output. You can see the highlighted area which we will read as:

  • INSERT statement with transaction ID 0000:00000448
  • started at 2013/09/27 16:50:44:530
  • INSERTed a row in a HEAP table 'dbo.Location' in pageID 0001:00000099
  • finished at 2013/09/27 16:50:44:530

 Similarly, it will show you this same kind of information for UPDATE and DELETE statements.

Finding internal SQL Server operations in transaction log

We can also check internal functions of SQL Server through this function. Let's take for example page splitting. We can track all info about page splits like how many times page splits occur, on which page and during which operation. Let's take a look at this below to see page splits for the above INSERT operation. Run the below SQL code to get all operations related to page splits.

USE ReadingDBLog
go
--Get how many times page split occurs.
SELECT 
 [Current LSN],
 [Transaction ID],
 [Operation],
  [Transaction Name],
 [CONTEXT],
 [AllocUnitName],
 [Page ID],
 [Slot ID],
 [Begin Time],
 [End Time],
 [Number of Locks],
 [Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Transaction Name]='SplitPage' 
GO

--Get what all steps SQL Server performs during a single Page Split occurrence.
SELECT 
 [Current LSN],
 [Transaction ID],
 [Operation],
  [Transaction Name],
 [CONTEXT],
 [AllocUnitName],
 [Page ID],
 [Slot ID],
 [Begin Time],
 [End Time],
 [Number of Locks],
 [Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Transaction ID]='0000:00000451'  

Capture Page Split in t-log

As you can see above Page Split occur three times for the above DML activity. The first script will let us know how many times page splits occurred and the second script elaborates on the internal processes SQL Server ran during a single Page Split operation. The above image takes the first page split, transaction ID '0000:00000451', and shows all internal processes in the second result set.

Similarly if you drop any object or you perform other database operations they will get logged in the transaction log file which will then be visible using this function.

How a backup interacts with the transaction log

Now I will run a backup and see the transaction log file again. Run a backup on this database and then again check the transaction log file.

SELECT COUNT(*)
FROM fn_dblog(null,null)
GO

BACKUP DATABASE ReadingDBLog TO DISK = 'c:\ReadingDBLog_Full.bak'
GO

SELECT COUNT(*)
FROM fn_dblog(null,null)
GO

Run backup to see the changes in t-log

As we can see, the number of rows has been drastically reduced after doing a backup and it has been reduced to 9 rows from 528. This means the inactive part of the log which tracked the transactions has been dumped to a backup file and the original entries from the log file have been flushed. Now you can shrink the log file if necessary.

Other things you can do

You can also use the DBCC Log() command to see log information, but this command will not give you detail information. You can also use trace flag 2537 to look at all logs and not just the active log.

Next Steps
  • Use this function to research transaction activity.
  • There is a lot of information you can get from this function and there are more than 100 columns that you can use.
  • Use this data and also correlate with other data that you collect on your server.
  • Explore more tips on SQL Server Database Administration


Last Update:






About the author





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, December 06, 2016 - 5:32:09 AM - Johnson Weltch Back To Top

Great article, There i found one article which briefly describes the need and working of DBCC LOG Command.

http://www.sqlmvp.org/dbcc-log-command-in-sql-server/


Thursday, May 05, 2016 - 12:28:35 AM - Bassi Back To Top

 

 Thank you for information.I want to ask how to find one perons daily work activities in entire system?

Baswanth


Tuesday, February 02, 2016 - 5:54:10 AM - Mopale Back To Top
USE ReadingDBLog
go
--Get how many times page split occurs.
SELECT 
 [Current LSN],
 [Transaction ID],
 [Operation],
  [Transaction Name],
 [CONTEXT],
 [AllocUnitName],
 [Page ID],
 [Slot ID],
 [Begin Time],
 [End Time],
 [Number of Locks],
 [Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Transaction Name]='SplitPage' 
GO

--Get what all steps SQL Server performs during a single Page Split occurrence.
SELECT 
 [Current LSN],
 [Transaction ID],
 [Operation],
  [Transaction Name],
 [CONTEXT],
 [AllocUnitName],
 [Page ID],
 [Slot ID],
 [Begin Time],
 [End Time],
 [Number of Locks],
 [Lock Information]
FROM sys.fn_dblog(NULL,NULL) 

WHERE [Transaction ID]='0000:00000451'  .


Thursday, December 24, 2015 - 1:39:51 AM - Kesavan P Back To Top

 I need to check the failed calls of particular stored procedure along with input values used.

 

Is there anyway to get that transaction logs from SQL Server 2012?

 

 


Friday, May 29, 2015 - 2:48:18 PM - kashif Back To Top

 

How can I find which tables causing database growth during 6 months in SQL SERVER 2008?


Tuesday, January 20, 2015 - 2:11:15 AM - Ashish Rohit Back To Top

Sir, 

   I read your blog about sql transection log. Its  appriciat but I need to get log of SQL DML Query executed in last day.

How can  I get record of all query executed previos day.?


Friday, September 19, 2014 - 1:21:16 PM - Desh Back To Top

Hi This is an excellent post. I am required to do some data forensics and I have struggled to extract the log information before.


Monday, August 11, 2014 - 6:38:39 AM - rohit mane Back To Top

hello Manvendra Sir,

 Actually i want to do failover clustering, will you please guide us.

Thank you


Wednesday, July 23, 2014 - 7:36:46 AM - Kumar Back To Top

Does some one reply these posts? May i know if this has any ETA?


Tuesday, July 22, 2014 - 1:18:15 AM - prakash deokar Back To Top

I have deleted some records from my tblemployee table. how to recover or find that record should be store some where in log file.

how to retrive that deleted data from log file

  •  

Monday, July 21, 2014 - 2:50:23 PM - Kumar Back To Top

sir,

I have a scenario to list the people who ran delete statements in the entire month. we have scheduled backups every month and Active part of the log will certainly be truncated. I need to find the entried in "inactive log"(Last month). May you please help me a link just like above which will guide me to achieve this task? If you have powershell doing this that would be great!

Thanks and Regards

Kumar


Wednesday, June 04, 2014 - 11:45:45 AM - Karthick Back To Top

Hi,

 

Is there a way to find out from which Machine (IP Address) or SQL User created/Modified the records by reading the transaction log?

Thanks

JK (Karthick)


Wednesday, April 23, 2014 - 5:07:20 AM - Shamas Saeed Back To Top

Very Informative and Interesting. After getting log information we can play around How SQL Server performing actions behind the scene. 


Tuesday, April 22, 2014 - 3:59:01 AM - rajesh Back To Top

Hi sir,

         Iam Rjshkumar,as iam studying u r blog from so many days,it is very informative.Today iam doing this above example as u explained,but when u create a database and table with dumy..u r geting 339 rows effected.when iam created with same example the rows are effedted only 139.why it was geeting like that can u plz explain that.

 

Thanks and regards

rjshkumar


Thursday, April 17, 2014 - 7:29:00 AM - Imran khan Back To Top

HI Singh,

 

Nice article on log, wanted to check with you I am new to this field, and I have a below requirement like

1) I have a requirement, we have to create a database A  from other database B which is hosting in remote place, database B files I can request thru FTP to transfer the files in database A environment on daily basis, how can I update database A with that files and what type of files I have to request from database B to replicate all the data from database B to database A

Configurations

both the server are running on SQL 2012, windows 2008 R2

DATABASE A is Hosting in A data centre,

DATABASE B  is hosting in B data centre,

 

both will not allow to direct connect to their database for mirroring, however we can request thru FTP servers.

 

Need your kind help and some lights to implement this kind of requirement, I hope you might have understood my requirement

 

Regards

Imran Khan


Tuesday, April 15, 2014 - 3:46:09 AM - Renuka Back To Top

 

I have a database where log file is corrupt due to which the log backups are failing. We plan to create a new log file and are aiming to create a new transaction log file. Is there a way to flush the transactions from the old log to the new log to make sure there is no data loss?


Friday, April 11, 2014 - 3:33:07 AM - gaurav Back To Top

very helpful.


Friday, March 14, 2014 - 5:43:11 AM - Rani Back To Top

Hello,

I have followed the steps and i was able to retrieve whatever i wanted from fn_dblofg.

But the problem is that the records from fn_dblog suddenly disappear after a little while.

Do you have any idea why that happens ?

 

Note : my database recovery mode is set to full, and there have been no back up and no recovery done to the database....

 

Can you help me splving this problem 

 

Thank you.


Saturday, February 15, 2014 - 9:59:19 AM - G Srikar Reddy Back To Top

]

 

Hello Sir,

Very informative post. Thanks for that!

I have an issue... 

Only transaction log backup will truncate (i mean size of the log file will not be reduced but the vlf's are flushed) the log file right? 

Will the full backup also truncates log?


Wednesday, February 12, 2014 - 4:22:21 AM - Mahesh Javalagaddi Back To Top

Manvendra you are Superb! Your post helped me lot! Thanks a lot


Wednesday, January 08, 2014 - 6:38:23 AM - TA Ghalib Back To Top

 

I like this post. Thanks a lot Manvendra:-)


Thursday, December 26, 2013 - 9:19:16 PM - Sowmya Back To Top

Awesome information Manu, very very helpful. Please keep posting :)


Tuesday, December 03, 2013 - 3:25:11 AM - vimal Back To Top

Just got the answer of my previous question. It is happening because it is in simple recovery model :) :)  In case of full or bulk logged recovery model, the number of log file will be increased after full backup.

Thanks!!!


Tuesday, December 03, 2013 - 3:03:44 AM - vimal Back To Top

Hello Sir,

I am new to sql server. I have read some where that transaction log does not gets truncated until we take the transaction log backup for that database. As you have written that " the number of rows has been drastically reduced after doing a backup and it has been reduced to 9 rows from 528." I'm confused that how a full back can reduce the log file.

Please help me to understand it!!!


Monday, December 02, 2013 - 2:18:41 PM - Hilda Back To Top

Very cool post and very informative. 


Friday, November 01, 2013 - 2:17:29 PM - ronak Back To Top

I just installed sqlserver 2008 r2 in my computer. I downloaded Adventureworks database from Microsoft codeplex website. Then I attached the .mdf file to my database. and run the following quesry.

CREATE DATABASE AdventureWorks2008 ON (FILENAME = 'c:\AdventureWorks2008R2_Data.mdf'), (FILENAME = 'c:\AdventureWorks2008_Log.ldf') FOR ATTACH;

The problem is now I have two databases in SQL server 1. Adventureworks2008R2 ( Read-Only) 2. AdventureWorks2008 (Read-Only).

Both databases are read only and I don't know Why?

Please help me.

Thank you in Advance.

Ronak

 


Thursday, October 31, 2013 - 11:07:54 AM - william liu Back To Top

It's useful to me. Thank your 

I look forward to your another tip.^^



Thursday, October 31, 2013 - 10:21:59 AM - Manvendra Deo Singh Back To Top

Thank you Alkesh.

Tlog can help you to recover your deleted data. but we need a backup file to recover it.

I am writing another tip regarding this to recover a deleted data with the help of TLOG file.

Regards

Manvendra


Thursday, October 31, 2013 - 10:13:19 AM - Alkesh Kansara Back To Top

Its  really a very important and informative post.

Is it possible to recover any deleted data from transaction logs?


Learn more about SQL Server tools