Here is the reason I am writing this post
As per the above entry from MSDN, there cannot be any minimally logged operations in full recovery mode, which is what I have always understood since full recovery model requires point in time recovery. In order to understand my dilemma we need to first define what minimally logged operations are.
Common examples include INSERT..SELECT, BULK INSERT, Select ..INTO etc. But more importantly, we need to define how does a minimally logged operation look? Is it by looking for entries in the BCM page2 or by looking up entries in the T log using the fn_db_log function. For the purpose of this post I will look at both.
So here is my baseline.
I have a new database created in Full Recovery model with an empty table into which I will INSERT.. SELECT some data. There are some pre-requites to using INSERT SELECT which can be found here 3 . A full and log backup of the database is taken before starting the bulk insert.
Here are the pre-requisites for quick reference. I have put an X against the criteria I am not following
- The recovery model of the database is set to simple or bulk-logged. X
- The target table is empty or is a nonempty heap.
- The target table is not used in replication.
- The TABLOCK hint is specified for the target table.
So based on the above, my insert operation should be fully logged. Right? Wrong !!
Here is the screenshot to prove it. Notice the recovery model is FULL and I insert 121317 rows of data using the TABLOCK hint but have only 6684 rows in the result of fn_db_log.
Now notice the same code executed without TABLOCK hint in FULL RECOVERY MODEL, notice the number of entries in the t log 148192
Based on the above I don’t understand why full recovery model would allow minimally logged operations. It would mean that there is potential for data loss in Full Recovery model since point in time recovery is lost it would also mean that BULK Logged recovery model is now redundant since we can implement the behavior directly within FULL RECOVERY MODEL. If anybody can provide an explanation for the above behavior I would sincerely appreciate it or at least find a mistake in the way I performed the test since this behavior has got me stumped.
Now if I look at Page 7 BCM page of the database there is a different story altogether. THE BCM page says there are no minimally logged extents, this is true with and without TABLOCK.
But once I change the recovery model to Bulk Logged I get the below output.
So it seems that the article is true when it comes to the BCM page but not entries in the TLog.
My code
IF NOT EXISTS (select 1 from sys.databases where name like'%recoverymodels%')
BEGIN
create database recoverymodels
END
GO
/****** Object: Table [Sales].[SalesOrderDetail] Script Date: 5/15/2015 1:26:51 PM ******/
if exists (select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '%SalesOrderdetail%')
DROP TABLE dbo.[SalesOrderDetail]
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
PS: – I am using SQL 2014
References
1 https://technet.microsoft.com/en-us/library/ms191244%28v=sql.105%29.aspx
2 https://technet.microsoft.com/en-us/library/ms190950%28v=sql.105%29.aspx
3 https://technet.microsoft.com/en-us/library/ms174335%28v=sql.105%29.aspx
Please Consider Subscribing
