Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Minimal Logging Enhancement in SQL Server 2008


Minimal Logging Enhancement in SQL Server 2008

Author
Message
Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 403
Hi folks,

SQL Server 2008 has enhanced INSERT..SELECT statement to allow minimal logging in certain cases, documented here:
http://msdn.microsoft.com/en-us/library/ms174335(v=sql.100).aspx (See section: Best Practices for Bulk Loading Data)

We planned to use this feature in a situation where we wanted to insert millions of rows in an empty heap (or an empty clustered index) and made sure that all pre-requisites are fulfilled.
a) The target table is a heap or an empty B-Tree,
b) TABLOCK is being used on the target table,
c) The database is in SIMPLE recovery model,
d) The target table is not used in replication.


The INSERT is done from a SELECT from a temp table (#temp) having around 300,000 rows (300,000 distinct Ids) joined with another table that has ~500 rows for each of the distinct #temp.Id. Thus, 500 x 300,000 = 150 million rows (around 10 GB)

We are on Microsoft SQL Server 2008 (SP1) - 10.0.2740.0 (X64).
But it seems the minimal logging is not working for us, as I see the transaction log growing to almost 10GB, during the insert if no other processes are running.
We also face transaction log full issue, when other processes are running along with this insert. (Transaction Log size = 20 GB)

There were no backups being run at the time of testing, which may have caused full logging.

Is the minimal logging using INSERT..SELECT available in all versions of SQL Server 2008 or did it get included in one of the Cumulative Updates?

I would really like to make use of this new feature in SQL Server 2008.
Any ideas on how to achieve it, would be appreciated.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47404 Visits: 44399
What recovery model is the database in?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 403
Hi Gail,

As mentioned, the database is in SIMPLE recovery model.

Best,
Yusuf
Vedran Kesegic
Vedran Kesegic
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1255
Heap should have no indexes. Try with TABLOCKX hint.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6362 Visits: 13687
are you doing the insert in batches? after the first batch the table will no longer be empty so you will no longer get minimal logging.

---------------------------------------------------------------------
Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 403
Hi Vedran,
I couldn't quite connect your two sentences: "Heaps have no indexes" and "Try using TABLOCKX".
Nevertheless, I did try TABLOCKX to no avail.
Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 403
george sibbald (3/26/2013)
are you doing the insert in batches? after the first batch the table will no longer be empty so you will no longer get minimal logging.


Not doing insert in batches. It is a single big insert..select
Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 403
Vedran Kesegic (3/26/2013)
Heap should have no indexes. Try with TABLOCKX hint.

Hi Vedran,
I couldn't quite connect your two sentences:
"Heaps should have no indexes" (heaps can have non clustered indexes, can't they?),
and
"Try with TABLOCKX hint".
Nevertheless, I did try TABLOCKX to no avail.
Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 403
I figured out the reason why minimal logging was not being done in my case.

All the pre-requisites mentioned in BOL to achieve minimal logging were met.
Though, a few hours later I noticed that my target table has an IDENTITY column, and I thought of removing the identity property or the identity column itself and see if it makes any difference.

And lo, minimal logging was achieved.

Not very sure of the internals behind it, but if I remove the identity column from the target table, the 150 million records insert is minimally logged.

Do you think BOL should mention this exception or add it in the list of pre-requisites for minimal logging during normal INSERT..SELECT operation?
Vedran Kesegic
Vedran Kesegic
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1255
Yusuf Ali Bhiwandiwala (3/27/2013)
Vedran Kesegic (3/26/2013)
Heap should have no indexes. Try with TABLOCKX hint.

Hi Vedran,
I couldn't quite connect your two sentences:
"Heaps should have no indexes" (heaps can have non clustered indexes, can't they?)

My sentence was: "Heap should have no indexes". Singular, not plural, your particular heap you want to achieve minimal logging.
If you have NC indexes on the heap, they will be fully logged on nonempty tables (after the first batch if you bulk import in batches, your table is no longer considered empty) even if table itself is minimally logged.
Maybe identity problem would disappear if you choose that value is imported instead of auto-generated.
Certainly, that should be in the BOL. You can add a comment there for others to know.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search