Sql server ошибка 9004

Introduction

Transaction logs in SQL database are the vital component consisting of all the changes a user makes in database. Moreover, user can undo the changes that he made in a file of the database using the information contained in the
transaction logs; this operation is also a transaction. It can be understood by the following:

You have made some changes in the data file of a database, say DB1, it means that a transaction is taking place. The log begins keeping record by the time you start making changes till you perform commit of that transaction. Note that a database consists
of minimum one data file and physical transaction log.

Now imagine that there is damage to transaction log, it will directly impact the database. If an operation is performed in SQL that requires processing or reading the transaction log, an error may occur similar to the following:

Error: 9004
An error occurred while processing the log for database.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Root Cause of the Error

The error occurs in case of damage to contents of transaction log. The severity of the error is same as that of a database corruption. Therefore, for in-depth analysis of cause, the similar techniques should be applied that are required for database corruption.

What to do

Try out the following to work around this problem:

Restore from Backup

It is recommended to bring the backup into use. It might be the case that the backup of transaction log or its portion has created corruption in the contents of transaction log. In this case, Error 9004 may occur while restoring. It indicates that there
is damage to transaction log placed in the backup.

Rebuild the Transaction Log

If it is not possible to restore from the backup, you can rebuild the transaction log. However, you need to be aware of all positives and negatives of doing it so that you can avoid possible transactional consistency loss in the database. Furthermore, it
includes
DBCC CHECKDB command to execute.

However, you might not be able to carry out the task successfully or even if you do, it might not give the expected results. If so, you can use commercial SQL recovery application. Such applications are the programs built by software experts. More of it,
such software will perform recovery, which has been failed by DBCC CHECKDB command, it will also recover the database from suspect mode. You can also recover the database in case of severe damages.

In SQL Server, the database contains the data file with information of the tables, views, stored procedure, etc. and the transaction log files. The transaction log files store all the transactions and modifications. For example, if you update, insert, or delete data in the tables or views, these changes are stored in the transaction logs.

The SQL Server error 9004 is related to a problem with the transaction logs. The error indicates that the transaction log is corrupt. It can be a problem with the log file header or the Virtual Log File (VLF), which can be damaged. The corruption may occur due to unexpected server or service shutdown, hardware failure, virus or malware attacks, etc.

Let’s discuss how to resolve the SQL Server error 9004.

Solutions to Fix the SQL Server Error 9004

Here are some solutions you can try to resolve this error and recover the database.

Restore the Database using Backup

If you have a backup of your database, the best option is restore your database from the last backup.

For this, open SQL Server Management Studio (SSMS) and go to the Object Explorer pane. Right-click the database to backup and select Tasks > Back Up.

• In the Backup Database window, select the Full Backup type, the destination path, and then press OK.

• If you need to restore the data, in the Object Explorer, right-click the database and select the Restore Database option.

• In the Restore Database window, select the database to restore and select the backup sets to restore, and press OK.

Note: It is strongly recommended to press the Verify Backup Media button before restoring.

Use DBCC CHECKDB Command to Repair the Database

You use the DBCC CHECKDB command to repair the corrupt database.

Here’s the syntax:

USE master;
ALTER DATABASE Adventurewords2019 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (Adventurewords2019, REPAIR_FAST) WITH NO_INFOMSGS;
GO
ALTER DATABASE Adventurewords2019 SET MULTI_USER;
GO

You need to first set the database in single-user mode. This mode will log out other users of the database. Then, run the CHECKDB command to repair the database.

After repairing the database, you can set the database to multi-user mode to allow other users to access the restored database again.

If the REPAIR_FAST command fails, in the DBCC CHECKDB command, you can try the following options:

• REPAIR_REBUILD: It will try to rebuild the database.
• REPAIR_ALLOW_DATA_LOSS: It repairs the database but you may lose some data.

Use CREATE DATABASE Command with FOR ATTACH_REBUILD_LOG

There is an option in SQL Server to create the database and rebuild the transaction log file. The syntax to rebuild the database is:

USE [master]
GO
CREATE DATABASE [AdventureWorks2019] ON
( FILENAME = N’c:dataadventureworks2019.mdf’ )
FOR ATTACH_REBUILD_LOG
GO

First, you need to go to the master database. Then, create the database and rebuild the corrupt log.

Use a Third-Party SQL Repair Software

You can use a third-party SQL repair software, such as Stellar Repair for MS SQL. This software can repair corrupt databases without any data loss.

The software is simple to use. You need to first find the data file.

Note: Make sure that your database is offline before using the software.

To find your data file, open Stellar Repair for MS SQL and press the Find button.

• To find your data file, you can open the Stellar Repair for MS SQL and press the Find button.

• Select the database file and press the Repair button to start the repair process.

Once the repair process is finished, you can export the repaired data to a SQL Server database or various other formats, such as Excel, CSV, HTML, etc.

Conclusion

The SQL Server 9004 error usually occurs due to corruption in transaction logs or database. In this article, we learned how to fix this error. You can restore the database from the backup or use the DBCC CHECKDB commands to repair and recover the database. However, for quick and easy recovery of the database, use a specialized SQL repair tool, such as Stellar Repair for MS SQL.

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

1. From SQL Configuration manager from Start menu
2. From Services in Windows server
3. 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

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.

1. Open task manager to check CPU, Memory usage etc.
2. 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.