We have just migrated a database to SQL Server 2008 from SQL 2000 SP4, there is an in house application that uses DMO to bulk copy data into the load tables.
We are expereincing major performance issues when we switch the database to simple recovery model and a lot of instances in the event logs/SQL logs where the recovery model is being switched to RECMODEL_70BACKCOMP then back to Simple. This is not the case when we switch to Bulk Logged.
We have prety much concluded that the issue is to do with our app, however not being too familiar with DMO (the Dev left many moons ago) I am after some advice.
At the moment the UseBulkCopyOption Property is set to true, what would be the repercussions of setting this to false and would it potentially sole our issue.
Any pointers on how to load lots of data quickly using .NET/SMO etc. would be appreciated.
Also does anyone know if using the BULK INSERT T-SQL would have similar issues to the DMO bulk copy that we are seeing?
Cheers in advance