The log for database ‘name_here’ is not available.
Original Post by Håkan Winther
SQL BLOG Here is the story: you wake up one morning, and as you read your email, you come across a SQL Server notification about ”SQL Server Alert System: ‘Severity 021’ occurred on Server1”. Sounds bad. How bad is it? Do you panic? Not yet.
Step 1: understand what the error means. After a short consultation with the web you realize that the error is related to the corrupt or missing log file of the database, or a malfunctioning hard drive.
Step 2. Is there a backup? NO?! Since the database log is not readable / accessible, it only makes sense that there is no backup, and since you delete the backups older than 2 days, the last working backup of the database is gone. Do you panic now? A little. Step 3. Check the SQL Server error log for additional messages. All you find is ”Error: 9001, Severity: 21, State: 1.” and ”The log for database ‘name_here’ is not available. Check the event log for related error messages. Resolve any errors and restart the database.” Cryptic, huh.
Step 4. You try to backup the database. No go, it is expected.
Step 5. You try to detach the database. You know that SQL Server re-creates the log file if you try to attach an existing database and if you do not point to a specific log file. Good plan, but the detaching of the database does not work. The database is flagged as ”not ready” and you cannot detach it. NOW do you panic? No, because you have a lot more tricks up your sleeve.
Step 6. Query a table in the database. Ha! It works. The database is ”not ready”, but it is not ”Suspect” yet. So, you can actually get the data out…
Step 7. Right-click on the database, go to ‘Tasks’ and then to ‘Copy Database’. The wizard starts, you follow it, and through it you create a SSIS package which helps you create a new database where all the data from the ‘not ready’ database is extracted. Step 8. Drop the ‘not ready’ database, rename the newly created one, move the files, sprinkle with sugar, bake for an hour and serve warm… 🙂 (yes, the last one was a ”spur-of-the-moment-pure-daydreaming” 🙂 ) So, here is how you go around to fix the Error 9001, Severity: 21. Note that there is much more to do: check why the error occurred in the first place, check the hard drive integrity and so on.
And remember: there is no single ultimate solution for a problem. Bottom line: If you would like to have automated email notifications from your SQL Server(s), or if you would like to have proper backups setup, or if you would like to get help with solving any SQL Server related errors – then you should contact us. We are here to help.