Sql-server – Why is log reserve negative in the SQL Server compensation log when using fn_dblog command

database-internalssql servert-sql

I was using the fn_dblog command and was not able to get why the log reserve was negative.

NUTS

Best Answer

The function is technically undocumented, so I can't find any official word on this. However, my understanding is that those "compensation" rows are related to a rollback. A rollback is essentially erasing some previous log records, so it frees up some space in the transaction log. The negative values are saying "give this much free space back to the log file."

You can observe this by performing a demo like this:

USE [master]
GO
DROP DATABASE IF EXISTS LogUsageTest;
GO
CREATE DATABASE LogUsageTest;
GO
ALTER DATABASE [LogUsageTest] 
SET RECOVERY SIMPLE 
WITH NO_WAIT;
GO
USE LogUsageTest;


CREATE TABLE dbo.A
(
    Id int IDENTITY(1,1) NOT NULL,
    Something int NOT NULL

    CONSTRAINT PK_A PRIMARY KEY (Id)
);
GO
CHECKPOINT;

That sets up a database in the SIMPLE recovery model with one table in it, then clears the log (with a checkpoint). Here's the baseline transaction log usage:

screenshot of disk usage report in SSMS showing 5.5 percent space used in the log file

Now I'll run an insert inside a transaction:

BEGIN TRANSACTION;
INSERT INTO dbo.A
    (Something)
SELECT
    v1.number
FROM master.dbo.spt_values v1;

We've gone from 5.5% usage to 13%:

screenshot of disk usage report in SSMS showing 13 percent space used in the log file

And now we'll roll that insert back:

ROLLBACK;

screenshot of disk usage report in SSMS showing 8.9 percent space used in the log file

And we've gone from 13% down to 8.9%.

You might notice that the compensations don't free up all the log space used by the insert. In my test, the inserts reserve 178 bytes, while the rollbacks free up -74 bytes.

I think this is because the log reservations are extra big in case of page splits, which don't get rolled back. But that's just an educated guess.

Related Question