How to Fix the SQL Attach Database 9003 Error?
Summary: The error 9003 in SQL Server can occur due to corruption in the database file. There are various reasons that can lead to corruption in a database file. In this post, we will discuss different solutions to resolve the SQL error 9003. We will also mention a third-party SQL database repair tool that can help fix the error by repairing the corrupt database.
When attaching a database in SQL Server, you may get the error 9003. The complete error message looks like this:
Msg 9003, Level 20, State 1, Line 1
The LSN (1:1134:1) passed to log scan in the database Stellar is invalid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
As you can see, this error has level 20, State 1, and line 1. Let’s understand these numbers:
- Msg 9003 is the error number.
- Level 20 means itis a fatal error with a high impact because the database does not work at all.
- State 1 means it is a general error.
- Line 1 is the line of SQL code that fails.
Why does the SQL Error 9003 Occur?
This error occurs when the database is corrupt. The database can get corrupted for different reasons. The following are some common reasons:
- Problems with the anti-virus. Sometimes, the anti-virus program by mistake blocks the transaction file thinking that it is a malicious file.
- The disk is full. Sometimes, the DBA and the system administrators do not get the alerts when the disk is almost full. Then, the log file cannot grow and it produces an error.
- Due to some problems with the file system or the registry keys, the transaction log becomes accessible.
- The hard disk is failing due to problems with the electric motor, extreme heat, power failures, etc.
How to Fix the Error 9003 in SQL?
As the error is related to corruption in the database, you can follow the ways mentioned below to fix the problem.
Restore the Database
If you have a backup, then the easiest way to solve the problem is to restore the database from the most recent backup.
The following T-SQL code shows how to back up a database, named stellar, to the c:\backup\stellar.bak device:
BACKUP DATABASE stellar TO DISK = ‘c:\backup\stellar.bak’
The following T-SQL code shows how to restore the database (stellar) from the c:\backup\stellar.bak device:
RESTORE DATABASE stellar FROM DISK = ‘c:\backup\stellar.bak’
If you’re not comfortable with the T-SQL commands, you can use the SQL Server Management Studio (SSMS). Here’s how:
- Open SSMS.
- In the Object Explorer, right-click the Databases node and select the Restore Database option.
- Select the Device option, press the browse button, and select the path where the backup is located.
- Finally, press OK.
Use the DBCC CHECKDB Command
If you do not have a current backup, you can use the DBCC CHECKDB command to repair the database. You need to set the database in Emergency mode first.
ALTER DATABASE stellar SET EMERGENCY;
Next, you need to set the database in single-user mode.
ALTER DATABASE stellar SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Now, you need to repair the database.
DBCC CHECKDB (stellar, REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;
After that, change the database from single-user mode to multi-user mode using the ALTER DATABASE command.
ALTER DATABASE stellar SET MULTI_USER;
What happens if the backup is corrupt or does not contain some recent and crucial information? Also, what happens if the DBCC CHECKDB command fails to fix the issue?
In such cases, you can rely on a third-party SQL database repair software like Stellar Repair for MS SQL. This software can fix damaged or corrupted databases, thus fixing error 9003.
This software is easy-to-use. It requires you to have the database offline. Otherwise, it will send a message saying that the database is in use. You can set the database OFFLINE by right-clicking the database in the Object Explorer in SSMS and selecting the Task > Take Offline option.
Once offline, copy the database file and work with the copy.
Open the Stellar Repair for MS SQL software and use the Find button to find the data file (for example, the stellar.mdf) if you do not know where it is stored.
Alternatively, browse and select the data file. After selecting the file, press the Repair button to repair the database. You can also select the Include Deleted Records option, if necessary.
After repairing, press the Save icon to save the data. You can save it in a New Database, current Live Database, or in other formats, like CSV, Excel, and HTML.
Above, we have discussed the SQL Server error 9003 and the reasons that may lead to this error. We have also mentioned some possible solutions to resolve this SQL error. You can restore the database from the backup using the RESTORE command or use the DBCC CHECKDB command to repair the database. If none of the solutions works, you can use Stellar Repair for MS SQL to repair the corrupt database, thus resolving the SQL error 9003.