Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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

share|improve this answer
    
Yes, I've checked those also, thanks. –  kor_ Mar 14 '12 at 7:03

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

share|improve this answer
    
Shouldn't it be small after a log backup and database shrink? –  kor_ Mar 14 '12 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 '12 at 18:23
up vote 0 down vote accepted

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.