Table of Contents
SQL Server Error : 9004 Details
SQL Server Error: 9004
Severity: 21
Event Logged or not: Yes
Description:
An error occurred while processing the log for database ‘%.*ls’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Severity 21 Description:
Indicates that a problem has been encountered that affects all tasks in the current database, but it is unlikely that the database itself has been damaged.
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.
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
What causes the error?
1. SQL server failure to open the database for a period of time during which a backup may be successd is the primary cause of SQL 9001 backup. In the case of the AutoClose on the database, when there’s no registered activity, the database will be closed automatically. This can lead to abrupt data closure of the mid-backup process.
2. This error can be caused by a corrupted database or log file. If a shared server is used, there is a very high likelihood of log file corruption. DBCC CHECKDB’db name’on the database can confirm this. The check for the database reveals whether or not the database is damaged.
3. SQL log file sometimes takes up much storage than the database itself. SQL Error 9001 may result.
4. Server hardware problems also play a part in this error.
Solution for Resolving the Error
Some of the manual methods for removing the SQL error 9001, which prove useful are:
(1) AutoClose to be Turn-Off
Turn it off if Auto Close is on. In the backup process the interruption was done by the database auto close is therefore prevented from closing.
(2) SQL Server Unmount
SQL server restart. The removal of the server where the log file resides also proves useful to remove the error.
(3) DBCC Checkdb.
If this is a storage problem, run DBCC CHECKDB. In case the true reason behind the error is that, the command will generate a storage problem.
(4) Repair of emergency mode
If the SQL database or log file has been corrupted, execute Emergency Mode Repair. This helps to repair the log file and the backup process is completed. However, this process is not usually recommended since some parts of the log file might be deleted. The last resort to combat the SQL Error 9001 this should be opted.
SQL backup Error 9001 can be removed from the SQL database with the application of the above solutions.
More than 95-99 percent of the corruption issues with the database are the storage system. The other half are bugs in the SQL server and half of the remaining problems are because of bad memory.
Odds is that it’s an issue with storage.
If it happens again, run DBCC CHECKDB in the database, giving you more information about corruption, and fixing the problem without restoring it. In emergency mode, you will probably need to connect the database to checkdb from the database.
The problem is not related to memory use of high percent. SQL Server is designed to run the memory up to 100% (or nearby).
Alternate Solutions 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
- From SQL Configuration manager from Start menu
- From Services in Windows server
- From Cmd using net start and net stop
2.Checking SQL Performance metrics like CPU, Memory
Check SQL Server CPU, Memory usage, longest running queries, deadlocks etc.. using activity monitor or sp_who2.
To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission.
2 Different Ways to Open up Activity Monitor in SQL Server 2008 are mentioned below:
Open up Activity Monitor Using Object Explorer
In Object Explorer, right click the SQL Server 2008 Instance and click on Activity Monitor.
Also can be opened from SQL Server 2008 Management Studio’s toolbar, by clicking Activity Monitor
SSMS Activity Monitor by Method2
It shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second.
For information on SQL Server Activity monitor go to https://sqlserver-dba.co.uk/sql-server-administration-basics/activity-monitor
Or using SQL Query analyzer window to run sp_who2 command which is less resource intensive and gives same information as activity monitor.
2.Checking Windows Performance metrics like CPU, Memory, Disk Space etc.
- Open task manager to check CPU, Memory usage etc.
- Open file explorer to check Disk space on each drive.
SQL Server Error Code and solution summary
SQL Server Error: 9004
Severity: 21
Event Logged or not: Yes
Description:
An error occurred while processing the log for database ‘%.*ls’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.