20

Over the weekend a website I run stopped functioning, recording the following error in the Event Viewer each time a request is made to the website:

Event ID: 9001

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

The website is hosted on a dedicated server, so I am able to RDP into the server and poke around. The LDF file for the database exists in the C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA folder, but attempting to do any work with the database from Management Studio results in a dialog box reporting the same error - 9001: The log for database is not available...

This is the first time I've received this error, and I've been hosting this site (and others) on this dedicated web server for over two years now.

It is my understanding that this error indicates a corrupt log file. I was able to get the website back online by Detaching the database and then restoring a backup from a couple days ago, but my concern is that this error is indicative of a more sinister problem, namely a hard drive failure.

I emailed support at the web hosting company and this was their reply:

There doesn't appear to be any other indications of the cause in the Event Log, so it's possible that the log was corrupted. Currently the memory's resources is at 87%, which also may have an impact but is unlikely.

Can the log just "become corrupted?"

My question: What are the next steps I should take to diagnose this problem? How can I determine if this is, indeed, a hardware problem? And if it is, are there any options beyond replacing the disk?

Thanks

Community
  • 1
Scott Mitchell
  • 473
  • 2
  • 6
  • 13

11 Answers11

16

Well over 99% of database corruption problems are to do the storage system. Half of the remaining problems are due to bad memory, with the other half being bugs in SQL Server.

Odds are it is a storage problem.

If it happens again run DBCC CHECKDB against the database and this will give you more information about the corruption, and if the problem can be fixed without doing a restore. You will probably need to bring the database online in emergency mode to run checkdb against the database.

Memory usage being at 87% has nothing to do with the problem. SQL Server will run the memory all the way to 100% (or close to it) by design.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • Thanks for the suggestions. I actually tried doing DBCC CHECKDB, but got a lot of errors, including an error saying it couldn't find the log file. But I didn't try bringing the DB online in emergency mode. – Scott Mitchell Feb 22 '11 at 00:00
  • Usually if the transaction log is corrupt it's a pretty bad thing. CHECKDB might be able to repair it, or it might not, depending on how bad the corruption is. If you have transaction log backups (your provider may not allow these) then you could have lost almost no data. At the end of the checkdb output will be the repair level which is required to correct the problems with the database files. – mrdenny Feb 22 '11 at 00:04
  • Correct. Memory Usage won't have anything to do with this - unless the memory was corrupt and just transferred on down to the disk. Either way, you should be seeing some other indications of IO problems in your event logs. Somewhere. – Michael K Campbell Feb 22 '11 at 00:42
  • You can try running a checkdisk (chkdsk) against the disk to see if Windows sees any problems with the disk. Odds are you'll need to replace the disk. However it could have just been a bug within the disk controller's code or the code within the disk's BIOS. In either case I'd look into replacing the disks, and/or the controller. – mrdenny Feb 22 '11 at 08:29
8

I was able to solve this by taking the database offline in Management Studio then immediately bringing it back online. dbcc checkdb had thrown errors which were resolved after doing this. I can't say why this worked only that it did work.

Factor Mystic
  • 463
  • 1
  • 10
  • 15
5

I have had this problem recently too and after mountains of research it appears to be common when a database is set to AUTO CLOSE. I set all the databases to AUTO CLOSE = FALSE. This started with one database then went over to two and the next it was on all of them. I simply restarted the SQL Server Instance Service instead of restoring databases. Another way to fix the symptom is to take the problematic database offline and bring it back online again.

Clarice Bouwer
  • 151
  • 1
  • 3
1

MS SQL will take the logs of an affected database offline to avoid database corruption. That is why you get the 9001 error.

When you take the affected database offline/online MS SQL will enable the affected database logs until the error reoccur.

Another way to solve this is to change the Auto_Close option to OFF

http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases

Saul A. Greco V.
  • 11
  • 3
0

I also encountered the same error "the log for database 'TempDB' is not available. Fatal error 9001. Please contact your administrator"-

I simply restarted all the SQL services and the issue got solved :)

Scalvo
  • 1
0

I've seen this just now with SQL Azure when switching eDTU level. As it was coming back online I got the 9001 error. I don't think it suggested any data corruption, just the side effect of killing a connection and a reminder to do it during off peak times - or revisit your retry logic.

Note it was also accompanied by a 3314 error.

System.Data.SqlClient.SqlException (0x80131904): The service has encountered an error processing your request. Please try again. Error code 9001.
The service has encountered an error processing your request. Please try again. Error code 9001.
The service has encountered an error processing your request. Please try again. Error code 3314.
Simon
  • 1,301
  • 2
  • 15
  • 19
0

I'm going to guess/hope that you've got a raid going for the disk for your sql server. if you suspect hardware issues, the very first thing i would do is run your raid maintenance/diagnostic tools.

the second thing (probably concurrently if you can) is run dbcc checkdb on the database (mayhaps your system databases as well).

Thirster42
  • 354
  • 1
  • 2
  • 14
0

Ok, first step, make a backup of your log and your mdf files to a completely different drive. QUICKLY! (file copy)

Also, try an perform a full database backup.

Next, try the following. Using your current database, detach it, if you can and then delete the log file, or move it to a completely different location on disk. Then re-attach the database, and it will show up in the gui with a log file, click the remove (or delete) for the log file so that it does not show up, and then click ok. Basically attaching it without a log, will force it to create a log file for the database in the default location.

Let me know.

Ryk
  • 101
  • 2
0

Yes, I too got this same issue, it was regarding the tempDb error 9001 i.e log not available. We restarted the services and all was fine.

The issue behind this was SAN or storage issue, while I/O write operation, it was unable to write for more than 15 secs.

Bunny
  • 1
0

Yesterday, I had received the same error "the log for database '%' is not available. Fatal error 9001 , msg 21. Please contact your administrator"-

Workaround- I checked the 'TempDB' but it was not accessible similarly rest of the system databases. Then before going for repair option I simply restarted the SQL services for that instance and issue got resolved :) :)

Poonam Choudhary
  • 1
-2

I have seen this happen when there is no disk space available for log expansion; can you verify there was ample space on the C:\, and that your logs are being managed, i.e. getting backed up if you are in full recovery mode.

I would move your ldf's (and mdf's) off the boot volume if you have the option.

SqlACID
  • 2,166
  • 18
  • 18
  • Running out of hard drive space will NEVER cause database corruption, unless you are using thin provisioned storage and the base storage runs out of space. But that's a whole different nightmare. – mrdenny Feb 22 '11 at 00:06
  • I'll rephrase..maybe not corruption of the database, but certainly a cause for log files being unavailable as the op stated. – SqlACID Feb 22 '11 at 03:13
  • 1
    There is more than 25 GB of free space on the drive, and the database in question is under 25 MB in size. – Scott Mitchell Feb 22 '11 at 06:44
  • The only error that you'll ever see from running out of space is a file full error when attempting to modify rows within the database as the transaction can't be written to the log (not what the OP stated). Running out of space wouldn't cause the database to become unavailable (what the OP stated). – mrdenny Feb 22 '11 at 08:27
  • Disagree. Ran out of space on the drive where the log file was and then I started seeing exactly the same issue in question. – ADNow Dec 09 '13 at 14:38