How do I minimize transaction log growth when copying data into SqlServer?

Question

I've written a small app to periodically query, sanitize, and insert a legacy database's data into SqlServer. There are about 3M records and, if I make the polling widow pretty small, I can imagine the transaction log getting pretty big. How can I minimize its growth during these "copy" operations?

score 8 · Accepted Answer · answered Sep 08 '09 at 18:10

8

How are you inserting data?

Bulk-logged recovery model can be your friend here.

http://msdn.microsoft.com/en-us/library/aa173529(SQL.80).aspx

answered Sep 08 '09 at 18:10

Massimo

70,200
57
200
323

score 2 · Answer 2 · answered Sep 09 '09 at 00:03

2

Massimo is right. Specifically, you want to read this article - it lists the prerequisites for bulk logging to take place:

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

answered Sep 09 '09 at 00:03

Aaron Alton

1,138
11
10

score 0 · Answer 3 · answered Sep 08 '09 at 17:30

0

Depends on the backup mode of the target database.

If simple, the transaction log space should be freed and reused as transactions are committed, limiting space used.

Otherwise you will need to perform transaction log back ups.

answered Sep 08 '09 at 17:30

Richard

5,324
1
23
20

How do I minimize transaction log growth when copying data into SqlServer?

3 Answers3