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
