asked
Minimal logging using TSQL MERGE?
Hello, I'm trying to load a dimension table in SSIS on SS2012. It is a type 2 SCD and I am using a method of loading using a MERGE embedding in an INSERT to update and insert. I am trying to load >400,000 rows and hitting an issue. It is failing due to the transaction log: "The transaction log for database 'db_name' is full due to 'ACTIVE_TRANSACTION'. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Is there a way to perform such a statement with minimal logging? I am currently using the simple recovery model. Thanks Terry
So as the error message says, you have full transaction log and it cannot be expanded due to probably fixed size. Also you have mentioned you are implementing SCD2. If there are updates, updates are always fully logged and cannot be bulk logged. What more if you have clustered index on the table, than the inserts are also always fully logged. In some scenario a bulk insert into the can be achieved using Trace Flag 610. (see The [Data Loading Performance Guide][1]). In the Data Loading Performance Guide you can also find, that MERGE supports bulk logged inserts for heap and for clustered indexes only with the Trace Flag 610 enabled. So for inserts you will be able to achieve bulk inserts, but for updates not. Anyway I suggest you extend your log file to support the operation. Also the T610 should be used with caution as mentioned in the guide. For some systems it can improve performance significantly but in same cases it can even degrade the performance. [1]:
http://technet.microsoft.com/en-us/library/dd425070(v=SQL.100).aspx
16 People are following this question.
