Summary: SQL database error 3456 can turn your database inaccessible. This blog discusses what causes SQL database error 3456 and how it can be fixed. You can try the manual solutions to fix the 3456 error or use specialized SQL recovery software to repair and recover the database.
You are a SQL Server Admin, and it is understandable that in your career you may have experienced the SQL database error 3456. This error usually turns your database to a suspect state. A suspect state in a database means that the database has some corruption errors and cannot be online.
You can check the error message using this query in multiple languages:
SELECT * FROM master.dbo.sysmessages where error =3456
The messages in different languages are the following:
The error message 3456 is the following:
|
spid41s Error: 3456, Severity: 21, State: 1. spid41s Could not redo log record (#), for transaction ID (#), on page (#), database ?mydb? (5). Page: LSN = (#), allocation unit = #, type = #. Log: OpCode = #, context #, PrevPageLSN: (#). Restore from a backup of the database or repair the database. |
|---|
In the above error message, Severity 21 means that there is a system error or fatal error. The connection to the database may fail in this state. This error message indicates that error is related to LSN (Allocation error) where LSN is the log sequence number.
Causes of SQL Database error 3456
This error message occurs when it is not possible to redo the transaction log or when we try to recover a database. During this process, the transaction log may have problems. If you have a backup of the database, you can try to restore it and verify if it works. Another alternative would be to try to repair the database. You could try for example to repair using the DBCC CHECKDB:
DBCC CHECKDB (DB_NAME, REPAIR_REBUILD);
For more information, try to check the event viewer. To do this, press the search icon and write event:
In the event viewer, go to Windows Logs>Application and look for the Events with the source MSSQL tips. You may find more tips related to the problem of the database:
How can we solve this error?
There are following ways to fix SQL database error 3456, and these are:
- Using SQL Server Management Studio (SSMS)
- T-SQL Command
- SQL database repair software
Let’s discuss these options one by one.
Solution 1: Using SSMS
- If you have a backup, you can run a simple restore statement. You can use the SSMS.In Databases, right click and select Restore Database:
- In Restore Database, press the browse button in the device section to select a device:
- In the Select Backup devices, select your device. If you do not have a device, press the Add button:
- Select the backup file and press OK:
- In this example, a tail-log backup will be created before restoring. A warning message will be displayed:
Solution 2: Using T-SQL
If you prefer to use T-SQL, the sentences required will be similar to the following:
USE [master]
BACKUP LOG [db1] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\db1_LogBackup_2018-02-13_05-17-11.bak’ WITH NOFORMAT, NOINIT, NAME = N’db1_LogBackup_2018-02-13_05-17-11′, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [db1] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\db1.bak’ WITH FILE = 2, NOUNLOAD, STATS = 5
GO
We will restore the master database first, and if it is necessary then, we will run a tail log backup. After this, we will restore the database.
If your back up is not updated or it is corrupted then you can try the following solution:
ALTER DATABASE mydb SET ONLINE;
It will try to bring your database online again. You can verify if your database is online by using the following query:
select name,state_desc from sys.databases
Also, you can verify the SQL Error Log:
It is also possible to rebuild the database. Use the SQL Server installer and in the command prompt run the Setup using the REBUILDDATABASE. You will also need to specify a login and a password:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MyInstance /SQLSYSADMINACCOUNTS=sa /SAPWD= $tro0ngpassword
After rebuilding the databases, you may need to restore them again from the last backups available.
Solution 3: Using Stellar Repair for MS SQL
The alternative solution is to use Stellar Repair for MS SQL. This software is used to repair damaged SQL database and if your database is in suspect status, this software may help to fix the problem. You can download the software by Free Download button:
- This software requires stopping the SQL Server Service. Once stopped, you can open the Stellar Repair for MS SQL.
- You will need to select the MDF file. The MDF file is the main data file that contains the database information. Once selected the MDF file, press Repair:
- Once repaired, you will receive a success message:
- You can also check the preview of repairable database objects:
Conclusion
In this article, we have learnt about the SQL Server error 3456. This error can have the SQL Server Database in the suspect state. We also learned different solutions to solve this problem. One of the solutions was to restore the database. The other solution was to alter the database and set online.
If none of these solutions works, it is possible to use Stellar Repair for MS SQL or SQL recovery software to repair and recover the .MDF and have this database ready to use.
This software is easy to learn and saves a lot of time. It is recommended by Microsoft MVPs. If you have more questions about this Error and how to fix it, feel free to write your comments.
