Danny Sheridan (4/19/2010)
I had a similar issue in our production environment where our transaction log was growing to unacceptable sizes after bulk data loads. In my case, the transaction log was not needed so I would manually truncate it after the inserts to get it back down to an acceptable size. I added this query at the end of my stored procedure to perform this:
The size of the file after the load is of no consequence. The fact that it grew to 9G in the first place means that I need to have 9G (or more) available on a drive where space is at a premium.
"CHECKPOINT" should be enough, in simple recovery mode, to force a checkpoint. I've actually added this to several points in my code while troubleshooting. This is how I know that a single INSERT statement is causing the problem.