0

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?

3 Answers 3

0

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..

0
0

The transaction log file shouldn't be small. It's "scratch space" for SQL transactions, and essential for SQL Server to function. Set it to something reasonable like 512MB or 1GB, then try the operation again.

On a side note, please don't run a production database on "Simple" recovery model. Full transaction logging helps SQL Server maintain internal transactional consistency in the event of the unexpected. You should observe the log file's growth, manually grow it to a reasonable size, then take regular backups of the transaction log (mine are set to hourly).

2
  • Shouldn't it be small after a log backup and database shrink?
    – kor_
    Mar 14, 2012 at 9:13
  • I tried to set the initial size to 512MB. I got an error "A severe error occurred on the current command. The results, if any, should be discarded. (.Net SqlClient Data Provider)". So what now?
    – kor_
    Mar 14, 2012 at 18:23
0

The problem was that our virtual server's hard drive had been corrupted. When I tried to run chkdsk I got an error "The type of the file system is RAW" (which means unformatted). Our IT support fixed the drive and now everything works fine!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .