Database Log File Not Available Error: 9001

Jobs has been failed with error “The log for database 'AdventureWorksDW2008' is not available”

Ran DBCC CheckDB to Check logical and physical integrity of all the objects, Got below error.

Error: ‘The log for database xxxxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database.’

Since the database log is in offline state, users won’t be able to perform the below activities on the database.

      1) Backup

      2) Integrity Check

      3) Any operation which requires logging into ldf file.

Resolution:

CAUSE: From the error log, state and severity details, it was found that the database log was taken offline. Usually this happens because of a catastrophic failure that requires the database to restart.

Most of the times I see this issue is caused due to database being closed. Just check is your database has auto_close enabled using below query.

select @@SERVERNAME AS server_name,getutcdate() as report_date_utc,name as database_name, is_auto_close_on , state_desc, user_access_desc

from sys.databases

where is_auto_close_on = 1

order by name asc

If auto_close is on, switch to off (Step 7).

This fix need taking database offline and make it online to recognize the log file. It can be done by taking database into Single User Mode which kills all connections to database.

In case you don’t have a good database backup, then export everything to a temporary database for rollback purpose.

Step 1: Create a temporary database with the name DBNAME_Temp and export all the data from affected database into the new database (DBNAME_Temp) using Export wizard.

Step 2: Script out User defined stored procedures, User defined functions, Triggers, and views, and users along with their permissions.

Step 3: Run the above scripts on the new database.

Step 4: Now bring the database offline using the command

Alter database [database_name] set offline with rollback immediate;

Go

Step 5: Copy .mdf and .ldf files of the affected database to a safe location.

Step 6: Bring the database ONLINE using the command.

Alter database [database_name] set online;

Go

Step 7:  Set auto_close option to Off

Alter database [database_name] set AUTO_CLOSE OFF;

Go

Step 8: Run DBCC CHECKDB on the database.

Note: Before performing above task make sure you have maintenance window on that database.