Backup error Auto Close

Backup failed for Server

"The log for database 'MYTestDB' is not available. Check the event log for related error messages. Resolve any errors and restart the database. (Microsoft SQL Server, Error: 9001)


Possibilities:
1. Someone deleted the log file
2. Disk holding the .ldf file went bad
3. Other hardware (disk) malfunction


Do this:
A. Backup all databases
B. Hard boot server
C. If problem occurs again, have HW diagnostics performed.


Sol: In mycase after re-starting the SQL Server Instance, i was able to take database backup.


--Auto Close option off
"When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.
 When set to OFF, the database remains open after the last user exits."



What is AutoClose and why is it Bad?
AutoClose is a database option or setting – set on a database by database basis (meaning that it can’t be controlled at the server level).

According to Books Online:

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

When set to OFF, the database remains open after the last user exits.


--To check the auto close on or off.
select name,is_auto_close_on from master.sys.databases where is_auto_close_on=1;

--To change auto close option
Alter database DB_Name set auto_close off
Go




2. Open SSMS and run the following command new query window.
   Alter database DB_Name set offline with rollback immediate
   go
 
3. Open new query window and run the following script.
   Alter database DB_Name set online
   go
 
4. Once the databases are online run checkdb.

5. Run the following query to set the Auto close off.

   Alter database DB_Name set Auto_close off
   go
6. close ssms and log off from the server