How to Fix SQL Database Error 3456

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 suspect state. A suspect state in a database means that 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:

Error Message 3456

The error message 3456 is the following:

spid41s Error: 3456, Severity: 21, State: 1.

In above error message, the 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:

Event Viewer

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:

Windows log application

How can we solve this error?

There are following ways to fix SQL database error 3456, and these are:

  1. Using SQL Server Management Studio (SSMS)
  2. T-SQL Command
  3. SQL database repair software

Let’s discuss these options one by one.

Solution 1: Using SSMS

  1. 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:

restore database

  1. In Restore Database, press the browse button in the device section to select a device:

select the database

  1. In the Select Backup devices, select your device. If you do not have a device, press the Add button:

add the database

  1. Select the backup file and press OK:

select the backup file

  1. In this example, a tail-log backup will be created before restoring. A warning message will be displayed:

tail log backup

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:

sql server logs

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

Alternative Solution is to use the 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:

free download

  1. This software requires stopping the SQL Server Service. Once stopped, you can open the Stellar Repair for MS SQL.

  1. 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:

  1. Once repaired, you will receive a success message:

  1. 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 software to repair 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.

Comments(10)
  1. Conrad A. Parrish December 26, 2018
    • Eric Simson December 26, 2018
  2. Steven A. Rosario November 21, 2018
  3. Rafael D. Cuevas November 19, 2018
  4. David November 16, 2018
    • Eric Simson November 17, 2018
  5. Kylo Benicio September 5, 2018
    • Eric Simson September 6, 2018
  6. Lance Whittaker March 5, 2018
    • Eric Simson March 6, 2018

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.