How to Track Database Transaction Log in SQL Server 2012

SQL Server database transaction logs are the most important part of a database. Every DBA and database developer should understand Database Transaction logs, this is a huge topic of SQL Server. 

About database Transaction logs

SQL Server databases have a transaction log that records all transactions and the database modifications made by each transaction. This contains enough information to undo all changes made to the data file as part of any separate transaction. If you want to see the data in a given database's transaction log, there is an undocumented SQL Server command called DBCC LOG. This command is used to view the transaction log for a specific database. The DBCC Log command only shows log information and this command will not provide you detailed information. 

Syntax

DBCC log ({dbid|dbname}, [, type= {-1|0|1|2|3|4}])

Parameters:

Dbid or dbname: Enter either the dbid or the database name of the database in question.

Type is the type of output as in the following:

1. minimum information (operation, context, transaction id)
2. More information (plus flags, tags, row length, description)
3. Very detailed information (plus object name, index name, page id, slot id)
4. Full information about each operation
5. Full information about each operation plus hexadecimal dump of the current transaction log's row.
   

1. The full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID by default type = 0.

Note: DBCC LOG can also be called as a system function for use in a SELECT statement or other queries via the fn_dblog function.

Syntax

USE MASTER

GO

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

The fn_dblog () function accepts two parameters.

The first is the starting log sequence number, or LSN. You can also specify NULL, which means it will return everything from the start of the log.

The second is the ending LSN. You can also specify NULL, which means you want to return everything to the end of the log .

I will create a small Test Environment and how to work with SQL Server Transaction logs using DDL and DML oprations, like a create, insert, update and delete.

The test environment

 

Create the test environment with the following:

 --Create Test DB.
USE [master];
GO
CREATE DATABASE TrackingDBLog;
GO
-- Create tables.
USE TrackingDBLog;  GO  CREATE TABLE [Test] (
[ID] INT IDENTITY (1, 1),
[Date] DATETIME DEFAULT GETDATE (),  [Name] CHAR (25) DEFAULT 'A');

Now you can check all information and processes that have been used by SQL Server to create the database and table. We will run the following code to check the log file for this newly created database to check the processes and steps of SQL Server.

----------------------------------------------------------------  DBCC log (TrackingDBLog,1)
GO
select Top 10 * FROM fn_dblog(null,null)
USE TrackingDBLog;
GO
select COUNT(*) from fn_dblog(null,null)

  ----------------------------------------------------------------

We have created a dummy database and a blank table. You can check the logs by using this function to get the details for all the processes used to create the database and table. Look at the following code to see the data in the transaction log file.

 

The DDL Scenario

 

Create the DDL Scenario with the following:

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

 

DML Scenario

 

Create the DML Scenario with the following.

 

I will now run a little DML script to check how data insertion, updates 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 TrackingDBLog
GO
INSERT INTO [Test] DEFAULT VALUES ;
GO 15
SELECT TOP 6 * FROM [Test]
SELECT count (*) TotalCount FROM [Test]
GO
UPDATE [Test]
SET [Name]='B'
WHERE [ID] >3
And [ID] <=6
GO
SELECT TOP 6 * FROM [Test]
SELECT count(*) TotalCount FROM [Test]
GO  DELETE [Test]
WHERE [ID]>6
GO
SELECT * FROM [Test]
SELECT count (*) TotalCount FROM [Test

This is the output of the preceding query.

 

Let's check our database log file again using the following script:

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

 

 

 

How a backup relates with the database transaction log

Now to 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 * FROM fn_dblog(null,null)
GO
BACKUP DATABASE TrackingDBLog TO DISK = 'D:\backup\TrackingDBLog.bak'
GO  SELECT COUNT(*) FROM fn_dblog(null,null)
  ----------------------------------------------------------------

 

Let's check our database log file again using the preceding script.

 

 

 

In the next article I will explain:

• How to find the user who DROPed or DELETEd statements on your SQL Server Objects.

•  How to recover deleted data and tables on SQL Server using the Transaction Log.