Sql Server 2005 'Log for database not available'

I'm getting an error which is resulting in the sql server 2005 database being inaccessible and fatal errors from .NET -

 ERROR [HY000] [Microsoft][SQL Native Client][SQL Server]Warning: Fatal error 9001 occurred at Aug 22 2007  8:43AM. Note the error and time, and contact your system administrator.

Exception Details: System.Data.Odbc.OdbcException: ERROR [HY000] [Microsoft][SQL Native Client][SQL Server]Warning: Fatal error 9001 occurred at Aug 22 2007  8:43AM. Note the error and time, and contact your system administrator.

Further investigation in the event log reveals the same message that occurs millions of times - probably 30-40 every second -
'The Log for database 'Virtual' is not available. Check the event log for related messages. Resolve any errors and restart the database. '

I have searched the event log on the machine and have found 'FallBack certificate initialization failed with error code: 1' occurs a couple of times on the days in question, but I'm unsure if this is related.

I am unable to search the sql server logs properly and effectively, because there are so many messages - the server seems to be running out of memory and throwing an exception when filling the view. Is there another way I can export these error logs? What can cause the log to not be available and is the fallback certificate error possibly related?

thanks

Johann
LVL 1
machina1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
Has your log file run out of space? Do you have a enourmous log file that has filled up the disk?

The log file (database transaction log) has an extension or .LOG by default if you want to check.
0
nmcdermaidCommented:
...and the other thing to check is that you haven't limited the size of your trnasction log file.
0
machina1Author Commented:
The disk isn't full. There 60-70gb's free. I'll double check the size limit on the transaction log file...

One thing I didn't mention - a restart of the server solves the problem (for a while). But this has happened 3 times in the last couple of weeks - once on a dev server, and 2 in the last 2 days on the live server...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

machina1Author Commented:
Okay - I've finally managed to get one of the logs open - after splitting the 8gb file into 80 separate files - 79 of them have the same message over and over -
'The Log for database 'Virtual' is not available. Check the event log for related messages. Resolve any errors and restart the database. '

The first file, the start of which is when the db is still working,  doesn't seem to reveal anything the part immediately before the error starts repeating is as follows:

2007-07-30 12:37:35.71 spid56      Starting up database 'Virtual'.
2007-07-30 12:37:35.99 spid56      CHECKDB for database 'Virtual' finished without errors on 2007-05-17 23:00:22.380 (local time). This is an informational message only; no user action is required.
2007-07-30 12:38:04.44 spid56      Starting up database 'Virtual'.
2007-07-30 12:38:04.49 spid56      CHECKDB for database 'Virtual' finished without errors on 2007-05-17 23:00:22.380 (local time). This is an informational message only; no user action is required.
2007-07-30 12:39:54.77 spid56      Starting up database 'Virtual'.
2007-07-30 12:39:54.87 spid56      CHECKDB for database 'Virtual' finished without errors on 2007-05-17 23:00:22.380 (local time). This is an informational message only; no user action is required.
2007-07-30 12:43:05.80 spid56      Starting up database 'Virtual'.
2007-07-30 12:43:05.83 spid56      CHECKDB for database 'Virtual' finished without errors on 2007-05-17 23:00:22.380 (local time). This is an informational message only; no user action is required.
2007-07-30 12:45:00.60 spid24s     Changing the status to MERGE for full-text catalog "ftcat_documentindex" (5) in database "EOC_MSCRM" (7). This is an informational message only. No user action is required.
2007-07-30 12:56:08.56 spid56      Starting up database 'Virtual'.
2007-07-30 12:56:08.65 spid56      CHECKDB for database 'Virtual' finished without errors on 2007-05-17 23:00:22.380 (local time). This is an informational message only; no user action is required.
2007-07-30 12:57:42.00 spid59      Starting up database 'Virtual'.
2007-07-30 12:57:42.23 spid59      CHECKDB for database 'Virtual' finished without errors on 2007-05-17 23:00:22.380 (local time). This is an informational message only; no user action is required.
2007-07-30 12:57:45.17 Logon       Error: 18456, Severity: 14, State: 16.
2007-07-30 12:57:45.17 Logon       Login failed for user 'vce'. [CLIENT: 192.168.0.88]
2007-07-30 12:57:58.01 Logon       Error: 18456, Severity: 14, State: 16.
2007-07-30 12:57:58.01 Logon       Login failed for user 'vce'. [CLIENT: 192.168.0.88]
2007-07-30 12:58:00.09 Logon       Error: 18456, Severity: 14, State: 16.
2007-07-30 12:58:00.09 Logon       Login failed for user 'vce'. [CLIENT: 192.168.0.88]
2007-07-30 12:58:02.11 Logon       Error: 18456, Severity: 14, State: 16.
2007-07-30 12:58:02.11 Logon       Login failed for user 'vce'. [CLIENT: 192.168.0.88]
2007-07-30 12:58:04.47 Logon       Error: 18456, Severity: 14, State: 16.
2007-07-30 12:58:04.47 Logon       Login failed for user 'vce'. [CLIENT: 192.168.0.88]
2007-07-30 12:58:43.22 Logon       Error: 18456, Severity: 14, State: 16.
2007-07-30 12:58:43.22 Logon       Login failed for user 'vce'. [CLIENT: 192.168.0.88]
2007-07-30 13:01:48.37 Logon       Error: 18456, Severity: 14, State: 16.
2007-07-30 13:01:48.37 Logon       Login failed for user 'vce'. [CLIENT: 192.168.0.88]
2007-07-30 13:01:49.50 spid23s     Changing the status to MERGE for full-text catalog "ftcat_documentindex" (5) in database "EOC_MSCRM" (7). This is an informational message only. No user action is required.
2007-07-30 13:01:53.81 Logon       Error: 18456, Severity: 14, State: 16.
2007-07-30 13:01:53.81 Logon       Login failed for user 'vce'. [CLIENT: 192.168.0.88]
2007-07-30 13:02:43.20 spid24s     Starting up database 'Virtual'.
2007-07-30 13:03:16.94 spid24s     CHECKDB for database 'Virtual' finished without errors on 2007-05-17 23:00:22.380 (local time). This is an informational message only; no user action is required.
2007-07-30 13:03:38.53 spid21s     Error: 9001, Severity: 21, State: 1.
2007-07-30 13:03:38.53 spid21s     The log for database 'Virtual' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2007-07-30 13:03:38.53 spid21s     Error: 9001, Severity: 21, State: 1.
2007-07-30 13:03:38.53 spid21s     The log for database 'Virtual' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2007-07-30 13:03:38.53 spid21s     Error: 9001, Severity: 21, State: 1.
2007-07-30 13:03:38.53 spid21s     The log for database 'Virtual' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2007-07-30 13:03:38.53 spid21s     Error: 9001, Severity: 21, State: 1.
0
JulianvaCommented:
You can try to detach and attach the database again , but this might be risky.

If you decide to detach , please truncate the log files only   and shrink the log file only.
0
machina1Author Commented:
I've just detached and re-attached... I doubt this will have any effect, but it's worth a try... It's been running okay for about 24 hours now, since the last re-start. This was also the case last time however - it was fine for 24-48 hours then with no warning starting spitting out that message over and over...
0
nmcdermaidCommented:
So is your log file limited in size??
0
machina1Author Commented:
no - it's set to automatically grow...
0
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.