SQL Server Error : 9001, Severity: 10. The log for database ‘%.*ls’ is not avai

SQL Server Error : 9001 Details

SQL Server Error: 9001
Severity: 10
Event Logged or not: Yes
Description:
The log for database ‘%.*ls’ is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Severity 10 Description:
Informational messages that return status information or report errors that are not severe. For compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application.

sql server error 9001 severity 21 state 1

Event ID: 9001

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

SQL backup error 9001 occurs when SQL server fails to open the database for a time period in which the backup can be taken successfully. When no activity is registered on the database, the database gets automatically closed in case the AutoClose property on database is turned on. Therefore, abrupt closing of the database in middle of backup process can lead to this error.

A corrupt database or log file can lead to this error. Running DBCC CHECKDB‘dbname’ on the database which will give more information. The DBCC database check will reveal if the database is corrupted or not.

SQL log file takes up a lot of storage space than the actual database sometimes. This can also result in SQL Error 9001.

Hardware issues in the server like corrupt disk may also cause this error.

fatal error 9001 in sql server occurs with corrupt db. .

Reading sql server error log location from SQL Query

Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.

SQL server error number 9001 is indication of corruption and it is  a fatal error that is sql server fatal error 9001 occurred which is recorded in sql error logs.

USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go

The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1:- String one you want to search for
4. Search string 2:- String two you want to search for to further refine the results
5. start time for Search
6. end time for search
7. Sort order for search results:- N’asc’ = ascending, N’desc’ = descending

By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.

For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html

Fatal error 9001 sql server is a warning fatal error 9001 occurred which is based on corrupted database

Solution for Resolving the Error

Solutions the SQL Error 9001 are:

Now before we get to solutions, check the database integrity of the database. If there are no errors generated by this command, then  move on to changing the auto_close option.

1. DBCC CHECKDB – Running DBCC CHECKDB will give the reason to be a storage issue in case the true reason behind the error is that.

dbcc checkdb('database_name')

2. Turn Off AutoClose – If Auto Close is set to on on database properties, turn it Off. This will prevent the database from closing in the absence of activity during backup process.

alter database [database_name] set AUTO_CLOSE OFF;

3. Set SQL Server Database Offline and Online back – Offline and Online the database on SQL server

alter database [database_name] set offline with rollback immediate;
and
alter database [database_name] set online;

4. Emergency Mode Repair – Running Emergency Mode Repair can repair the SQL database or log file which  has got corrupted. This repair mode will help in repairing the log file and completion of the backup process.

But this emergency mode repair process is usually not recommended as it may end up in deleting some parts of the log file. It is a last resort to fight the SQL Error 9001.

Alternate Solutions

1. Restarting SQL Server Service(non production instances only)

• To Restart, Start or Stop the SQL Server instance by right click on sql server instance in SSMS or in SQL. You may need to open SSMS as administrator to start, stop the instance.

• Other ways for restarting SQL server Service

1. From SQL Configuration manager from Start menu
2. From Services in Windows server
3. From Cmd using net start and net stop

2.

SQL Server Error Code and solution summary

SQL Server Error: 9001
Severity: 10
Event Logged or not: Yes
Description:
The log for database ‘%.*ls’ is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Solutions would be to

1. Run DBCC CHECKDB

2. Turn Off AutoClose

3. Set SQL Server Database Offline and Online back

4. Emergency Mode Repair as last resort