Registros de log no log de transações

Para expandir o que Tibor Karaszi mencionou na seção de comentários sobre muitos outros fatores. Eu escrevi um código de demonstração mostrando apenas três cenários. Você já pode ver a variação no número de registros de log, comprimento e conteúdo. Eu coloquei comentários entre explicar cada teste.

A coluna de descrição lhe dará uma boa ideia sobre o que é o registro de log.

Se você quiser ver o conteúdo das RowLog Contents Xcolunas, veja este 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

O log de transações registra todas as transações e as modificações do banco de dados que são feitas por cada transação. Colunas intocadas por atualizações não contam para isso. Ele registra as alterações de páginas. É sobre páginas não registros.

Não há versão da página anterior. a versão da página anterior é armazenada no backup completo do banco de dados. Em seguida, aplicando cada alteração de página no tlog, você pode rolar todas as transações feitas após a restauração completa.

As operações de reversão também são registradas. Cada transação reserva espaço no log de transações para certificar-se de que existe espaço de log suficiente para suportar uma reversão causada por uma instrução de reversão explícita ou se um erro for encontrado. A quantidade de espaço reservado depende das operações realizadas na transação, mas geralmente é igual à quantidade de espaço usada para registrar cada operação. Esse espaço reservado é liberado quando a transação é concluída.