locked
Bulk operations in Full recovery model RRS feed

  • Question

  • why full recovery model would allow minimally logged operations?

    http://www.enabledbusinesssolutions.com/blog/?p=1222


    http://www.t-sql.ru

    Saturday, April 2, 2016 10:53 AM

Answers

  • Well, it depends on what you mean with a minimally logged transaction. TRUNCATE TABLE could be considered to be minimally logged, since it only logs extent deallocations. On the other hand, that is enough for a restore to point in time - you don't need the data in the individual rows for that.

    The script below illustrates what is going on. The important thing is the addition of sum of the log record length, which the blog author failed to consider. You will find that with full recovery the difference between with and without TABLOCK is not equally dramatic. Furthermore the script shows that a restore to point in time is possible. Change to BULK_LOGGED and compare.

    With TABLOCK, the pages or extents can be logged one at a time, so you get considerably few log records, but the log records are beefier. The total size is still smaller, and I would guess is because each log record has a fixed part.

    Why DBCC PAGE reports MIN LOGGED I don't now.

    create database recoverymodels
    ALTER DATABASE recoverymodels SET RECOVERY FULL -- BULK_LOGGED
    GO
    USE recoverymodels
    go
    CREATE TABLE dbo.[SalesOrderDetail]( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL , [LineTotal] float, [rowguid] varchar(128), [ModifiedDate] datetime,) ON [PRIMARY]
    GO
    BACKUP DATABASE recoverymodels TO DISK = 'C:\temp\slask.bak' WITH INIT
    go
    INSERT SalesOrderDetail WITH (TABLOCK)
       SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail
    go
    SELECT COUNT(*), SUM([Log Record Length]) FROM fn_dblog(NULL, NULL)
    go
    BEGIN TRANSACTION mark1 WITH MARK 'mark1'
    DELETE TOP (2000) SalesOrderDetail
    COMMIT TRANSACTION
    go
    TRUNCATE TABLE SalesOrderDetail
    go
    BACKUP LOG recoverymodels TO DISK = 'C:\temp\slask.bak'
    go
    INSERT SalesOrderDetail    SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail
    go
    SELECT COUNT(*), SUM([Log Record Length]) FROM fn_dblog(NULL, NULL)
    SELECT COUNT(*) FROM SalesOrderDetail
    go
    go
    USE tempdb
    go
    RESTORE DATABASE recoverymodels FROM DISK = 'C:\temp\slask.bak' WITH FILE = 1, NORECOVERY, REPLACE
    RESTORE DATABASE recoverymodels FROM DISK = 'C:\temp\slask.bak' WITH FILE = 2, STOPATMARK = 'mark1', RECOVERY
    go
    SELECT COUNT(*) FROM recoverymodels..SalesOrderDetail
    go
    DROP DATABASE recoverymodels

    • Proposed as answer by Eric__Zhang Saturday, April 2, 2016 1:37 PM
    • Marked as answer by Alexey Knyazev Saturday, April 2, 2016 2:02 PM
    Saturday, April 2, 2016 1:03 PM