question

@SQLShark avatar image
@SQLShark 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
ssistsqlmergelogginglogs
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the help guys. I hadn't released that there was a max size set on the log. All working fine now. Also thanks for the link I will have a read.
0 Likes 0 ·
I hadn't heard of 610 before. That's a good tip with the Data Loading Performance Guide
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.