Solved

How to truncate and reload a large table in SQL Server without generating transaction logs

Posted on 2013-01-29
15
971 Views
Last Modified: 2013-01-30
I have a large table (1,000,000+ rows) in a SQL Server 2005 database that I truncate and reload from scratch 3-5  times per week.  This table is for reporting purposes only and the data in this table does not need to be restored in the event of a system failure (I can easily restore the data by rerunning the program that normally truncates and reloads it).

Whenever I do this, there are a lot of transactions generated and my transaction log grows very large.

Is there a way to load this table without generating all of the transaction logs?

In Oracle, I would create the table as "UNRECOVERABLE" or with "NOLOGGING".

Is there something similar for SQL Server?
0
Comment
Question by:jbaird123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 38831410
How are you loading the table?  Have you looked into switching to Bulk logged recovery during the load then switching back to full?

http://msdn.microsoft.com/en-us/library/ms189275.aspx
0
 

Author Comment

by:jbaird123
ID: 38831535
tim_cs,

I am loading the table using an ETL process created in Talend.  There is a "bulk insert" option for loading data in bulk, but I am not sure what it does.  Unfortunately, there isn't any documentation explaining what that option does.  I wouldn't know how to tell if using this option was having the desired affect or not.

Your suggestion to switch to bulk logged mode will affect the entire database - correct?  I was hoping for a setting that could be done on a table by table basis (creating certain tables without logging and others with logging).

Thanks.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38831547
SQL Server does not have any equivalent to "UNRECOVERABLE".

You could put that table in tempdb (under a non-temp name) or another database to move the activity away from the main db's log.  

For tempdb, if the SQL service went down and came back up, of course you'd have to reload the table.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:liija
ID: 38832184
For DW purposes, you could change the recovery mode permanently, SIMPLE is enough for DW.
(Management Studio/Database properties/Options)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38832325
The log will grow the same amount regardless of the recovery model.

SIMPLE just allows the resulting log space to be re-used without backing up the log first.
0
 
LVL 6

Expert Comment

by:liija
ID: 38832378
Scott is right, here we are talking about one table - so recovery mode might not help at all in this case.

Just to be sure, even though you are talking about truncating the table, you are of course using TRUNCATE table instead of DELETE?
DELETE creates logs, TRUNCATE doesn't.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38832595
TRUNCATE does log: it logs at the page level instead of the row level, so it's less overhead.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38832599
The tempdb database only inherently has reduced logging compared to other dbs, since SQL "knows" it will never have to recover tempdb later, such as when SQL restarts (tempdb is always rebuilt from scratch when SQL starts).
0
 
LVL 6

Expert Comment

by:liija
ID: 38832633
TRUNCATE doesn't log data. It logs page deallocations. That means less log.
0
 

Author Comment

by:jbaird123
ID: 38832661
All:

Thanks for the feedback.  

Based on the information above, it seems like the best option is to move the table to a different database altogether and set the recovery mode to simple.  I don't want to use the tempdb since if I restart the server I don't want to rebuild the table.  

Is there any better solution?

Thanks.
0
 
LVL 6

Expert Comment

by:liija
ID: 38834276
Is your ETL-process just copying the data into SQL Server - or is there some other logic? Is it doing any updates afterwards to the table- which could be moved into the ETL process itself -or moved to the source query?

If not, it would be interesting to know what Talend does in the background. You could use SQL Server profiler to find out what is really going on while the data is written into SQL Server table. If you are just copying data from table to another, might be that you cannot do anything to Talend internal logic though.
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 38834365
If your need is to completely reload the table and "truncate table" logs too much for you (even if I would say not), you can consider dropping and re-creating the table itself.
Just script it in your SSMS by right-clicking the table, "script table as", "drop and create to".
And remember to include non-pk indexes.

This is the most cruel way to reload a table, but I think it is the less-logging (even if, I repeat, I would use "truncate table").

Regards.
0
 
LVL 6

Expert Comment

by:liija
ID: 38834381
True - and indexes of course. If you are rebuilding indexes after the load - it might also cause log increase.

Dropping indexes before the load and recreating them after the load is finished might cause less log. In many cases the load process is also faster - taken into account the time that recreating indexes takes.
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 38834401
Yes, in that case I would script the indexes re-creation just after the table creation.
Creating indexes over an empty table should log only a little amount of data...
Obviously the load process will be slowed down by the indexes update, but that is a disadvantage that usually worth the cost.

Regards.
0
 

Author Closing Comment

by:jbaird123
ID: 38835216
Thank you.  I will put the table in another database with a simple recovery model.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question