Bulk Load - Transaction Log Fillup -- sql server programming

Robert Klemme
9/29/2006 12:00:00 AM

[quoted text, click to view]

You can temporarily switch the recovery model but I do not think this is
a good idea - especially if in parallel there is other activity going
on. Another solution is to temporarily make more space available to the
TX log. After the operation you can reduce TX log size again (backup etc.).

Maybe you can also use recovery model bulk logged and use some form of
export / import (BCP, bulk insert).

Regards

Tibor Karaszi
9/29/2006 12:00:00 AM

Try to use any of the try bulk loading operations, and you can switch the database to BULK_LOGGED or
SIMPLE recovery model for the duration of the bulk loading process. Just make sure you discuss this
with your DBA as it will affect backup/restore options.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

[quoted text, click to view]

Hari Prasad
9/29/2006 12:00:00 AM

Hi,

1. Schedule a transaction log backup every 5 minutes
2. Use BCP OUT to unload the data into a text file
3. Use BCP IN or Bulk INSERT with Batch option to load the data

Ensure that the tranction log backup folder have sufficiant space.

Thanks
Hari
SQL Server MVP

[quoted text, click to view]

Hilary Cotter
9/29/2006 12:00:00 AM

Use bcp with a small batch size, like 1000. Also use the bulk insert
recovery model.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

ML
9/29/2006 12:23:02 AM

The transaction log cannot be "turned off", well, it can if you shut down the
server. ;)

Have you considered migrating data in chunks? Using DTS could also help.

The transaction log is as vital to SQL Server as the liver is to your body.

ML

---

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Bulk Load - Transaction Log Fillup