machina1
asked on
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.OdbcExcep
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
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.OdbcExcep
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
...and the other thing to check is that you haven't limited the size of your trnasction log file.
ASKER
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...
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...
ASKER
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.
'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.
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.
If you decide to detach , please truncate the log files only and shrink the log file only.
ASKER
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...
So is your log file limited in size??
ASKER
no - it's set to automatically grow...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The log file (database transaction log) has an extension or .LOG by default if you want to check.