The log for database "x" is not available

Question

I tried to use the registration page for my site after not changing anything, and suddenly this error message appeared:

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

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

I tried to reopen the program & database, tried to refresh the database, but nothing worked. What can be the problem?

score 47 · Answer 1 · answered Oct 06 '13 at 06:59

47

Take the database offline.
Bring it back online.
Run DBCC checkdb to make sure everything is good.
Set Auto Close = False.

answered Oct 06 '13 at 06:59

Eric Higgins

690
5
9

5

Thanks, taking the db offline then bringing it back online worked for me. – Simon Francesco Oct 24 '13 at 03:44
4

Great! Remember to turn off auto close, else it will happen again. – Eric Higgins Nov 04 '13 at 22:11
2

DBCC checkdb failed. Take offline, bring online worked. Thanks – Ben E G Oct 09 '14 at 07:08
4

SET auto close by ALTER DATABASE Mydb SET AUTO_CLOSE OFF – jazza1000 Apr 28 '15 at 12:04
Taking the db offline then bringing it back online worked for me. – Rolwin Crasta Jun 28 '16 at 06:49
2

In fact, SQL Server 2008 Books Online states that this setting is going away anyway, so you should have them turned off (http://msdn.microsoft.com/en-us/library/ms135094(SQL.90).aspx). – Christoph Lösch Jan 04 '17 at 10:48

Aaron Bertrand · Answer 2 · 2013-09-23T13:37:53.447

4

It is possible that the database was set to AutoClose, or was set OFFLINE, or while the SQL Server service wasn't running, an .ldf file was deleted or corrupted/compressed by other people or tools.

Make a copy of the MDF file and you can try to attach it using:

CREATE DATABASE mydb_copy ON (filename = 'C:\path to.mdf')
  FOR ATTACH_REBUILD_LOG;

Otherwise, start looking for a backup...

edited Sep 23 '13 at 13:37

answered Apr 02 '13 at 18:21

Aaron Bertrand

272,866
37
466
490

And run DBCC CHECKDB to see if the database is now corrupt (in case it was not shut down cleanly). – usr Apr 02 '13 at 19:56
FOR ATTACH_REBUILD_LOG, not FOR_ATTACH_REBUILD_LOG – ggponti Sep 23 '13 at 13:19

score 2 · Answer 3 · answered Jun 21 '13 at 11:42

Too many things can cause this. I had same problem, you can try take database offline and bring back online (or restart MSSQL). Usually it could help. If this won't help you try these solutions:

For SQL Server 2008 and 2005 try install the latest SP
Run DBCC CHECKDB ('DatabaseName') and review founded errors
Check Windows Log and SQL Server Logs for more specific Error code
Some people find solution of setting Auto Close option on database to FALSE
Reason behind this scenario could be due to poor IO or memory of your server

The log for database "x" is not available

3 Answers3