We have our databases set to SIMPLE model, but we continue to blow up during DataWarehouse loads because the log file fills. Our understanding is that SIMPLE should have no logging yet it appers to. I have 2 requests.
1) Is there a command to tell the database prior to the DataWarehouse load to not log any transactions?
2) Do we need to do more then set the DB to SIMPLE to get a general "no log" effect?
Thanks,
Todd1) No. Logging is never entirely turned off. The transaction log is simply not saved when you are in SIMPLE recovery mode.
2) Probably. The culprit is ikely to be your code. I would guess there is either:
a) A begin transaction statement somewhere way at the top of the process
or
b) the BCP/DTS load command is not batching the rows it is inserting.|||I know the culprit is the code. There is a join of massive tables. We had gone the batching route but it still was blowing up. We have ben increasing the max size of the log but were hoping we could stop going that direction.
Thanks|||If this is a complete refresh of the table (meaning you can drop it before running the insert), you can try to write the insert as SELECT INTO.|||With SIMPLE recovery, the log is not needed for backup, but SQL Server still needs it for recovery. Therefore every transaction is still logged, causing the log file to expand if there is no free space.
A section of a log file is marked for reuse as soon as all transactions are committed or rolled back and they are not needed for recovery, meaning the data has been written to disk during a checkpoint.|||Thanks for all the input
No comments:
Post a Comment