SQL Server bulk/mass inserts and the Transaction Log
Let me tell you a story about a guy, his file processing service, and the transaction log.
The file comes in, it has 65,000 entries. Guy starts file processing service on a p4 dual core with about 2gb ram. Surprisingly, 5 minutes later Guy wanders up and checks his log file to see that “file processor” is still processing!! Guy screams “WTF mate, these blimey new boxes are still slow as the afternoon sun”. A bit later, the processsing is complete and Guy gets to go home. Lucky Guy.
Guy is not happy about the wasted time at work that he could use to leave before the rest of traffic, so he Googles “slow stupid sql server big file” and stumbles across information about the Transaction Log. After much reading Guy reads that a small log size for this type of log results in slow performance. The next day Guy looks at the size of this log and notices it is very small compared to the log for the primary database.
Guy knows that lots of free hard drive space exists so he decides to try and increase it. He downloads Sql Server Express and Management Studio Express so that he can figure out how to adjust the size. At least Guy is smart enough to not “learn” how to do it on the main machine.
After Guy learns how to fix the transaction log size at home he fixes it on the machine at work and anxiously starts the file processing service. To his surprise the entire 65,000 entry daily file is completely processed in 15 seconds, instead of the 5 minutes as before.
Moral of the story: Don’t forget about the transaction log, make sure you set it up so that it can grow proportionally. A fixed size just won’t cut it.