I'm trying to do a large INSERT operation from a database to another. But I get an error message:
Msg 9002, Level 17, State 4, Line 1
The transaction log for database '_ARCHIVE_mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Things I've done so far:
- Checked that the disk has enough disk space
- checked that the transaction log file size is small (1024 KB)
- checked that the log_reuse_wait_desc was NOTHING
- checked that the recovery mode for _ARCHIVE_mydatabase is Simple
- restarted the server
I also tried to run the query from this SF answer:(
The Transactional Log is Full)
but it doesn't help.
Also we have many _ARCHIVE databases and all of them have the same error!
What else can I try and why is this happening?
Best Answer
Not to ask the obvious question, but have you checked to make sure that a log file size limit is not set? Or the option to auto-grow is not disabled..