CODEWITHSUNDEEP
sqlsql-server
Morris Moss
Morris Moss

Reputation: 3

SQL Server MERGE on a large table with small log file

I am running a MERGE statement on a large table (5M of rows) with a small log file size (2GB). I am getting an error:

Merge for MyTable failed: The transaction log for database 'MyDb' is full due to 'ACTIVE_TRANSACTION'.

Could be this solved by another action than extending the log file? I can’t really afford to extend the log file currently.

Upvotes: 0

Views: 876

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

If you have a fixed log file size, you have essentially two options:

  1. Temporarily change the recovery mode of your database from FULL to BULK-LOGGED. You'll lose the ability to do point-in-time recovery during this period, but it allows you to quickly do the operation and then go back. There are other caveats, so you need to do some research to make sure this is what you want to do.

  2. Instead of changing the transaction log, you can adopt a batching approach to commit small batches of changes at a time, thus allowing the log to flush as needed.

Upvotes: 2

Related Questions