Оператор INSERT с подсказкой TABLOCK может быть минимально зарегистрирован в режиме полного восстановления в SQL Server 2014, почему?

Question

От Microsoft Понимание минимально зарегистрированных операций

Минимально зарегистрированные операции доступны только в том случае, если ваша база данных находится в режиме массовой регистрации или в простом режиме восстановления.

Код ниже объясняет, что я видел.

use master;

IF DB_ID('RecoveryETL') IS NOT NULL
BEGIN
    ALTER DATABASE RecoveryETL SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
    DROP DATABASE RecoveryETL;
END

--Create sample database for testing
CREATE DATABASE RecoveryETL 
ON PRIMARY
    (NAME = N'RecoveryETL_Date', FILENAME = N'C:\SQLDATA\RecoveryETL_Data.Mdf', SIZE = 1024MB, MAXSIZE = UNLIMITED, FILEGROWTH = 50MB)
LOG ON
    (NAME = N'RecoveryETL_Log', FILENAME = N'C:\SQLDATA\RecoveryETL_Log.LDF', SIZE = 100MB, MAXSIZE = 1024MB, FILEGROWTH = 25MB)
GO

--Set Recovery Mode to Full
ALTER DATABASE RecoveryETL SET RECOVERY FULL ;
GO

USE RecoveryETL;
GO

--Immediately perform a full backup otherwise database will remain in simple mode
BACKUP DATABASE RecoveryETL
TO DISK = 'C:\Database Backups\RecoveryETL_1.bak'
WITH RETAINDAYS = 90 
, FORMAT
, INIT
, MEDIANAME = 'RecoveryETL'
, NAME = 'RecoveryETL-Full Database Backup'
, COMPRESSION ;
GO

--Create a Testing Table
IF OBJECT_ID('dbo.HeapTable') IS NOT NULL 
DROP TABLE dbo.HeapTable

CREATE TABLE HeapTable
(
    Fld1 INT, 
    Fld2 INT, 
    FId3 INT,
    ModDate Datetime
)

--Insert Records W/O TABLOCK hint
INSERT INTO HeapTable (Fld1, Fld2, FId3, ModDate)
    SELECT TOP 10000 
        SalesOrderDetailID, SalesOrderDetailID + 100, SalesOrderDetailID + 99, GETDATE()
    FROM 
        AdventureWorks2012.Sales.SalesOrderDetail

--Is it minimum logged? No, more than 10,000 log records
SELECT * 
FROM sys.fn_dblog(null, null)
WHERE AllocUnitName LIKE '%HeapTable%' 

--Truncate logs
CHECKPOINT;

BACKUP LOG RecoveryETL
TO DISK = 'NULL'
WITH RETAINDAYS = 90
, NOINIT
, MEDIANAME = 'RecoveryETL'
, NAME = 'RecoveryETL-Log Backup'
, COMPRESSION ;
GO

--Make sure logs are gone
SELECT * 
FROM sys.fn_dblog(null, null)
WHERE AllocUnitName LIKE '%HeapTable%' 

--Insert records WITH TABLOCK hint
INSERT INTO HeapTable WITH(TABLOCK)(Fld1, Fld2, FId3, ModDate)
    SELECT TOP 10000 
        SalesOrderDetailID, SalesOrderDetailID + 100, SalesOrderDetailID + 99, GETDATE()
    FROM 
        AdventureWorks2012.Sales.SalesOrderDetail

--Minimum logged here!!!!!
SELECT * 
FROM sys.fn_dblog(null, null)
WHERE AllocUnitName LIKE '%HeapTable%' 

Я также пытался восстановить базу данных на определенный момент времени, используя резервную копию журнала с минимальным количеством зарегистрированных журналов, как и ожидалось, я не смог вывести базу данных из состояния восстановления, где я могу сказать, что они действительно минимально зарегистрированы. Официальная документация неточна?

Answer 1

где я могу сказать, что они действительно минимально зарегистрированы. Официальная документация неточна?

Нет, вы не правы.

Да, были изменения в том, как INSERTs полностью зарегистрированы, но под full recovery model даже с TABLOCK они все еще полностьюзарегистрированы.

В SQL SERVER 2005-2008INSERT под full recovery model был записан построчно, так что если вы сделали 10000 строк INSERT в лог-файле вы видели не менее 10000 LOP_INSERT_ROW операции (см. мой первый рисунок)

Теперь я сделал то же самое INSERT на SQL SERVER 2016 сначала под BULK_LOGGED модель восстановления, а затем под FULL (Вы можете увидеть результат на моей второй картинке). Под BULK_LOGGED Вы можете видеть, что были зарегистрированы только настройки растрового изображения, поэтому операция действительно minimally logged, но при полном восстановлении модера картина иная. Да вы не видите 10000 LOP_INSERT_ROW строк больше, но теперь вы можете увидеть 38 LOP_FORMAT_PAGE операции, и очень лог-строка, соответствующая этой операции, имеет 8276 байты, т.е. 8Kb, один page, Эти 38 страниц являются полностраничными изображениями, которые были отформатированы во время INSERTкак это было сделано еще в 2008 году для INDEX REBUILD операции. Так что операция еще fully loggedинформация, которая поступает в журнал, достаточна для полного восстановления операции, а не только для отката, как в случае minimally logged операции. Так INSERT в кучу даже с TABLOCK подсказка - все еще полностью зарегистрированная операция, но теперь это даже эффективно зарегистрированная операция. Я пытался даже сделать резервную копию журнала после моей вставки под FULL и я смог восстановить из этого журнала с stop at во время до завершения резервного копирования журнала.