Proper Database Model for large volume of inserts

Question

Post reply

Proper Database Model for large volume of inserts

Jpotucek

SSCoach

Points: 18583
More actions
November 5, 2009 at 7:52 am

#217151

Our App Dev group start a series of large insert jobs that are inserting millions of rows into one particular database from flat files. been running for days now...
I left the database in Full recovery mode and sized the Transaction log so that it would not have to continually grow as the inserts were happening. (TLOG is set to grow in 10% increments)
I'm just wondering how others would have handled this ? Should I have put the DB in SIMPLE recovery mode for the duration of the intial load (for performance sake) ???
We will be restoring the DB and running the initial again next week (in dev) before doing the final initial load into production sometime next month.. I'd just like to make sure that I'm not slowing down the process.. any suggestions?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jpotucek SSCoach Points: 18583 More actions · Answer 1

any suggestions on this? Full recovery with frequent TLOG backups? Bulk logged recovery mode? simple?

NickDBA Hall of Fame Points: 3792 More actions · Answer 2

Your best bet is to have the DB in FULL mode for normal operations, then switch to BULK-LOGGED when you need to do the import. Then switch back to FULL mode when the import has completed. Thats assuming you need to be in FULL mode for normal day to day activity requiring point in time recovery

Jpotucek SSCoach Points: 18583 More actions · Answer 3

Thank you! yes, for day to day operations I need to be able to do point in time recovery so I am in Full recovery mode. If I change to Bulk-logged for the duration of the import, should I also do TLOG backups during the the import? NOT for recovery purposes but to checkpoint the log?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I would not set the t-log to grow by a percentage amount, but by a fixed amount should it still have to grow. By using a percentage, each time it required to grow, it will grow by a larger amount.

NickDBA Hall of Fame Points: 3792 More actions · Answer 5

Do your backup before, not during.

Also, simply placing the DB in BULK-LOGGED mode won't guarantee minimal logging. There are some cases where full logging will still occur, even when the database is placed in BULK-LOGGED mode. For more info check this out

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