Log records in the transaction log

by

The question:

The log record for data modifications in the transaction log: does it only contain the before and after states for the rows and columns affected or does it contain the before and after states for all the columns (for the rows affected), i.e. does the log record contain the whole row or only part of the row that is modified?

Thanks

Edit: I have now created an experiment and it seems that number of columns in the table does not affect the size of the transaction log for data modifications, i.e. if I update col2 in a table then it does not matter if the table has 5 columns or 100 columns, the growth of the transaction log will be the same.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

To expand on what Tibor Karaszi mentioned in the comment section about many other factors. I wrote some demo code showing only three scenarios. You can already see the variance in the number of log records, length, and content. I did put comments in between explaining each test.

The description column will give you a good idea about what the log record is about.

If you want to see the content of the RowLog Contents X columns, see this post.

USE master;
GO
DECLARE @SQL nvarchar(1000);

IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = N'LogRecordMod')
  BEGIN
    SET @SQL = 
      N'USE [master];
       ALTER DATABASE LogRecordMod SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
       USE [master];
       DROP DATABASE LogRecordMod;';
    EXEC (@SQL);
  END;
ELSE
  BEGIN
    PRINT 'Database does not exist'
  END;
GO

CREATE DATABASE LogRecordMod;
GO

/*
Change settings to reduce the number of log records
*/
USE master;
GO
ALTER DATABASE LogRecordMod SET RECOVERY SIMPLE;
GO
ALTER DATABASE LogRecordMod SET AUTO_CREATE_STATISTICS OFF;
GO

/*
Create a table
Insert data
*/
USE LogRecordMod;
GO
DROP TABLE IF EXISTS dbo.testMod;
GO
CREATE TABLE dbo.testMod (
  ProductID integer,
  StoreID integer,
  Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_testMod_ProductID PRIMARY KEY CLUSTERED (ProductID)
);

CREATE NONCLUSTERED INDEX IX_testMod_StoreID
  ON dbo.testMod (StoreID);   
GO  

INSERT INTO dbo.testMod (ProductID, StoreID) VALUES 
  (1,1001),
  (2,1002),
  (3,1003),
  (4,1004),
  (5,1005),
  (6,1006);
GO

CHECKPOINT;
GO

--Test one: modify primary key
BEGIN TRAN;
GO
UPDATE dbo.testMod SET ProductID = 7
WHERE ProductID = 1;
GO
CHECKPOINT;
GO
--check the log record generated due to a change in primary key
--You can ignore the last four records, those are related to checkpoint and not the update statement
SELECT * FROM fn_dblog (NULL, NULL);
GO
--once you examined the log records, run this 
COMMIT TRAN;
GO
CHECKPOINT;
GO
SELECT * FROM fn_dblog (NULL, NULL);
GO

--Test two: modify a column with non-clustered index
BEGIN TRAN;
GO
UPDATE dbo.testMod SET StoreID = 1007
WHERE ProductID = 7;
GO
CHECKPOINT;
GO
--check the log record generated due to a change in primary key
--You can ignore the last four records, those are related to checkpoint and not the update statement
SELECT * FROM fn_dblog (NULL, NULL);
GO
--once you examined the log records, run this 
COMMIT TRAN;
GO
CHECKPOINT;
GO
SELECT * FROM fn_dblog (NULL, NULL);
GO

--Test three: modify a column not part of any index
BEGIN TRAN;
GO
UPDATE dbo.testMod SET Timestamp = GETDATE()-7
WHERE ProductID = 7;
GO
CHECKPOINT;
GO
--check the log record generated due to a change in primary key
--You can ignore the last four records, those are related to checkpoint and not the update statement
SELECT * FROM fn_dblog (NULL, NULL);
GO
--once you examined the log records, run this 
COMMIT TRAN;
GO
CHECKPOINT;
GO
SELECT * FROM fn_dblog (NULL, NULL);
GO

/*
Clean up
Drop the database
*/
USE master;
GO
DROP DATABASE IF EXISTS LogRecordMod;
GO

Method 2

The transaction log records all transactions and the database modifications that are made by each transaction. Untouched columns by updates don’t count for it. It records pages changes. It’s about pages not records.

There is no previuos page version. previuos page version is stored in the full database backup. Then applying each page changes in the tlog, you can roll over all transaction done after full restore.

Rollback operations are also logged. Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. This reserved space is freed when the transaction is completed.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment