SQL Server Performance Forum – Threads Archive

SQL Server Express Error 9001

I#%92m new to the SQL world…I#%92m moving way from Access. I#%92m using SQL Server Express 2005 (9.0.2047) and have been receiving errors in my ERRORLOG file, which is as follows: Error: 9001, Severity: 21, State: 1.
The log for database ‘BetterLife’ is not available. Check the event log for related error messages. Resolve any errors and restart the database. I#%92ve been using SQL for a few months now, but in the last four weeks this issue keeps coming up. SQL will log this error in the log MANY times a second and the file will continue to grow until it utilizes my entire hard drive space. The only way I#%92ve been able to stop the error is to restart SLQ…but then later the error happens again. Any help would be greatly appreciated.

---

It sounds like your transaction log is getting corrupted. Have you checked the disk and I/O subsystem for hardware issues?
You might have to restore from a known good backup of the transaction log, then check the database and log. If the problem comes up again, I would suspect I/O hardware problems.

---

your DB is corrupted.try to restore database from the previous backup. —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

Do you have a good backup for this file, if so you can restore by dropping current database.
If not you need to perform a root of detach & attach. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Thank you for your advice…I did what you said and so far so good. If the error comes up again I#%92ll let you know. Out of curiosity, why would this fix the issue? Does it delete the log file and create a new one?

---

Well I thought the detach and attach had fixed the issue, but the error happened again today. Some people mentioned that it might be a hardware error…is there a specific tool that I can use to check for this?

---

Check the event viewer log for any information, you might involve the Vendor for such tests on the hardware. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Not certain if this is related, but I installed Windows Defender on all my machines once it went live. I noticed that in my event log that by 2:51 AM that there was an event stating that my hard drive space was critically low. So obviously the SQL error happened sometime early in the morning. In my system log I noticed that at 1:39 AM was the first entry since the prior day. The event was that Windows Defender was getting updated (it also scans the drive after the update). Has anyone heard anything about Windows Defender causing issues with SQL Server?

---

I’ve had a similar situation where the I/O subsystem was just not working correctly, and kept corrupting the databases. The hardware guys assured me that it was all working fine, but this tool from Microsoft helped me prove it was not right. The tool simulates the kind of specific I/O load that SQL server puts on a machine.<br /><br />SQLIOSim:<br /><br /><a href=’"http://support.microsoft.com/kb/231619"’ target=’_blank’ title=’"http://support.microsoft.com/kb/231619"'<a target="_blank" href=http://support.microsoft.com/kb/231619>http://support.microsoft.com/kb/231619</a></a><br /><br />I used the previous version which was called SQLIOstress, or somthing like that, and it worked great and revealed the problem. <br /><br />As for Windows Defender, I don’t what it would do that would corrupt a file, but I can tell you that it certainly brings my home computer to it’s knees when it decides to run a scan at 2:00am. [<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />!]<br /><br />-Greg

---

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by LeClair</i><br /><br />I’ve had a similar situation where the I/O subsystem was just not working correctly, and kept corrupting the databases. The hardware guys assured me that it was all working fine, but this tool from Microsoft helped me prove it was not right. The tool simulates the kind of specific I/O load that SQL server puts on a machine.<br /><br />SQLIOSim:<br /><br /><a href=’"http://support.microsoft.com/kb/231619"’ target=’_blank’ title=’"http://support.microsoft.com/kb/231619"'<a target="_blank" href=http://support.microsoft.com/kb/231619>http://support.microsoft.com/kb/231619</a></a><br /><br />I used the previous version which was called SQLIOstress, or somthing like that, and it worked great and revealed the problem. <br /><br />As for Windows Defender, I don’t what it would do that would corrupt a file, but I can tell you that it certainly brings my home computer to it’s knees when it decides to run a scan at 2:00am. [<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />!]<br /><br />-Greg<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Out of curiosity can I run the utility using the default configuration or does it need to be tweaked?

---

There are a few command line parameters for basic config, and then there is an optional configuration file where you can really tweak it. I was able to uncover issues using the default configuration, so I certainly would give that a try first. -Greg

---

I ran the default settings and it said it had no errors but 61 warnings. The warnings consisted of warnings about IO not completing with 15 seconds. So I’m assuming that this was a good sign (the warnings didn’t seem critical). Do you have a suggestion on a more thorough config or should this be enough? I’m really hopeing that now that I’ve uninstalled the Windows Defender everythings ok. ***crosses fingers***

---

Well I#%92ve tried everything I can think of to no avail…SQL will still encounter the error. If it#%92s a hardware error that causes this problem is it usually just the hard drives or is it some other hardware usually failing?

---

Hi,
i would suggest to run dbcc checkdb for any integrity error in specified database , refer relavent articlehttp://msdn2.microsoft.com/en-us/library/ms176064.aspx for same. What is a detailed description in SQL Error Log for the given error referhttp://support.microsoft.com/kb/299685 also. Regards Hemantgiri S. Goswami
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
————————
http://hemantgirisgoswami.blogspot.com

---

I would suggest to approach MS PSS in this case with all the available logs in order to assess the issue. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

I’ve got the same error 9001 too. Error: 9001, Severity: 21, State: 1.
The log for database ‘Shop_Galereya’ is not available. Check the event log for related error messages. Resolve any errors and restart the database. SELECT @@VERSION: Microsoft SQL Server 2005 – 9.00.2047.00 (X64) Apr 14 2006 01:11:53 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
I’ve tested drive with SQLIOSim and there were 2 similar errors:
User: CreateFileStream
Error:0x8007045D
Error Text:
Description: Unable to get disk cache info for D:
Function: CLogicalFile::GetDiskGeom
File: e:yukonsosbranchsql
tdbmsstorengutilsqliosimfileio.cpp
Line: 382
Computer test has got 2 ATA Drives (hardware mirroring). What’s the matter of the problem? How can I fix the error?

---

Well I thought I would give an update in case someone else has this issue. It turns out that no matter what I tried my database would constantly report the error (more often than not multiple times a day).<br /><br />As a last change effort to avoid a reload or hardware upgrade I created a whole new database and configured it EXACTLY like the one causing the error (luckily I didn#%92t have to many tables…though still more than I wanted to duplicate). Then I create a webpage that would open the error causing database (which would still work once SQL Server was restarted) and copy the data from one database to the other. I tried to simply copy/paste but it wouldn#%92t copy all the data for some reason. Then I merely deleted the old database.<br /><br />Luckily for me I use object orientated programming (I know so many people that hard code values all over their programs) and only had to change two variable to reflect the new database.<br /><br />I did all this about 9 days ago and haven#%92t had an error since…everything is working perfectly. And I made sure to make a backup of the database just in case this kind of thing happens again.<br /><br />Thanks you everyone for your help.[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br />

---

YOu can compare the database activity in between these 2 databases,I would guess sometimes due to the collation settings mismatch that might happen. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

]]>