June 20, 2018 at 7:21 am
Hi
Running SQL 2017 Enterprise. I am trying to estimate log space requirements for rebuilding clustered indexes ONLINE with SORT_IN_TEMPDB set to ON. To understand what was going on under the hood I emptied the transaction log and issues a REBUILD operation against a partition on a table containing 23,802,158 records. The reserved space used by the index was 1103 MB so row size is ~ 48 Bytes. After rebuilding the index I checked out the contents of the LOG file using the following SQL.
SELECT allocunitname, operation, context, COUNT_BIG(*) as counts, SUM(CONVERT(BIGINT,[log Record Length])) as [log Record Length]
FROM sys.fn_dblog(NULL, NULL)
GROUP BY allocunitname, operation, context
ORDER BY [log Record Length] DESC
The results showed that 95% of the log usage (based on aggregated [log Record Length]) was dedicated to the following operation = LOP_INSERT_ROWS, context = LCX_CLUSTERED, details below. One entry for each row in the index, which makes sense given the context.
operation context counts log Record Length
LOP_INSERT_ROWS LCX_CLUSTERED 23802158 3449666256
The average size of each log entry is ~ 155 bytes, if we subtract 62 bytes (Log Record Fixed Length) and then 48 Bytes (Length of each record in the clustered index in Bytes) we are left with 45 Bytes. Does anyone know what is logged in the remaining 45 Bytes?
Cheers
Rob
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply