Solved

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

Posted on 2013-01-29
15
865 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now