I have a SQL Server 2008 R2.

I got a mail that the backup had failed, I opened the SQL log viewer and started browsing. And i found these two errors a bunch of times.

error 1:

Date        31-01-2013 13:20:30
Log     SQL Server (Current - 01-02-2013 05:13:00)

Source      spid20s

Message Error: 9001, Severity: 21, State: 1.

error 2:

Date        31-01-2013 13:20:30
Log     SQL Server (Current - 01-02-2013 05:13:00)

Source      spid20s

The log for database 'DB_NAME' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

After a little googling, I had read a few times that the errors indicated that the DB or log file maybe corrupt. I read that I could confirm it, by running a DBCC CHECKDB 'DB_NAME', so i did, and here are the last to lines of the output:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'DB_NAME'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The output shows that the DB isn't corrupt. But can the log file still be corrupt?

I also read that one solution might be run something called "Emergency Mode Repair" to fix the corrupt files. But some users did NOT recommend it.

Could another solution be to delete the log file completely and then create a new?.

closed as off-topic by Martin Smith, RolandoMySQLDBA, Marian, Michael - sqlbot, Erwin Brandstetter Dec 26 '13 at 16:59

If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    I think we need a little more context about how you got to this state. We see that a backup failed, but that is a symptom. What happened to the log to get to this scenario in the first place? Did someone try to do something with the files while the server wasn't running? Do you have a valid backup from before this situation occurred? And no, deleting the log file completely is not likely to work out for you... – Aaron Bertrand Feb 4 '13 at 14:46
  • I can't see any other error events in the event log. I'm a DB and IT sysadmin(trainee/student) at web-coding house, so I haven't been using the DB my self, but one of our developers have. So I don't know exactly what happend to the DB. Only that the backup stopped working for that particular DB. I don't have valid backup of my DB. – Arviddk Feb 4 '13 at 14:55
  • 1
    I think you need to get more information from the developers who have been meddling with this database. Not everything they would have done will show up in the error log. – Aaron Bertrand Feb 4 '13 at 15:07
  • You don't have a valid backup? So you only keep the most recent backup, but you don't validate they can be restored before deleting them? Sadly this is a pretty expensive lesson that most users have to learn the hard way before they realize they need a recovery plan not a backup plan. Don't bother taking backups at all if you're not going to periodically check that they can, in fact, be restored. – Aaron Bertrand Feb 4 '13 at 15:09
  • 2
    This question appears to be off-topic because it seems to have been abandoned without supplying sufficient information to know why the log isn't available. – Martin Smith Dec 25 '13 at 16:56

These symptoms sometimes occur when AutoClose in database settings is turned on. Deactivate it and try again. I had the same errors with 2008R2 Express Edition vor a Sharepoint Foundation 2010 database instance a short time ago. I investigated and found for example:

  1. http://www.sqlservercentral.com/Forums/Topic1037190-1550-1.aspx
  2. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/acfacc5e-bf1e-474d-825e-9eff927e0311/the-log-for-database-xtrans-is-not-available
  3. https://stackoverflow.com/questions/15771371/the-log-for-database-x-is-not-available

Then I changed "AutoClose" to off and the troubles had gone. Before that, I had to take the affected databases offline and online again.

The errors occured everytime a scheduled job did a DBCC CHECKDB. A manually started check of the databases did not show any error.

  • Can you please elaborate on why the AutoClose setting might be involved on the problem encountered? – KookieMonster Nov 25 '13 at 10:12
  • I added details – Andreas Nov 25 '13 at 15:05

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