Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Welcome to my blog. I work as Database lead at Synaptic Digital. Hope you find some interesting stuff here.
Browse by Tags · View All
BRH 17
SQL Server 15
#SQL Server 11
#BI 10
#TSQL 8
TSQL 8
BI 7
SSRS 6
#SQLServer 6
SSRS 2008R2 5

Archive · View All
January 2011 6
December 2010 5
September 2012 4
May 2012 4
March 2011 4
November 2012 2
October 2012 2
January 2012 2
February 2011 2
November 2010 2

Less number of log records in FULL recovery mode

Jan 3 2012 3:12PM by Chintak Chhapia   

Note:- Please refer to post mentioned here to see why there are less number of log records. Actully this is not minimal logging but its less number of log records/

As per BOL ( here and here),  minimal logging is only possible in SIMPLE and BULK-LOGGED recovery mode using bulk operations  and FULL recovery mode fully logs all transactions.

But, I have seen that the minimal logging is happening for when we load data into tables using SSIS, for some combination minimal logging is happening even if database in FULL  recovery mode. Below is the create to create such database and source table.

use master
go
if db_id('minimalLogTest') is not null    
begin
    alter database minimalLogTest set single_user with rollback immediate
    drop database minimalLogTest
end    
go
Create database minimalLogTest
go
Alter database minimalLogTest set recovery FULL
go
use minimalLogTest
go     
if object_id('sourceTable') is not null    
    drop table sourceTable
go
Create table sourceTable
(
    id int identity(1,1),
    name nvarchar(10) default 'abc',
    dateOfbirth date default '20000101'        
)
go

--now insert 1000000 rows into source table
declare @i int = 0
Set nocount on;
begin transaction
    while ( @i < 1000000)
    begin
        insert into sourceTable default values
        select @i = @i + 1    
    end
commit

--now take a full backup to change database really into FULL recovery mode
Backup database minimalLogTest to disk =N'c:\minimalLogTest.bak'
go

if object_id('targetHeapTable') is not null    
    drop table targetHeapTable
go
Create table targetHeapTable
(
    id int ,
    name nvarchar(10) ,
    dateOfbirth date 
)
go

if object_id('targetTable') is not null    
    drop table targetTable
go
Create table targetTable
(
    id int ,
    name nvarchar(10) ,
    dateOfbirth date 
)
go

Create clustered index ix_targetTable_id on targetTable(id)
go

I haved used below queries to track number of log records generated

select AllocUnitName,* from fn_dblog(NULL,NULL) where AllocUnitName like '%targetHeapTable' 
go 
select AllocUnitName,* from fn_dblog(NULL,NULL) where AllocUnitName like '%targetTable%' 

In this test, I have tested for following things

  1. OLEDB destination (with FASTLOAD) for HEAP without any indexes
  2. OLEDB destination (with FASTLOAD) for table with Clustered Index
  3. SQL server destination for HEAP without any indexes
  4. SQL server destination for table with Clustered Index
  5. SQL server destination for non-empty table with Clustered Index

Same package is attached below.

Between each test, I had taken log backups so that count of log entries is manageable.

First test is to start with HEAP. Before loading, if we run below query no rows is returned

select AllocUnitName,* from fn_dblog(NULL,NULL) where AllocUnitName like '%targetHeapTable' 
--Zero rows 

Now, start loading data


 

Now, when we run below queries..

select COUNT(*) from fn_dblog(NULL,NULL) where AllocUnitName like '%targetHeapTable' 
--4622 rows 
select COUNT(*) from targetHeapTable 
--1000000 rows 

Even if we look into detailed records, we found entries simpler to what we got in bulk operations in SIMPLE and Bulk-Logged recovery mode.

Even if run below query, we didn’t get any rows.

select * from fn_dblog(NULL,NULL) where AllocUnitName like '%targetHeapTable' and Operation like 'LOP_INSERT_ROWS' 

So, looking at these details, we can conclude that minimal logging operation is happening  even if database id in FULL recovery mode.

Same way even if you run the task again which inserts data into HEAP using OLEDB FastLoad, you can find that minimal logging is happening with each load.

I run these tests for all other combinations and found out below detail:

Combination Minimally logged
OLEDB destination (with FASTLOAD) for HEAP without any indexes YES
OLEDB destination (with FASTLOAD) for table with Clustered Index NO
SQL server destination for HEAP without any indexes YES
SQL server destination for empty table with Clustered Index YES
SQL server destination for non-empty table with Clustered Index NO

Note:- I have not considered scenarios while backup is running or tables involved in replication

This can be helpful in scenario, where  want to load data and not want to switch between recovery modes (al though, as its not confiremed by Microcost, this behaviour can be changed in future releases).   Only thing to take note here is Point-in-time restore will not work in that log backup.

Tags: #SQL Server, SQL Server, BI, #BI, SSIS, Bulk Logging,


Chintak Chhapia
40 · 5% · 1457
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • This is a good one, Chintak!

    Although this is a surprising find to me, I can understand to some extent why SQL Server would perform minimal logging in case of HEAPs (without any indexes), what I am not able to understand is why SQL Server would perform minimal logging for an SQL server destination of an empty table with clustered index defined (which is not the case when an OLEDB destination is used)?

    commented on Jan 3 2012 10:44PM
    Nakul Vachhrajani
    4 · 33% · 10690
  • Nakul, I have tried different things to find out the resaon, but not able to find out the cause.

    commented on Jan 3 2012 11:13PM
    Chintak Chhapia
    40 · 5% · 1457
  • Nice one Chintak, thanks for sharing.

    commented on Jan 6 2012 1:16AM
    Hardik Doshi
    20 · 9% · 2839

Your Comment


Sign Up or Login to post a comment.

"Less number of log records in FULL recovery mode" rated 5 out of 5 by 3 readers
Less number of log records in FULL recovery mode , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]