March 5, 2012 at 12:09 pm
Outline:
Destination table is a flat table for reporting.
A job runs over the weekend inserting to the destination table selecting from our reporting database.
Uses select - into
Errors out with:
Executed as user: ''. The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002) The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed.
Thanks,
DK
March 5, 2012 at 12:39 pm
detail your doubt!!!! 😀
March 5, 2012 at 12:58 pm
I'd export the data using bcp and import it into the target db using bcp again.
Make sure the target db is set to bulk-logged recovery or Simple recovery.
The current approach will be fully logged during the insert to ensure transactional consistency (regardless of the recovery model of the target db) hence killing the transaction log.
There are several aternatives (I, personally qualify as second best options):
a) insert the data in batches (with the risk of incomplete data at the target)
b) increase the useable size for tempdb and the log to allow to finish
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply